Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pc2
Helper I
Helper I

Workaround for "dynamic data source" not refreshing on Power BI Service

I am grabbing REST API data and this is how the pagination works for this particular site.  The query limit is 200 records, so it offers pagination, and in addition to the first 200 records under "data", it also provides "next" info under "meta".

 

image.png

image.png

 

Upon googling, I managed to find Chris Webb's post and some variation of it and after copying and pasting, I came up with the following M code:

 

let

 authKey = "Basic " & Binary.ToText(Text.ToBinary("XXXXX:XXXXXXXXX"),0),
 url = "https://api.getdor.com/v2/location-metrics?datetime_start=2020-12-01T00%3A00%3A00&interval=hour",

    iterations = 100,
 FnGetOnePage =
     (url) as record =>
      let
      Options = [Headers=[ #"Authorization" = authKey ]],
       Source = Json.Document(Web.Contents(url, Options)),
       data = try Source[data] otherwise null,  
      next = try Source[meta][next] otherwise null,
    res = [Data=data, Next=next]
      in
       res,

    GeneratedList =
     List.Generate(
      ()=>[i=0, res = FnGetOnePage(url)],
      each [i]<iterations and [res][Data]<>null,
      each [i=[i]+1, res = FnGetOnePage([res][Next])],
      each [res][Data]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
 #"Converted to Table"

 

This code works beautifully and it works in Power BI Desktop.

 

The issue is with Power BI Service.  The M code includes dynamic URL (based on the URL provided in "next" response) and Power BI Service does not like this.

 

So, I tried RelativePath option and revised the code below:

 

let

 authKey = "Basic " & Binary.ToText(Text.ToBinary("XXXXXXXX:XXXXXXXXXX"),0),
 url = "https://api.getdor.com/",
RelativePathURL = "v2/location-metrics?datetime_start=2020-12-01T00%3A00%3A00&interval=hour",
    iterations = 100,
 FnGetOnePage =
     (url) as record =>
      let
      Options = [Headers=[ #"Authorization" = authKey ],RelativePath=RelativePathURL],
       Source = Json.Document(Web.Contents(url, Options)),
       data = try Source[data] otherwise null,  
      next = try Source[meta][next] otherwise null,
    res = [Data=data, Next=next]
      in
       res,

    GeneratedList =
     List.Generate(
      ()=>[i=0, res = FnGetOnePage(url)],
      each [i]<iterations and [res][Data]<>null,
      each [i=[i]+1, res = FnGetOnePage([res][Next])],
      each [res][Data]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

 

However, this M code returns only the 1st 200 records; the loop isn't working in this case with RelativePath.

 

What am I doing wrong?  Is there an alternate way to write the M code to bypass the "dynamic data source" error?

 

Thanks for your help.

2 REPLIES 2
Anonymous
Not applicable

Hi @pc2

 

I could not find a workaround to this particular issue, where I had this problem I had to go to a simplified model in which I had to provide some pre-defined values to PBI. It just does not like complete random. In my case instead of using a text parameter as a Sharepoint address (kind of if A then Sharepoint.Files (textA) else Sharepoint.Files (textB) ) to define both connections separately and then use parameters to if.

In your case, I guess a workaround could be using something like 

https://api.getdor.com/v2/location-metrics?datetime_start=2020-12-01T00%3A00%3A00&interval=hour;PAGE=201

i.e. sending exact URL to the function instead of a generated based on the previous step (if you can predetermine it of course). In this case, you can generate a table of URLs first, add column calling the function for each URL and then combine the outputs.

 

Kind regards,

JB

Hi @Anonymous ,

Thank you so much for your input.

Unfortunately, the subsequent pages are not in page numbers (e.g. page2, page3,....).  The next URL it provides is dynamic and there's no way I can know or guess it.

 

For example, the 2nd page URL would look like below:

"https://api.getdor.com/v2/location-metrics?next=eBZxqwoeiqew32fqew9ivovosijfewe23rfasldf"

After the base URL, it uses "next=" to provide the pagination URL.

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors