Power BI

Power BI's Web connector cannot request an OAuth token on its own. Use a Power Query that requests the token and downloads all the pages of data.

#Before you start

1. In the portal, open Tenants → Edit → API and create a client named Power BI.
2. Select only the data the report needs, for example Devices and Compliance.
3. Copy the Client ID, the Client secret and the API address.

#Create the parameters

In Power BI Desktop: Transform data → Manage Parameters → New Parameter. Create three text parameters:

Keeping the secret in a parameter makes it easy to update when you renew it.

#Create the query

Get data → Blank query → Advanced Editor, then paste:

let
    // 1. Token, valid 60 minutes
    TokenResponse = Json.Document(Web.Contents(ApiBaseUrl, [
        RelativePath = "connect/token",
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = Text.ToBinary(Uri.BuildQueryString([
            grant_type = "client_credentials",
            client_id = ApiClientId,
            client_secret = ApiClientSecret]))
    ])),
    Token = TokenResponse[access_token],

    // 2. One page of data; nextCursor leads to the next page
    GetPage = (cursor as nullable text) => Json.Document(Web.Contents(ApiBaseUrl, [
        RelativePath = "v1/devices",
        Query = [pageSize = "500"] & (if cursor = null then [] else [cursor = cursor]),
        Headers = [Authorization = "Bearer " & Token]
    ])),

    // 3. All pages, until nextCursor is empty
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each if _[nextCursor] = null then null else GetPage(_[nextCursor])),
    Devices = Table.FromRecords(List.Combine(List.Transform(Pages, each _[data])))
in
    Devices

Name the query Devices. For other data, duplicate the query and change RelativePath:

When Power BI asks how to connect to the API address, choose Anonymous. The query handles authentication.

#Scheduled refresh in the Power BI service

1. Publish the report.
2. In the dataset settings, open Data source credentials for the API address.
3. Choose Anonymous as authentication method and select Skip test connection.
4. Set the privacy level to Organizational.
5. Configure the refresh schedule.

Each refresh requests one token per query. With 8 refreshes per day the report stays well within the API limits.

#Troubleshooting