Forum Discussion
how to fully load data from limited HTTP API?
I managed to load 100% of the limited query, but the script if far from perfect.
I am still looking for a script that checks if offset < next_offset and then stops.
the aproach I used so far is: I created a list of offset values, and the script loads all data parts.
if I set a low number for the offset possibilities, the script will miss the last values.
if I set a high number, the script will return a lot of empty rows after the full load is finished.
the script below loads all rows until the end at row 4323.
but it continues to load more lines until the 4400 limit that I set (inicial source list goes from 0 to 4400).
for example, at this row 4323, this is when the offset and next_offset are the same and the script must stop, but i could not work on a script using this logic.
let
Source = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),
Source1 = {0..45}, //list of total pages in my full dataset
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Personalizar", each [Column1]*100), //times 100 because my offset is at every 100 rows
#"Columns Renamed" = Table.RenameColumns(#"Added Custom",{{"Personalizar", "Column2"}}),
#"Added Custom" = Table.AddColumn(#"Columns Renamed", "Custom", each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]) & "&date_range=201001010000:201912310000")))
in
#"Added Custom"
I can work with this script for a short period of time, as it is not future proof.
But now I found another 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 allow 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?