Direct API Integration¶

Overview¶

The Direct API integration approach connects Power BI directly to Matomo's Reporting API, providing a simple and cost-effective solution for importing analytics data without additional infrastructure.

📖 Official Documentation: This integration uses the Matomo Reporting API. Refer to the official documentation for the complete API reference, authentication methods, and advanced features.

Matomo Instance: https://syvntyve.matomo.cloud

Architecture¶

graph LR
    subgraph "Matomo Cloud"
        MA[Matomo Analytics]
        MAPI[Reporting API]
    end

    subgraph "Power BI Desktop"
        WC[Web Connector]
        DM[Data Model]
        T[Transformations]
    end

    subgraph "Power BI Service"
        DS[Dataset]
        GW[Data Gateway<br/>Optional]
        REF[Scheduled Refresh]
        REP[Reports & Dashboards]
    end

    MA --> MAPI
    MAPI -->|HTTPS/JSON| WC
    WC --> T
    T --> DM
    DM -->|Publish| DS
    DS --> REF
    REF -->|Refresh| MAPI
    DS --> REP

    style MA fill:#f9f,stroke:#333,stroke-width:2px
    style REP fill:#9f9,stroke:#333,stroke-width:2px

Implementation Steps¶

🔐 Security First: This integration uses POST requests only to keep API tokens secure. GET requests expose tokens in URLs, browser history, and server logs, creating security vulnerabilities.

Power BI Implementation Approaches¶

📍 Important: Power BI offers two ways to connect to APIs. Choose the right approach for security:

Approach	Security	Complexity	Recommended For
Web Connector	❌ Low (GET only, token in URL)	✅ Simple	Development/Testing Only
Blank Query + M Code	✅ High (POST, token in body)	⚠️ Medium	Production Use

⚠️ Web Connector Limitation: Power BI's Web Connector only supports GET requests, which means tokens are exposed in URLs. This is a significant security risk and should never be used in production.

✅ Recommended Approach: Use Blank Query with M Code to implement secure POST requests.

Step 1: Generate Matomo API Token¶

Log into your Matomo Cloud instance at https://syvntyve.matomo.cloud
Navigate to Settings → Personal → Security
Generate a new API Authentication Token
Store the token securely (Azure Key Vault recommended)

⚠️ Security Warning: Never include API tokens in URLs, share URLs containing tokens, or commit tokens to version control.

Step 2: Set Up Power BI Parameters (Security Best Practice)¶

Before creating queries, set up secure parameter management:

Create Parameter:
In Power BI Desktop: Home → Manage Parameters → New Parameter
Name: MatomoApiToken
Type: Text
Suggested Values: None
Current Value: [Your API Token]
Mark as Sensitive (Power BI Service):
After publishing, go to Settings → Parameters
Enable "Sensitive" for the token parameter
This prevents the token from being displayed in the interface

Step 3: Create Secure POST Queries in Power BI¶

📝 Where to Enter Your Queries: Follow these steps to create secure Matomo API connections:

Method 1: Blank Query (Recommended)¶

Create New Query:
Power BI Desktop → Get Data → Other → Blank Query
Click "OK"
Access Advanced Editor:
In Power Query Editor → Home tab → Advanced Editor
Or right-click the query → Advanced Editor

Replace Default Code:

let
    Source = "Hello World"
in
    Source

With your Matomo POST request:

let
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    RequestBody = Text.ToBinary(
        "module=API" &
        "&method=VisitsSummary.get" &
        "&idSite=1" &
        "&period=day" &
        "&date=yesterday" &
        "&format=JSON" &
        "&token_auth=" & MatomoApiToken
    ),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded",
            #"User-Agent" = "PowerBI-MatomoConnector/1.0"
        ],
        Content = RequestBody
    ],

    Source = Json.Document(Web.Contents(ApiUrl, Options))
in
    Source

Name Your Query:
Right-click the query in the Queries pane
Rename to something descriptive like "Matomo_Traffic_Summary"
Test Connection:
Click "Done" in Advanced Editor
Power BI should fetch and display your data
If errors occur, check the error message and verify your token

Method 2: Custom Function (For Multiple Queries)¶

Create Function Query:
Get Data → Blank Query → Advanced Editor
Name the query "FetchMatomoData" (this becomes your function name)

Function Code:

(method as text, optional parameters as record) =>
let
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    DefaultParams = [
        module = "API",
        idSite = "1",
        period = "day", 
        date = "yesterday",
        format = "JSON",
        token_auth = MatomoApiToken
    ],

    MergedParams = Record.Combine({DefaultParams, parameters ?? []}),
    UpdatedParams = Record.AddField(MergedParams, "method", method),

    // Convert parameters to form data
    ParamPairs = List.Transform(
        Record.FieldNames(UpdatedParams),
        each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(UpdatedParams, _)))
    ),

    RequestBody = Text.ToBinary(Text.Combine(ParamPairs, "&")),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        Content = RequestBody
    ],

    Source = Json.Document(Web.Contents(ApiUrl, Options))
in
    Source

Use the Function:
Create new Blank Query

Call your function:

let
    TrafficData = FetchMatomoData("VisitsSummary.get"),
    PageData = FetchMatomoData("Actions.getPageUrls", [filter_limit = "50"]),
    CountryData = FetchMatomoData("UserCountry.getCountry")
in
    TrafficData

Method 3: Data Source Settings (Alternative)¶

Get Data → Web → Advanced:
URL parts: Just enter https://syvntyve.matomo.cloud/index.php
HTTP request method: POST
Request body: Select "Custom" and enter form data

Request Body Content:

module=API&method=VisitsSummary.get&idSite=1&period=day&date=yesterday&format=JSON&token_auth=[Your Token Here]

⚠️ Important Notes: - Always use POST method to keep tokens secure - Test queries in Power BI Desktop before publishing to Service
- Set up parameters before creating queries for easier maintenance - Name queries descriptively for better organization

Step 4: Matomo API Reference¶

🔐 POST Request Format Only (Secure):

All Matomo API calls use the same base URL with parameters in the request body:

Base URL: https://syvntyve.matomo.cloud/index.php

Request Body Parameters:

module=API
&method=<Module>.<method>
&idSite=<siteId>
&period=<period>
&date=<date>
&format=JSON
&token_auth=<your_token>

⚠️ Security Note: Never include these parameters in the URL. Always use POST with parameters in the request body.

Common report methods:

Core Analytics: - VisitsSummary.get - Basic traffic metrics (visits, visitors, page views, bounce rate) - Actions.getPageUrls - Page views data with URLs and performance metrics - Actions.getPageTitles - Page titles and their performance - Actions.getEntryPageUrls - Landing pages analysis - Actions.getExitPageUrls - Exit pages analysis

Audience Analytics: - UserCountry.getCountry - Geographic visitor distribution - UserCountry.getCity - City-level geographic data - DevicesDetection.getType - Device types (desktop, mobile, tablet) - DevicesDetection.getBrowsers - Browser analytics - DevicesDetection.getOsVersions - Operating system versions - VisitorInterest.getNumberOfVisitsPerPage - Engagement metrics

Traffic Sources: - Referrers.getReferrerType - Traffic source categories (direct, search, social, etc.) - Referrers.getWebsites - Referring domains and traffic volume - Referrers.getSearchEngines - Search engine traffic breakdown - Referrers.getSocials - Social media referrals

Conversion Tracking: - Goals.get - Goal conversions and conversion rates - Goals.getGoalsOverview - Overview of all goals performance - Ecommerce.getItemsSku - E-commerce product performance (if applicable)

Custom Analytics: - CustomDimensions.getCustomDimension - Custom dimension reporting - Events.getCategory - Event tracking by category - Events.getAction - Event actions analysis

POST Request Examples with Response Formats:

Traffic Summary Request (POST):

curl -X POST "https://syvntyve.matomo.cloud/index.php" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "module=API&method=VisitsSummary.get&idSite=1&period=day&date=last30&format=JSON&token_auth=your_token"

Power BI Implementation:

let
    RequestBody = Text.ToBinary(
        "module=API&method=VisitsSummary.get&idSite=1&period=day&date=last30&format=JSON&token_auth=" & MatomoApiToken
    ),
    Options = [
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = RequestBody
    ],
    Source = Json.Document(Web.Contents("https://syvntyve.matomo.cloud/index.php", Options))
in
    Source

Expected Response:

{
  "2024-01-01": {
    "nb_visits": 1245,
    "nb_uniq_visitors": 892,
    "nb_pageviews": 5678,
    "nb_actions": 4321,
    "bounce_rate": "42%",
    "avg_time_on_site": 185,
    "nb_actions_per_visit": 3.5
  },
  "2024-01-02": {
    "nb_visits": 1167,
    "nb_uniq_visitors": 834,
    "nb_pageviews": 4893,
    "nb_actions": 3876,
    "bounce_rate": "45%",
    "avg_time_on_site": 172,
    "nb_actions_per_visit": 3.3
  }
}

Top Pages Request (POST):

curl -X POST "https://syvntyve.matomo.cloud/index.php" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "module=API&method=Actions.getPageUrls&idSite=1&period=month&date=today&format=JSON&filter_limit=50&token_auth=your_token"

Power BI Implementation:

let
    RequestBody = Text.ToBinary(
        "module=API&method=Actions.getPageUrls&idSite=1&period=month&date=today&format=JSON&filter_limit=50&token_auth=" & MatomoApiToken
    ),
    Options = [
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = RequestBody
    ],
    Source = Json.Document(Web.Contents("https://syvntyve.matomo.cloud/index.php", Options))
in
    Source

Expected Response:

[
  {
    "label": "/home",
    "nb_visits": 2456,
    "nb_uniq_visitors": 1789,
    "nb_pageviews": 3421,
    "bounce_rate": "38%",
    "avg_time_on_page": 145,
    "exit_rate": "12%"
  },
  {
    "label": "/products",
    "nb_visits": 1834,
    "nb_uniq_visitors": 1245,
    "nb_pageviews": 2567,
    "bounce_rate": "52%",
    "avg_time_on_page": 98,
    "exit_rate": "28%"
  },
  {
    "label": "/about",
    "nb_visits": 987,
    "nb_uniq_visitors": 756,
    "nb_pageviews": 1123,
    "bounce_rate": "61%",
    "avg_time_on_page": 203,
    "exit_rate": "45%"
  }
]

Geographic Data Request (POST):

curl -X POST "https://syvntyve.matomo.cloud/index.php" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "module=API&method=UserCountry.getCountry&idSite=1&period=week&date=today&format=JSON&token_auth=your_token"

Power BI Implementation:

let
    RequestBody = Text.ToBinary(
        "module=API&method=UserCountry.getCountry&idSite=1&period=week&date=today&format=JSON&token_auth=" & MatomoApiToken
    ),
    Options = [
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = RequestBody
    ],
    Source = Json.Document(Web.Contents("https://syvntyve.matomo.cloud/index.php", Options))
in
    Source

Expected Response:

[
  {
    "label": "United States",
    "nb_visits": 3456,
    "nb_uniq_visitors": 2134,
    "nb_pageviews": 12345,
    "code": "US",
    "logo": "plugins/Morpheus/icons/dist/flags/us.png"
  },
  {
    "label": "United Kingdom", 
    "nb_visits": 1234,
    "nb_uniq_visitors": 892,
    "nb_pageviews": 4567,
    "code": "GB",
    "logo": "plugins/Morpheus/icons/dist/flags/gb.png"
  }
]

Device Analytics Request (POST):

curl -X POST "https://syvntyve.matomo.cloud/index.php" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "module=API&method=DevicesDetection.getType&idSite=1&period=month&date=today&format=JSON&token_auth=your_token"

Power BI Implementation:

let
    RequestBody = Text.ToBinary(
        "module=API&method=DevicesDetection.getType&idSite=1&period=month&date=today&format=JSON&token_auth=" & MatomoApiToken
    ),
    Options = [
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = RequestBody
    ],
    Source = Json.Document(Web.Contents("https://syvntyve.matomo.cloud/index.php", Options))
in
    Source

Expected Response:

[
  {
    "label": "Desktop",
    "nb_visits": 4567,
    "nb_uniq_visitors": 3241,
    "nb_pageviews": 18934,
    "avg_time_on_site": 245
  },
  {
    "label": "Smartphone",
    "nb_visits": 3421,
    "nb_uniq_visitors": 2876,
    "nb_pageviews": 9876,
    "avg_time_on_site": 156
  },
  {
    "label": "Tablet",
    "nb_visits": 892,
    "nb_uniq_visitors": 734,
    "nb_pageviews": 2456,
    "avg_time_on_site": 198
  }
]

Step 5: Basic Data Processing Example¶

Secure POST Implementation for Traffic Summary:

let
    // Configuration using parameter
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    // Build POST request body
    RequestBody = Text.ToBinary(
        "module=API" &
        "&method=VisitsSummary.get" &
        "&idSite=1" &
        "&period=day" &
        "&date=last30" &
        "&format=JSON" &
        "&token_auth=" & MatomoApiToken
    ),

    // Configure secure POST request
    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded",
            #"User-Agent" = "PowerBI-MatomoConnector/1.0"
        ],
        Content = RequestBody
    ],

    // Fetch data using POST
    Source = Json.Document(Web.Contents(ApiUrl, Options)),

    // Convert to table (handles both single records and date series)
    ConvertedToTable = if Value.Type(Source) = type record then
        Record.ToTable(Source)
    else
        Table.FromRecords({Source}),

    // Expand values for date-based data
    ExpandedValues = if Record.HasFields(ConvertedToTable{0}, "Value") then
        Table.ExpandRecordColumn(
            ConvertedToTable, 
            "Value", 
            {"nb_visits", "nb_uniq_visitors", "nb_pageviews", "bounce_rate"},
            {"Visits", "UniqueVisitors", "PageViews", "BounceRate"}
        )
    else
        ConvertedToTable
in
    ExpandedValues

Step 4: Create Custom Function for Multiple Reports¶

Create a reusable function to fetch different reports:

Basic Matomo Function (POST Method):

(method as text, optional parameters as record) =>
let
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    DefaultParams = [
        module = "API",
        idSite = "1",
        period = "day",
        date = "last30",
        format = "JSON",
        token_auth = MatomoApiToken
    ],

    MergedParams = Record.Combine({DefaultParams, parameters ?? []}),
    UpdatedParams = Record.AddField(MergedParams, "method", method),

    // Convert parameters to form data string
    ParamPairs = List.Transform(
        Record.FieldNames(UpdatedParams),
        each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(UpdatedParams, _)))
    ),

    RequestBody = Text.ToBinary(Text.Combine(ParamPairs, "&")),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        Content = RequestBody
    ],

    Source = Json.Document(Web.Contents(ApiUrl, Options))
in
    Source

Advanced Multi-Site Function:

(method as text, siteIds as list, optional parameters as record) =>
let
    BaseUrl = "https://syvntyve.matomo.cloud/index.php",
    TokenAuth = "your_token_here",

    // Function to fetch data for a single site
    FetchSiteData = (siteId as text) =>
        let
            DefaultParams = [
                module = "API",
                idSite = siteId,
                period = "day",
                date = "last30",
                format = "JSON",
                token_auth = TokenAuth
            ],
            MergedParams = Record.Combine({DefaultParams, parameters ?? []}),
            UpdatedParams = Record.AddField(MergedParams, "method", method),

            QueryString = Text.Combine(
                List.Transform(
                    Record.FieldNames(UpdatedParams),
                    each _ & "=" & Text.From(Record.Field(UpdatedParams, _))
                ),
                "&"
            ),

            ApiUrl = BaseUrl & "?" & QueryString,
            Response = Json.Document(Web.Contents(ApiUrl)),

            // Add site identifier to response
            WithSiteId = if Response is record 
                then Record.AddField(Response, "SiteId", siteId)
                else [SiteId = siteId, Data = Response]
        in
            WithSiteId,

    // Fetch data for all sites
    AllSitesData = List.Transform(siteIds, each FetchSiteData(_)),

    // Convert to table format
    ConvertedToTable = Table.FromList(AllSitesData, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ConvertedToTable

Segmented Analytics Function:

(method as text, segment as text, optional parameters as record) =>
let
    BaseUrl = "https://syvntyve.matomo.cloud/index.php",
    TokenAuth = "your_token_here",

    DefaultParams = [
        module = "API",
        idSite = "1",
        period = "day",
        date = "last30",
        format = "JSON",
        segment = segment,
        token_auth = TokenAuth
    ],

    MergedParams = Record.Combine({DefaultParams, parameters ?? []}),
    UpdatedParams = Record.AddField(MergedParams, "method", method),

    QueryString = Text.Combine(
        List.Transform(
            Record.FieldNames(UpdatedParams),
            each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(UpdatedParams, _)))
        ),
        "&"
    ),

    ApiUrl = BaseUrl & "?" & QueryString,
    Source = Json.Document(Web.Contents(ApiUrl))
in
    Source

Usage Examples:

// Single site basic traffic data
BasicTraffic = FetchMatomoData("VisitsSummary.get"),

// Multi-site comparison
MultiSiteTraffic = FetchMultiSiteData("VisitsSummary.get", {"1", "2", "3"}),

// Mobile users only
MobileTraffic = FetchSegmentedData("VisitsSummary.get", "deviceType==mobile"),

// Returning visitors from specific countries
ReturningVisitorsUS = FetchSegmentedData("VisitsSummary.get", "visitorType==returning;countryCode==US"),

// High-value pages (more than 5 page views)
PopularPages = FetchSegmentedData("Actions.getPageUrls", "pageviews>=5")

Advanced Segment Filtering¶

Common Segment Examples:

# Mobile users from the US who made purchases
segment=deviceType==mobile;countryCode==US;visitEcommerceStatus==ordered

# Returning visitors who spent more than 5 minutes on site
segment=visitorType==returning;visitDuration>=300

# Users who viewed specific pages
segment=pageUrl=@/products/;pageUrl=@/checkout

# High-engagement users (more than 3 page views)
segment=actions>=3

# Direct traffic only (no referrers)
segment=referrerType==direct

# Social media traffic from specific networks
segment=referrerName==Facebook,referrerName==Twitter

# Users with specific browser characteristics
segment=browserName==Chrome;resolution=@1920x1080

# Goal conversion analysis
segment=visitConvertedGoalId==1

Power BI Segment Integration:

let
    // Define dynamic segments based on analysis needs
    SegmentDefinitions = Table.FromRecords({
        [
            SegmentName = "Mobile Users",
            SegmentExpression = "deviceType==mobile",
            Description = "All mobile device users"
        ],
        [
            SegmentName = "High Value Visitors", 
            SegmentExpression = "actions>=5;visitDuration>=180",
            Description = "Users with 5+ actions and 3+ minutes on site"
        ],
        [
            SegmentName = "Returning Customers",
            SegmentExpression = "visitorType==returning;visitEcommerceStatus==ordered",
            Description = "Returning visitors who made purchases"
        ],
        [
            SegmentName = "Social Traffic",
            SegmentExpression = "referrerType==social",
            Description = "All social media referral traffic"
        ],
        [
            SegmentName = "US Desktop Users",
            SegmentExpression = "countryCode==US;deviceType==desktop", 
            Description = "Desktop users from United States"
        ]
    }),

    // Function to fetch data for each segment
    FetchSegmentData = (segmentRow as record) =>
        let
            ApiUrl = "https://syvntyve.matomo.cloud/index.php?" &
                "module=API" &
                "&method=VisitsSummary.get" &
                "&idSite=1" &
                "&period=month" &
                "&date=today" &
                "&format=JSON" &
                "&segment=" & Uri.EscapeDataString(segmentRow[SegmentExpression]) &
                "&token_auth=your_token_here",

            Response = try Json.Document(Web.Contents(ApiUrl)) otherwise null,

            // Add segment metadata to response
            WithMetadata = if Response <> null then
                Record.Combine({
                    Response,
                    [
                        SegmentName = segmentRow[SegmentName],
                        SegmentDescription = segmentRow[Description],
                        SegmentExpression = segmentRow[SegmentExpression]
                    ]
                })
            else
                [
                    SegmentName = segmentRow[SegmentName],
                    Error = "Failed to fetch data",
                    SegmentExpression = segmentRow[SegmentExpression]
                ]
        in
            WithMetadata,

    // Apply function to all segments
    SegmentResults = Table.AddColumn(
        SegmentDefinitions,
        "AnalyticsData", 
        each FetchSegmentData(_)
    ),

    // Expand the analytics data
    ExpandedResults = Table.ExpandRecordColumn(
        SegmentResults,
        "AnalyticsData",
        {"nb_visits", "nb_uniq_visitors", "nb_pageviews", "bounce_rate"},
        {"Visits", "UniqueVisitors", "PageViews", "BounceRate"}
    )
in
    ExpandedResults

Complex Segment Analysis:

let
    // Advanced segment for e-commerce analysis
    EcommerceSegments = {
        [
            Name = "Cart Abandoners",
            Segment = "visitEcommerceStatus==abandonedCart",
            Method = "VisitsSummary.get"
        ],
        [
            Name = "High Value Orders", 
            Segment = "visitEcommerceStatus==ordered;revenueOrder>=100",
            Method = "Goals.get",
            GoalId = "ecommerceOrder"
        ],
        [
            Name = "Mobile Converters",
            Segment = "deviceType==mobile;visitConvertedGoalId==1",
            Method = "DevicesDetection.getType"
        ],
        [
            Name = "Repeat Customers",
            Segment = "visitorType==returning;visitEcommerceStatus==ordered;daysSinceLastEcommerceOrder<=30",
            Method = "VisitsSummary.get"
        ]
    },

    // Process each segment
    ProcessSegment = (segmentRecord as record) =>
        let
            BaseParams = [
                module = "API",
                idSite = "1", 
                period = "week",
                date = "today",
                format = "JSON",
                segment = segmentRecord[Segment],
                token_auth = "your_token_here"
            ],

            // Add method-specific parameters
            FinalParams = if Record.HasFields(segmentRecord, "GoalId") 
                then Record.AddField(BaseParams, "idGoal", segmentRecord[GoalId])
                else BaseParams,

            MethodParam = Record.AddField(FinalParams, "method", segmentRecord[Method]),

            QueryString = Text.Combine(
                List.Transform(
                    Record.FieldNames(MethodParam),
                    each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(MethodParam, _)))
                ),
                "&"
            ),

            ApiUrl = "https://syvntyve.matomo.cloud/index.php?" & QueryString,

            Response = try Json.Document(Web.Contents(ApiUrl)) otherwise [Error = "API call failed"]
        in
            [
                SegmentName = segmentRecord[Name],
                Data = Response,
                SegmentFilter = segmentRecord[Segment]
            ],

    Results = List.Transform(EcommerceSegments, each ProcessSegment(_)),
    ConvertedToTable = Table.FromList(Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ConvertedToTable

Step 5: Data Transformation¶

graph TD
    subgraph "Data Processing Pipeline"
        RAW[Raw JSON Data]
        PARSE[Parse JSON Structure]
        NORM[Normalize Tables]
        TYPE[Set Data Types]
        REL[Create Relationships]
        CALC[Add Calculated Columns]
        MEAS[Create Measures]
    end

    RAW --> PARSE
    PARSE --> NORM
    NORM --> TYPE
    TYPE --> REL
    REL --> CALC
    CALC --> MEAS

    style RAW fill:#ffd,stroke:#333,stroke-width:2px
    style MEAS fill:#dfd,stroke:#333,stroke-width:2px

Example transformation for time-series data:

let
    Source = FetchMatomoData("VisitsSummary.get", [date = "last30"]),

    // Convert to table
    ToTable = 
        if Source is record then 
            Table.FromRecords({Source})
        else 
            Table.FromList(Source, Splitter.SplitByNothing()),

    // Expand nested columns
    ExpandedData = Table.ExpandRecordColumn(
        ToTable,
        "Column1",
        {"nb_visits", "nb_uniq_visitors", "nb_pageviews", "bounce_rate"},
        {"Visits", "UniqueVisitors", "PageViews", "BounceRate"}
    ),

    // Add date column
    AddedDate = Table.AddIndexColumn(ExpandedData, "DaysAgo", 0, 1),
    AddedDateCalc = Table.AddColumn(
        AddedDate,
        "Date",
        each Date.AddDays(Date.From(DateTime.LocalNow()), -[DaysAgo])
    ),

    // Set data types
    TypedData = Table.TransformColumnTypes(
        AddedDateCalc,
        {
            {"Date", type date},
            {"Visits", Int64.Type},
            {"UniqueVisitors", Int64.Type},
            {"PageViews", Int64.Type},
            {"BounceRate", type number}
        }
    ),

    // Remove helper column
    FinalTable = Table.RemoveColumns(TypedData, {"DaysAgo"})
in
    FinalTable

Step 6: Configure Scheduled Refresh¶

Publish report to Power BI Service
Navigate to dataset settings
Configure refresh schedule:
Basic: 8 refreshes/day
Pro: 8 refreshes/day
Premium: 48 refreshes/day
Set up refresh failure notifications

Performance Optimization¶

API Call Batching¶

Bulk Request Method (POST):

Combine multiple metrics in single API call using API.getBulkRequest:

# Secure POST bulk request
curl -X POST "https://syvntyve.matomo.cloud/index.php" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "module=API&method=API.getBulkRequest&format=JSON&token_auth=your_token&urls[0]=method%3DVisitsSummary.get%26period%3Dday%26date%3Dtoday&urls[1]=method%3DActions.getPageUrls%26period%3Dday%26date%3Dtoday&urls[2]=method%3DUserCountry.getCountry%26period%3Dday%26date%3Dtoday"

Power BI Bulk Request Implementation (POST):

let
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    // Define multiple requests
    RequestUrls = {
        "method=VisitsSummary.get&period=day&date=today",
        "method=Actions.getPageUrls&period=day&date=today&filter_limit=50",
        "method=UserCountry.getCountry&period=day&date=today&filter_limit=20",
        "method=DevicesDetection.getType&period=day&date=today",
        "method=Referrers.getReferrerType&period=day&date=today"
    },

    // URL encode each request
    EncodedUrls = List.Transform(RequestUrls, each Uri.EscapeDataString(_)),

    // Build bulk request parameters
    BaseParams = "module=API&method=API.getBulkRequest&format=JSON&token_auth=" & MatomoApiToken,

    UrlParams = List.Accumulate(
        List.Positions(EncodedUrls),
        BaseParams,
        (state, current) => state & "&urls[" & Text.From(current) & "]=" & EncodedUrls{current}
    ),

    // Create POST request
    RequestBody = Text.ToBinary(UrlParams),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        Content = RequestBody
    ],

    // Fetch bulk response using POST
    Source = Json.Document(Web.Contents(ApiUrl, Options)),

    // Convert list to table with labels
    ConvertedToTable = Table.FromList(
        Source,
        Splitter.SplitByNothing(),
        {"Response"},
        null,
        ExtraValues.Error
    ),

    // Add request type labels
    WithIndex = Table.AddIndexColumn(ConvertedToTable, "RequestIndex", 0, 1),

    RequestLabels = {"VisitsSummary", "PageUrls", "Countries", "DeviceTypes", "ReferrerTypes"},

    WithLabels = Table.AddColumn(
        WithIndex,
        "RequestType",
        each RequestLabels{[RequestIndex]}
    )
in
    WithLabels

Advanced Bulk Request with Error Handling:

let
    // Define requests with metadata
    Requests = Table.FromRecords({
        [Label = "Traffic Summary", Method = "VisitsSummary.get", Params = "period=day&date=today"],
        [Label = "Top Pages", Method = "Actions.getPageUrls", Params = "period=day&date=today&filter_limit=100"],
        [Label = "Traffic Sources", Method = "Referrers.getReferrerType", Params = "period=day&date=today"],
        [Label = "Device Analytics", Method = "DevicesDetection.getType", Params = "period=day&date=today"],
        [Label = "Geographic Data", Method = "UserCountry.getCountry", Params = "period=day&date=today&filter_limit=50"]
    }),

    // Build URLs for each request
    RequestsWithUrls = Table.AddColumn(
        Requests,
        "EncodedUrl",
        each Uri.EscapeDataString("method=" & [Method] & "&" & [Params])
    ),

    // Create bulk request (POST)
    ApiUrl = "https://syvntyve.matomo.cloud/index.php",

    UrlsList = Table.Column(RequestsWithUrls, "EncodedUrl"),

    BulkUrlParams = List.Accumulate(
        List.Positions(UrlsList),
        "module=API&method=API.getBulkRequest&format=JSON&token_auth=" & MatomoApiToken,
        (state, current) => state & "&urls[" & Text.From(current) & "]=" & UrlsList{current}
    ),

    // Create POST request
    RequestBody = Text.ToBinary(BulkUrlParams),

    Options = [
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        Content = RequestBody
    ],

    // Execute request with error handling
    Response = try Json.Document(Web.Contents(ApiUrl, Options)) otherwise null,

    // Process successful responses
    ProcessedData = if Response <> null then
        let
            ResponseList = if Value.Is(Response, type list) then Response else {Response},

            ResponseTable = Table.FromList(
                ResponseList,
                Splitter.SplitByNothing(),
                {"Data"},
                null,
                ExtraValues.Error
            ),

            WithIndex = Table.AddIndexColumn(ResponseTable, "Index", 0, 1),

            // Join with request metadata
            WithMetadata = Table.NestedJoin(
                WithIndex,
                {"Index"},
                Table.AddIndexColumn(RequestsWithUrls, "Index", 0, 1),
                {"Index"},
                "RequestInfo"
            ),

            ExpandedMetadata = Table.ExpandTableColumn(
                WithMetadata,
                "RequestInfo",
                {"Label", "Method"},
                {"RequestLabel", "RequestMethod"}
            )
        in
            ExpandedMetadata
    else
        #table({"Error"}, {{"Failed to fetch bulk data"}})
in
    ProcessedData

Incremental Refresh¶

Configure incremental refresh in Power BI:

let
    // Define RangeStart and RangeEnd parameters
    FilteredData = Table.SelectRows(
        SourceData,
        each [Date] >= RangeStart and [Date] < RangeEnd
    )
in
    FilteredData

Query Folding¶

Optimize queries to push filtering to Matomo API:

let
    Source = FetchMatomoData(
        "Actions.getPageUrls",
        [
            date = "last7",
            filter_limit = "100",
            filter_column = "nb_visits",
            filter_sort_order = "desc"
        ]
    )
in
    Source

Common Issues and Solutions¶

Issue 1: API Rate Limiting¶

Symptom: HTTP 429 "Too Many Requests" errors during refresh

Root Cause: Matomo Cloud enforces API rate limits (varies by plan)

Solutions:

Basic Rate Limiting Protection:

let
    MatomoApiCall = (apiUrl as text) =>
        let
            Response = try 
                Web.Contents(apiUrl, [Timeout = #duration(0, 0, 1, 0)])
            otherwise
                let
                    // Wait and retry once on rate limit
                    Wait = Function.InvokeAfter(
                        () => 1,
                        #duration(0,0,0,5)  // 5-second wait
                    ),
                    RetryResponse = try Web.Contents(apiUrl) otherwise [Error = "Rate limit exceeded"]
                in
                    RetryResponse
        in
            Response
in
    MatomoApiCall

Advanced Rate Limiting with Exponential Backoff:

let
    CallApiWithRetry = (url as text, optional maxRetries as number) =>
        let
            MaxRetries = if maxRetries = null then 3 else maxRetries,

            AttemptCall = (attemptNumber as number) =>
                let
                    Response = try 
                        Json.Document(Web.Contents(url))
                    otherwise
                        let
                            ErrorDetails = try Web.Contents(url) otherwise [Error = "Network error"],
                            IsRateLimit = if Record.HasFields(ErrorDetails, "message") 
                                then Text.Contains(ErrorDetails[message], "429") or Text.Contains(ErrorDetails[message], "rate")
                                else false
                        in
                            if IsRateLimit and attemptNumber < MaxRetries then
                                let
                                    WaitTime = Number.Power(2, attemptNumber), // Exponential backoff: 2, 4, 8 seconds
                                    Wait = Function.InvokeAfter(
                                        () => 1,
                                        #duration(0, 0, 0, WaitTime)
                                    ),
                                    NextAttempt = AttemptCall(attemptNumber + 1)
                                in
                                    NextAttempt
                            else
                                [Error = "API call failed after " & Text.From(attemptNumber) & " attempts"]
                in
                    Response
        in
            AttemptCall(0)
in
    CallApiWithRetry

Issue 2: Large Dataset Timeouts¶

Symptom: Timeout errors for large date ranges or high-traffic sites

Root Cause: Matomo API timeout on complex queries

Solutions:

Date Range Chunking:

let
    FetchDateRangeInChunks = (startDate as date, endDate as date, chunkSizeDays as number) =>
        let
            DateRanges = List.Generate(
                () => [Start = startDate, End = Date.AddDays(startDate, chunkSizeDays - 1)],
                each [Start] <= endDate,
                each [
                    Start = Date.AddDays([End], 1),
                    End = Date.Min({Date.AddDays([Start], chunkSizeDays - 1), endDate})
                ]
            ),

            FetchChunk = (dateRange as record) =>
                let
                    ApiUrl = "https://syvntyve.matomo.cloud/index.php?" &
                        "module=API" &
                        "&method=VisitsSummary.get" &
                        "&idSite=1" &
                        "&period=day" &
                        "&date=" & Date.ToText(dateRange[Start], "yyyy-MM-dd") & "," & Date.ToText(dateRange[End], "yyyy-MM-dd") &
                        "&format=JSON" &
                        "&token_auth=your_token_here",

                    Response = try 
                        Json.Document(Web.Contents(ApiUrl, [Timeout = #duration(0, 0, 2, 0)]))
                    otherwise
                        [Error = "Chunk failed: " & Date.ToText(dateRange[Start]) & " to " & Date.ToText(dateRange[End])]
                in
                    [DateRange = dateRange, Data = Response],

            AllChunks = List.Transform(DateRanges, each FetchChunk(_)),
            SuccessfulChunks = List.Select(AllChunks, each not Record.HasFields([Data], "Error")),

            CombinedData = List.Accumulate(
                SuccessfulChunks,
                [],
                (state, current) => Record.Combine({state, current[Data]})
            )
        in
            CombinedData
in
    FetchDateRangeInChunks

Parallel Processing with Error Resilience:

let
    ParallelFetch = (dateList as list) =>
        let
            FetchSingleDate = (singleDate as date) =>
                let
                    DateString = Date.ToText(singleDate, "yyyy-MM-dd"),
                    ApiUrl = "https://syvntyve.matomo.cloud/index.php?" &
                        "module=API&method=VisitsSummary.get&idSite=1&period=day" &
                        "&date=" & DateString & "&format=JSON&token_auth=your_token_here",

                    Response = try
                        [
                            Date = singleDate,
                            Data = Json.Document(Web.Contents(ApiUrl, [Timeout = #duration(0, 0, 1, 0)])),
                            Status = "Success"
                        ]
                    otherwise
                        [
                            Date = singleDate,
                            Data = null,
                            Status = "Failed",
                            Error = "Timeout or network error"
                        ]
                in
                    Response,

            Results = List.Transform(dateList, each FetchSingleDate(_)),

            // Filter successful results and create combined dataset
            SuccessfulResults = List.Select(Results, each [Status] = "Success"),
            FailedResults = List.Select(Results, each [Status] = "Failed"),

            Summary = [
                SuccessfulCount = List.Count(SuccessfulResults),
                FailedCount = List.Count(FailedResults),
                SuccessRate = List.Count(SuccessfulResults) / List.Count(Results),
                Data = SuccessfulResults
            ]
        in
            Summary
in
    ParallelFetch

Issue 3: Authentication Errors¶

Symptom: 401 Unauthorized or 403 Forbidden errors

Root Causes: Invalid token, expired token, insufficient permissions

Solutions:

Token Validation Function:

let
    ValidateToken = (token as text) =>
        let
            TestUrl = "https://syvntyve.matomo.cloud/index.php?" &
                "module=API&method=UsersManager.getUser&userLogin=&token_auth=" & token,

            ValidationResponse = try 
                Web.Contents(TestUrl, [Timeout = #duration(0, 0, 0, 30)])
            otherwise
                [Error = "Token validation failed"],

            IsValid = not Record.HasFields(ValidationResponse, "Error"),

            Result = [
                IsValidToken = IsValid,
                Message = if IsValid then "Token is valid" else "Token is invalid or expired",
                TestUrl = TestUrl
            ]
        in
            Result
in
    ValidateToken

Secure Token Management:

let
    // Use Power BI parameters for token storage
    GetSecureToken = () =>
        let
            // This should reference a Power BI parameter marked as sensitive
            Token = try Parameter_MatomoToken otherwise "",

            TokenCheck = if Token = "" then
                error "Matomo token not configured. Please set Parameter_MatomoToken in Power BI."
            else
                Token
        in
            TokenCheck,

    // Wrapper function that includes token validation
    SecureMatomoCall = (method as text, parameters as record) =>
        let
            Token = GetSecureToken(),

            // Validate token first
            ValidationResult = ValidateToken(Token),

            ApiCall = if ValidationResult[IsValidToken] then
                let
                    BaseParams = Record.Combine({
                        [
                            module = "API",
                            method = method,
                            idSite = "1",
                            format = "JSON",
                            token_auth = Token
                        ],
                        parameters
                    }),

                    QueryString = Text.Combine(
                        List.Transform(
                            Record.FieldNames(BaseParams),
                            each _ & "=" & Uri.EscapeDataString(Text.From(Record.Field(BaseParams, _)))
                        ),
                        "&"
                    ),

                    ApiUrl = "https://syvntyve.matomo.cloud/index.php?" & QueryString,
                    Response = Json.Document(Web.Contents(ApiUrl))
                in
                    Response
            else
                error ValidationResult[Message]
        in
            ApiCall
in
    SecureMatomoCall

Issue 4: Data Structure Inconsistencies¶

Symptom: Power BI transformation errors due to changing JSON structure

Root Cause: Different API methods return different data structures

Solution:

Robust Data Processing:

let
    ProcessMatomoResponse = (response, expectedFields as list) =>
        let
            // Handle different response types
            NormalizedResponse = 
                if Value.Is(response, type record) then
                    if Record.HasFields(response, "message") and Text.Contains(response[message], "error") then
                        [Error = response[message]]
                    else
                        response
                else if Value.Is(response, type list) then
                    if List.Count(response) = 0 then
                        [Error = "No data returned"]
                    else
                        response
                else
                    [Error = "Unexpected response format"],

            // Safely extract fields
            SafeFieldExtraction = if not Record.HasFields(NormalizedResponse, "Error") then
                let
                    AvailableFields = if Value.Is(NormalizedResponse, type record) 
                        then Record.FieldNames(NormalizedResponse)
                        else if Value.Is(NormalizedResponse, type list) and List.Count(NormalizedResponse) > 0
                        then Record.FieldNames(NormalizedResponse{0})
                        else {},

                    SafeFields = List.Intersect({expectedFields, AvailableFields}),

                    ExtractedData = if Value.Is(NormalizedResponse, type record) then
                        Record.SelectFields(NormalizedResponse, SafeFields, MissingField.UseNull)
                    else if Value.Is(NormalizedResponse, type list) then
                        List.Transform(
                            NormalizedResponse, 
                            each Record.SelectFields(_, SafeFields, MissingField.UseNull)
                        )
                    else
                        null
                in
                    [
                        Data = ExtractedData,
                        AvailableFields = AvailableFields,
                        ExtractedFields = SafeFields,
                        Status = "Success"
                    ]
            else
                [
                    Data = null,
                    Status = "Error",
                    ErrorMessage = NormalizedResponse[Error]
                ]
        in
            SafeFieldExtraction
in
    ProcessMatomoResponse

Issue 5: Segment Syntax Errors¶

Symptom: Invalid segment parameter errors

Root Cause: Incorrect segment syntax or unsupported operators

Solution:

Segment Validation Function:

let
    ValidateSegment = (segment as text) =>
        let
            // Common segment syntax patterns
            ValidOperators = {"==", "!=", ">=", "<=", ">", "<", "=@", "!@"},
            ValidDimensions = {
                "deviceType", "browserName", "countryCode", "visitorType", 
                "referrerType", "actions", "visitDuration", "pageUrl", 
                "visitEcommerceStatus", "goalConversions"
            },

            // Split segment by semicolons (AND conditions)
            SegmentParts = Text.Split(segment, ";"),

            ValidatePart = (part as text) =>
                let
                    // Check if part contains valid operator
                    HasValidOperator = List.AnyTrue(
                        List.Transform(ValidOperators, each Text.Contains(part, _))
                    ),

                    // Extract dimension name (before operator)
                    DimensionMatch = List.FirstN(
                        List.Select(ValidDimensions, each Text.StartsWith(part, _)), 
                        1
                    ),

                    HasValidDimension = List.Count(DimensionMatch) > 0,

                    Result = [
                        Part = part,
                        HasValidOperator = HasValidOperator,
                        HasValidDimension = HasValidDimension,
                        IsValid = HasValidOperator and HasValidDimension,
                        Issues = if not HasValidOperator then "Invalid operator" 
                               else if not HasValidDimension then "Invalid dimension" 
                               else "OK"
                    ]
                in
                    Result,

            PartValidation = List.Transform(SegmentParts, each ValidatePart(_)),
            AllValid = List.AllTrue(List.Transform(PartValidation, each [IsValid])),

            ValidationSummary = [
                IsValidSegment = AllValid,
                OriginalSegment = segment,
                PartCount = List.Count(SegmentParts),
                ValidationDetails = PartValidation,
                Issues = List.Select(PartValidation, each not [IsValid])
            ]
        in
            ValidationSummary,

    // Safe segment application
    ApplySegment = (method as text, segment as text, otherParams as record) =>
        let
            SegmentValidation = ValidateSegment(segment),

            ApiCall = if SegmentValidation[IsValidSegment] then
                SecureMatomoCall(method, Record.Combine({otherParams, [segment = segment]}))
            else
                [
                    Error = "Invalid segment syntax",
                    SegmentIssues = SegmentValidation[Issues],
                    OriginalSegment = segment
                ]
        in
            ApiCall
in
    ApplySegment

Security Best Practices¶

Token Management¶

graph TD
    subgraph "Secure Token Flow"
        KV[Azure Key Vault]
        PBI[Power BI Parameters]
        DS[Dataset Settings]
        API[Matomo API]
    end

    KV -->|Retrieve| PBI
    PBI -->|Store Encrypted| DS
    DS -->|Use for Refresh| API

    style KV fill:#fdd,stroke:#333,stroke-width:2px
    style API fill:#dfd,stroke:#333,stroke-width:2px

Row-Level Security¶

Implement RLS for multi-tenant scenarios:

[SiteId] = LOOKUPVALUE(
    UserSites[SiteId],
    UserSites[UserEmail],
    USERPRINCIPALNAME()
)

Monitoring and Alerts¶

Power BI Metrics¶

Monitor via Power BI Admin Portal: - Refresh success/failure rates - Refresh duration trends - Data size growth

Custom Monitoring¶

Create monitoring dataset:

let
    RefreshLog = #table(
        {"Timestamp", "Status", "Duration", "RowsProcessed"},
        {
            {DateTime.LocalNow(), "Success", 45, 10000}
        }
    )
in
    RefreshLog

Cost Analysis¶

Estimated Monthly Costs¶

Component	Cost	Notes
Power BI Pro (5 users)	$50	$10/user/month
Matomo Cloud	Included	Part of existing subscription
Data Gateway (optional)	$0	Personal mode free
Total	$50/month	Minimal infrastructure

Cost Optimization Tips¶

Use query folding to reduce data transfer
Implement incremental refresh to minimize API calls
Cache frequently accessed data
Schedule refreshes during off-peak hours

Limitations¶

Maximum 1GB per dataset (Pro license)
10GB per dataset (Premium)
API rate limits vary by Matomo plan
No real-time data (minimum 15-minute delay)
Limited to 48 refreshes/day (Premium)

Next Steps¶

Start with a single report as proof of concept
Gradually add more data sources
Optimize queries based on usage patterns
Consider Azure Data Lake Integration for larger datasets
Evaluate Real-time Streaming if live data is required