Dynamic query with next page key parameter from API - How to adapt code for Power BI service?

[PowerBI88]

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
    returnList

  and 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

[PowerBI88]

In the end, I solved my problem using a python script to import the data and write them in excel files.

Kind regards

Marta

[v-priyankata]

Thanks for the information.

[v-priyankata]

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.

[PowerBI88]

I tried implementing the solution but I am not able to make it work.

Kind regards
Marta

[v-priyankata]

Hi @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.

[v-priyankata]

Hi @PowerBI88 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. @lbendlin Thank you for your inputs.
This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[PowerBI88]

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.

[PowerBI88]

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 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     allProblems

here 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 you

[lbendlin]

Don'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.

[PowerBI88]

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

[lbendlin]

When you request results you get two things back - the data and the pointer to the next page.

In the first iteration ignore the data and only harvest the next page links into a list.

In the second iteration retrieve the data from the list of URLs.  This assumes that the first iteration is cached in your web engine.

[PowerBI88]

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     allProblems

here 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 you

[lbendlin]

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