Hello,
I know there are a lot of posts on this subject and I've looked through a lot of them but I still can't get around this issue.
Background:
I am pulling store foot traffic information from Dor which provides a REST API. I am able to connect the API and pull data but because the data is paginated I have to make multiple requests. Dor's API uses cursor based pagination so I am using the value in the next field to pull the next page of information until it is null. I can get all of this working on Power BI Desktop but if try to refresh my report on Power BI Service or if I try to turn this into a dataflow I get the error that there there is a dynamic data source.
I know the issue is with web.contents, so I took everything that's not part of my base url and moved it to the RelativePath in the query options, however that still doesn't on Power BI Service because it still thinks there's a dynamic data source.
The next url looks something like this:
https://api.getdor.com/v2/location-metrics?next=eyJuZXh0X3Jlc3VsdCI6eyJwdWJfaWQiOiJsb2NfbzNIS0RyVmdx...
So, i modified the FnGetOnePage a little and used Text.Replace to remove the based url and then I am passing what's left as the RelativePath in the next request.
let
iterations = 300,
baseUrl = "https://api.getdor.com/v2/location-metrics",
token = [Headers=[Authorization="Basic XXXXXXXXXXXXXXXXXXXXXXXXXXX"]],
baseOptions = "?datetime_start=2020-08-01T00%3A00%3A00&interval=hour",
FnGetOnePage =
(options) =>
let
Source = Json.Document(Web.Contents("https://api.getdor.com/v2/location-metrics", [RelativePath=options] & [Headers=[Authorization="Basic XXXXXXXXXXXXXXXXXXXXXXXXXXX"]])),
data = try Source[data] otherwise null,
next = try Source[meta][next] otherwise null,
res = [Data=data, Next=Text.Replace(next, baseUrl, "")]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(baseOptions)],
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"
I know the issue is with web.contents but I've tried everything I can think of regarding the RelativePath and Query Options.
Any advice would be much appreciated!
Anthony
