Forum Discussion
Dynamic query with next page key parameter from API - How to adapt code for Power BI service?
Hi,
I am using a dynamic query in power bi desktop in order to extract all the rows from a Dynatrace API that lists all dynatrace problems. The Dynatrace api returns paginated results with a nextpage key to query for the next bunch of rows.
So I created in power BI desktop the following function called GetProblemApiResult:
= (api as text, headers as record, parameters as text, nextPageKey as text, currentList as list) =>
let
apiResult = if nextPageKey = ""
then Json.Document(Web.Contents(api & parameters, headers))
else Json.Document(Web.Contents(api & "?nextPageKey=" & nextPageKey, headers)),
newList = List.Combine({currentList, apiResult[problems]}),
hasNext = try apiResult[nextPageKey],
returnList = if hasNext[HasError]
then newList
else getProblemAPIResult(api, headers, parameters, apiResult[nextPageKey], newList)
in
returnListand then I call it in the following way:
= getProblemAPIResult("https://baseurl/api" & "/v2/problems", [Headers=[Accept="application/json; charset=utf-8", Authorization="Api-Token " & "mytoken"]], "?pageSize=500&problemSelector=problemFilterNames%28%22SNOW%20INTEGRATION%20%5BPROD%5D%22%29&entitySelector=type%28%22SYNTHETIC_TEST%22%29&from=" & "2024-01-01T00%3A00%3A01&to=now" , "", {})when I refresh the report in Power BI service it tells me that I cannot refresh a dynamic datasource.
Could you tell me how can I adjust the code so as to be able to refresh the report also on the Power BI Service?
Thank you
Kind Regards
Marta
In the end, I solved my problem using a python script to import the data and write them in excel files.
Kind regards
Marta
13 Replies
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
Handling paging for Power Query connectors - Power Query | Microsoft Learn
Hi, I followed the documentation but I still get the following error:
This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Here is the function call:= let baseURL = "https://baseurl/api", endpoint = "/v2/problems", queryParams = [ pageSize = "500", from = "2024-01-01T00:00:01", to = "now" ], apiToken = "token", allProblems = getProblemAPIResult(baseURL, endpoint, apiToken, queryParams, null, {}) in allProblemshere is the recursive function with next page Key:
= let getProblemAPIResult = (baseURL as text, endpoint as text, apiToken as text, queryParams as record, nextPageKey as nullable text, collectedData as list) => let queryParamsWithPage = if nextPageKey <> null and nextPageKey <> "" then Record.Combine({queryParams, [nextPageKey = nextPageKey]}) else queryParams, apiResponse = Json.Document(Web.Contents( baseURL, [ RelativePath = endpoint, Query = queryParamsWithPage, Headers = [ Accept = "application/json; charset=utf-8", Authorization = "Api-Token " & apiToken ] ] )), newProblems = try apiResponse[problems]? otherwise {}, combinedData = List.Combine({collectedData, newProblems}), nextPage = try apiResponse[nextPageKey]? otherwise null, finalData = if nextPage = null then combinedData else getProblemAPIResult(baseURL, endpoint, apiToken, queryParams, nextPage, combinedData) in finalData in getProblemAPIResult
Could you tell me where I am mistakening?
Thank youHi,
I followed the documentation but I still get the following error: This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Here is the APi call modified:= let baseURL = "https://baseurl/api", endpoint = "/v2/problems", queryParams = [ pageSize = "500", from = "2024-01-01T00:00:01", to = "now" ], apiToken = "token", allProblems = getProblemAPIResult(baseURL, endpoint, apiToken, queryParams, null, {}) in allProblemshere is the recursive function:
= let getProblemAPIResult = (baseURL as text, endpoint as text, apiToken as text, queryParams as record, nextPageKey as nullable text, collectedData as list) => let queryParamsWithPage = if nextPageKey <> null and nextPageKey <> "" then Record.Combine({queryParams, [nextPageKey = nextPageKey]}) else queryParams, apiResponse = Json.Document(Web.Contents( baseURL, [ RelativePath = endpoint, Query = queryParamsWithPage, Headers = [ Accept = "application/json; charset=utf-8", Authorization = "Api-Token " & apiToken ] ] )), newProblems = try apiResponse[problems]? otherwise {}, combinedData = List.Combine({collectedData, newProblems}), nextPage = try apiResponse[nextPageKey]? otherwise null, finalData = if nextPage = null then combinedData else getProblemAPIResult(baseURL, endpoint, apiToken, queryParams, nextPage, combinedData) in finalData in getProblemAPIResult
Could you tell me where I am mistakening?
Thank youDon't use external functions. Try not to use recursions. Remember that your browser is caching results, so you can first create a list of the nextPageKey values and then fetch the actual data from the cache.
Thank you for the suggestions but your proposed solution is not clear to me. How can I get the list of nextPagekey values in advance?
Alternatively, can I implement the extraction logic with a Power BI service dataflow or with Power Automate? So as to avoid the error on the dynamic datasource on the Service?
thank you
Kind regards
Hi, I have not had still the chance to implement the solution proposed, that's why I have not accepted the answer. I am involved on many projects, so I get not to test it immediately. Plus, I received I guideline so I have to figure out how to implement the complete solution.
Hi PowerBI88,
Did you get a chance to implement the solution? if everything was good, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.Thank you.
I tried implementing the solution but I am not able to make it work.
Kind regards
MartaHi PowerBI88
Thank you for reaching out to the Microsoft Fabric Forum Community.
Can you please try below points.-
The issue is happening because you're passing things like the URL and API token into the function as parameters, and Power BI sees that as dynamic.
-
Power BI Service doesn’t allow queries that build the URL at runtime—it blocks them for security reasons.
-
To fix it, just put the full URL and token directly inside the web.contents call instead of using variables or building them with &.
-
Basically, avoid making the request dynamic—keep it simple and static so Power BI can recognize and approve it during refresh.
-
Once that’s done, the refresh should work fine in the Power BI Service.
If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
Thank you.-
In the end, I solved my problem using a python script to import the data and write them in excel files.
Kind regards
Marta
Thanks for the information.
