Forum Discussion
workaround for SCHEDULED REFRESH on HTTP API with pages
I can load all pages using a script that cycles through all pages.
But now I found a problem and I need a workaround:
I cannot refresh data online (also scheduled refresh) because of the limitation of the PBI Service.
I am using a function inside the URL and this is not allowed for PBI Service.
In bold, the forbidden function inside URL:
each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]) & "&date_range=201001010000:201912310000")))
Can anyone please help me with another way to load all pages from API and solve this issue?
In another posts, people say that I need to use Query functions, but I am new to M language and looking for help.
I got the answer from outside the community, by another microsoft engineer.
I changed the code to something like this:
Json.Document(Web.Contents("https://url.com/token?offset=0", [Query=[offset=Text.From([Column2])]]))
Where my Column2 have all numbers that I need for paginating and cycling through the API.Using the code above, all my pages are loaded correctly, and now I can refresh using Power BI Service because there is no function inside the URL address.
11 Replies
I know I need to use static values to replace the function. But how can I make a script to load 44 different links?
www.url.com/offset=100, www.url.com/offset=200, going up to www.url.com/offset=4400.
I dont want to load 44 distinct tables and perform append queries later...
my current code uses this, where text.from([column2]) is a number going up from 0, 100, 200.. till 4400,
each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]))))I tried to put all line or parts of it inside a function, without success, where text.from([column2]) is the full line.
each Text.From([Column2])Where text.from([column2]) is a big list with: Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0")), where offset keeps on going +100 on offset value.