connect to api with a token

Hi,

I'd try using RelativePath in Web.Contents() instead of separate full URLs. Power BI Service can be a bit picky with dynamic data sources, even when everything works fine in Desktop.

Also, try hardcoding the token in the last request just for testing. If that works, then the issue is probably with how the token is being passed between the API calls rather than the credentials themselves.

One other thing, you might not need to add the quotes around the tokens manually. Json.FromValue() should handle that for you.

Hi aothmani​,

For monitoring scheduled refresh failures across multiple Power BI workspaces, I would recommend using the Power BI REST API with Power Automate or Azure Logic Apps.

Suggested approach:

1. Use the Admin – Get Refreshables API to identify semantic models, refresh failures and refresh schedules across the organisation.
2. Use Get Refresh History In Group to retrieve individual refresh statuses, timestamps and error details.
3. Run the monitoring process periodically using Power Automate, Azure Logic Apps or an Azure Function.
4. Store the results in a central SQL table or Fabric Lakehouse.
5. Trigger email or Teams notifications whenever a refresh fails.
   
   Best Regards,
   Maruthi
   LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
   X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Hi aothmani​,

The error you are seeing is most likely coming from how Power Query is combining the authentication, refresh-token, and reporting calls rather than from the credentials of the final endpoint.

Your reporting request depends on a token returned by the refresh request, and the refresh request in turn depends on the authentication request. Power Query's Data Privacy Firewall can block this type of pattern when a query references another query or value and then directly accesses a data source.

Microsoft explains this behaviour in the Data Privacy Firewall guidance.

I would first keep all calls under one static web origin and use Web.Contents with a fixed base URL plus RelativePath rather than constructing separate full URLs dynamically.

For example:

Web.Contents("https://server/", [RelativePath="party/refresh", ...])

and:

Web.Contents("https://server/", [RelativePath="reporting/permits", Query=[offset="12", sort_column="date_crea", sort_direction="desc"], ...])

Microsoft's Power BI refresh guidance specifically documents RelativePath and Query as supported exceptions for web requests that otherwise become dynamic data sources in the Service.

I would also make sure the privacy level for the web source is configured consistently in the Service. Microsoft now allows queries that reference other partitions and access data sources when their privacy levels are compatible, but mismatched privacy settings can still produce "Unable to combine data" failures.

One more thing: if possible, keep the authentication and refresh-token logic inside the same query/function rather than splitting each request into separate loaded queries. That reduces the number of Power Query partitions involved and usually makes the Firewall behaviour easier to control.

So I would troubleshoot this in this order:

• use one fixed base URL
• move endpoints into RelativePath
• use the Query option for URL parameters
• keep the token chain inside one query or function
• set the same privacy level for the web source in the Service

I would not start by changing the final endpoint's credentials, because the final request is using a bearer token generated inside M rather than a separate Power BI credential.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

There are a couple of potential issues with this.

Firstly, the ID token is being wrapped in quotations:

AddQuotes = Table.AddColumn(
    RefTable,
    "Quotes",
    each """" & [Value] & """"
)

Headers = [token = id_token]

So, it can send 

token: "eyJhbGciOi..."

It could be rejecting that as an invalid token.

The second thing I noticed is that the permits request body is never used. It defines Content, yet the final request isn't using it. For the Roadworks API shown in the example, the permits endpoint is a GET request that accepts query parameters. Those values should be passed using Query, not a JSON request body.

Thirdly, Power BI Service may be testing the endpoint without the generated token.

Even if the request works in Desktop, the Service's credential validation can fail before executing the complete authentication sequence. A static URL with RelativePath is also preferable for scheduled refresh because Power BI Service restricts dynamic data sources.

If I were to rewrite the example, I would try something like:

let
    BaseUrl = "https://api.manage-roadworks.service.gov.uk/v4/",

    // 1. Authenticate
    AuthResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "work/authenticate",
                    Headers = [
                        #"Content-Type" = "application/json"
                    ],
                    Content = Json.FromValue([
                        emailAddress = ApiEmail,
                        password = ApiPassword
                    ])
                ]
            )
        ),

    RefreshToken =
        Text.From(AuthResponse[refreshToken]),

    // 2. Refresh the token
    RefreshResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "party/refresh",
                    Headers = [
                        #"Content-Type" = "application/json"
                    ],
                    Content = Json.FromValue([
                        refresh_token = RefreshToken
                    ])
                ]
            )
        ),

    IdToken =
        Text.From(RefreshResponse[id_token]),

    // 3. Retrieve permits
    PermitsResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "reporting/permits",
                    Headers = [
                        token = IdToken,
                        #"Content-Type" = "application/json"
                    ],
                    Query = [
                        offset = "12",
                        sort_column = "date_create",
                        sort_direction = "desc"
                    ]
                ]
            )
        ),

    // 4. Convert response to table
    ConvertedToTable =
        Record.ToTable(PermitsResponse),

    Value =
        ConvertedToTable{1}[Value],

    ConvertedToTable1 =
        Table.FromList(
            Value,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),

    ExpandedColumn1 =
        Table.ExpandRecordColumn(
            ConvertedToTable1,
            "Column1",
            {"x", "y", "pz"}
        )

in
    ExpandedColumn1

Also double check the Power Bi Service Settings:
Web.Contents POST requests require Anonymous credentials at the Power Query connector level; the API token is supplied separately in the request.

1. Open the semantic model's Settings.
2. Open Data source credentials.
3. Select Anonymous authentication for the web source.
4. Set an appropriate, consistent privacy level.
5. If available, select Skip test connection.
6. Save and perform a manual refresh before enabling the schedule
   
   Another note:
   

   For authenticating once every 24 hours and refreshing the token hourly,

   Powerer Query doesn't persist its token variables between scheduled refresh runs. This code authenticates and refreshes the token again whenever the dataset refreshes.

   If the API strictly limits authentication to once per day, you'll need external token management, such as a custom connector or an Azure Function, rather than relying entirely on M.