Can't refresh dynamic API URL in Power BI service

[jimbob2285]

Hi

I have a paginated API data source in a Power BI report, that used the following M code to iterate through and combine multiple pages into a single table, but I then can't refresh the report or schedule a refresh for the report in the Power BI service, because it's a dynamic data source.

let
    // Base URL and parameters
    BaseUrl = "[URL]",
    ApiToken = "[API Token]",
    Limit = 500,
    InitialStart = 0,

    // Function to fetch one page of results
    GetPage = (Start as number) =>
        if Start = null then
            [Data = {}, More = false, NextStart = null]
        else
            let
                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                NextStart = try Response[additional_data][pagination][next_start] otherwise null
            in
                [Data = Data, More = More, NextStart = NextStart],

    // Loop through all pages using List.Generate
    AllPages = List.Generate(
        () => [Result = GetPage(InitialStart), Continue = true],
        each [Continue],
        each [
            Result = GetPage([Result][NextStart]),
            Continue = [Result][More]
        ],
        each [Result][Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),
 
    // Convert to table, automatically detecting columns
    RawTable = Table.FromRecords(Combined),
 
in
    RawTable

Has nayone come acros this before and can you tell me how to resolve it, I'm currently opening the report in Power BI desktop each morning to refresh it and then re-publishing the refreshed report to the service, but this isn't a sustainable solution

I've seen a video where they changed the dynamic URL to a Static URL, using [RelativePath = BaseURL & ""], but I couldn't get that to work for me, I'm guessing because mine is wrapped in a function?

I've also read about using a call into the API to trigger a refresh with a RefreshData endpoint, but the API I'm using doesn't have this endpoint

I can't belive I can build a report in Power BI Desktop with a data souce that i can refresh from there, but that I can't refresh from the Power BI service, there must be a solution

Any help would really be appreciated.  I can't keep manually refreshing for ever more

Cheers

Jim

[jimbob2285]

Hi

thanks @BA_Pete  and @v-ssriganesh  for your responses, but I just can't get the syntax right, I'm new to M code and while RelativePath is clearly the answer, to my problem, I can't get it to work - It's failing to authenticate, which tells me I'm nearly there, but not quite

Can anyone help me get this right please? I can't find a decent guide to RelativePath anywhere online and I just don't understand the syntax

// Dynamic Path - Won;t allow refresh from Power BI Service
//                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
//                Response = Json.Document(Web.Contents(Url)),

// Attempt at Static Path - To allow refresh from Power BI Service 
                Url = "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(
                    Web.Contents(
                        BaseUrl, [
//                            RelativePath = "/v1/organizations?start=" & Url
//                            RelativePath = "/v1/organizations?start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "api_token=" & ApiToken
//                            RelativePath = "/v1/organizations?",
                            RelativePath = "/v1/organizations?start=" & Text.From(Start) & "&limit=" & Text.From(Limit),
//                            Query = "start=" & Text.From(Start) & "&limit=" & Text.From(Limit),
                            ApiKeyName = "api_token=" & ApiToken
//                            Authorization = "api_token=" & ApiToken
                        ]
                    )
                ),

Cheers

Jim

[v-ssriganesh]

Hello @jimbob2285,
Thanks for getting back and for providing those additional details.

You're absolutely on the right track using RelativePath with Web.Contents is the correct solution to make your API call refreshable in the Power BI Service. You're very close, and the issue now seems to be related to how the parameters and authentication are being passed in the M code.

Key things to check:

• Keep your BaseUrl simple and static. It should not include parameters or dynamic segments.
• Use RelativePath only for the fixed portion of the endpoint path like /v1/organizations.
• Pass start, limit, and api_token using the Query option as a record. This is critical Power BI expects query parameters to be passed in this structured format to allow refresh in the Service.
• Avoid string concatenation inside RelativePath or Query. These should be clearly separated.
• If your API uses an API key in the query string, include it in the Query record as well. If it uses headers, you’ll need to configure it using the Headers option inside Web.Contents.

Here are two official docs that explain this pattern clearly:

• Web.Contents with RelativePath & Query: https://learn.microsoft.com/en-us/powerquery-m/web-contents
• Why dynamic URLs are blocked: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-source...

[jimbob2285]

Hi @v-ssriganesh 

Thanks for this, I've already seen both those two docs and didn't find them very helpful.  Despite my best efforts, I can't seem to make this work, it's failing authentication, as if it's not paasing the API key correctly.

So I've stripped it back to a more simple, none paginated query on the same endpoint:

• So, there are no Start or Limit parameters to pass
• Switching the Auto-generated M code for the RelativePath and Query approach (See below)
• But I still can't get it to work
• However, concatenting these three aspects together in the standard (non RelativePath and Query) method works fine

I've tried to follow your guidance but I'm not sure what you mean by:

• Power BI expects query parameters to be passed in this structured format, do you mean that i can pass several Query Parameters, but it didn;t seem to like any more than 1 query parameter
• Avoid string concatenation inside RelativePath or Query. These should be clearly separated - i think I've done this, but it still doesn't like it 

let
    BaseUrl = [Base URL],
    ApiToken = "api_token=" & [API Token],
//    Source = Json.Document(Web.Contents(BaseUrl & "/api/v1/organizations?" & ApiToken))
    Source =    Json.Document(
                    Web.Contents(
                        BaseUrl, [
                            RelativePath =  "/v1/organizations?",
                            Query = ApiToken
                        ]
                    )
                )
in
    Source

I just can't work out what I'm doing wrong here - it looks like it should work, but it's not... What am i doing wrong?

Cheers

Jim

[v-ssriganesh]

Hello @jimbob2285,
Thanks for sharing more details, you're really close. The issue now is with how the Query parameter is being passed.

Power BI expects Query to be in a structured format using key-value pairs, not as a single concatenated string like "api_token=XXXX". This is a common roadblock. Instead of building one string, you should treat each query parameter as a separate item in a list (or "record") so Power BI can safely handle them and construct the URL properly for both Desktop and Service refresh.

[v-ssriganesh]

Hello @jimbob2285,
Thank you for reaching out to the Microsoft Fabric Forum Community.

Power BI Service does not allow refresh of dynamic data sources for security and privacy reasons. This specifically affects queries where the URL is built dynamically inside a function or loop, which works fine in Desktop but is blocked in the Service.

To make your API call refreshable in the Service:

• You’ll need to restructure the API call to use static base URLs.
• Use Power BI’s advanced options for Web.Contents, such as RelativePath and Query, to safely pass parameters like start, limit and api_token.

This approach avoids the dynamic URL issue and ensures that your query meets the Power BI Service’s data privacy and security rules.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[BA_Pete]

Hi @jimbob2285 ,

My initial guess would be that you need to use RelativePath + Query for your URL.
Have a look at this short blog about it. Should set you on the right... path:

Handling Dynamic Data Sources in Power BI: RelativePath and Query Option – linusdata

Pete