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

[pc2]

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".

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.

[Anonymous]

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

[pc2]

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.