Not able to refresh a dataset due to dynamic queries

[Simon_Evans]

Hi Everyone

I have a lovely set of reports in Desktop all nicely pulling data from a third party API. The below code works perfectly and handles the pagination of the JSON.

let
Students = List.Generate( () =>
[URL = "https://api.customersite.com/v1.0/schools/AXXXXXXXXXXX/students?extra_ids=true&include=year,registra...",

Result = Json.Document(Web.Contents((URL), [Headers=[Authorization="Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]]))],
each [URL] <> null,
each [
URL = [Result][meta][pagination][next],
Result = Json.Document(Web.Contents((URL), [Headers=[Authorization="Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]]))
]

)

But when it comes to the Power BI service I can't do any refreshing due to the dynamic building of the URL. I understand using the Relative Path and Query for WebContents but I cannot see how I can alter my code above to handle the pagination of the JSON object when the number of pages is not known.

Can anyone give me some guidance on how I can achieve this please so I can setup a refresh schedule for the datasets in the Power BI Service?

[v-jayw-msft · Accepted solution]

Hi @Simon_Evans ,

Please see if the following link is helpful to you.

https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-s... 

https://blog.jongallant.com/2021/08/powerbi-dynamic-data-source-refresh/ 

Best Regards,

Jay