Forum Discussion
Can't refresh dynamic API URL in Power BI service
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
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-sources
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.
11 Replies
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
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.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
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-sources
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 SourceI 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
Hello jimbob2285,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
Hi v-ssriganesh
Have you seen my reply below to your earlier message? I think I've configured the M code correctly now, but it won't refresh in the service, it's not authenticating. It refreshes OK in Power Query and Desktop though
Cheers
Jim
