Forum Discussion
Dynamic query with next page key parameter from API - How to adapt code for Power BI service?
In the end, I solved my problem using a python script to import the data and write them in excel files.
Kind regards
Marta
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
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.
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 regardsWhen 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.
