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?
- RobFlanders9 years agoFrequent Visitor
For anyone who comes across this in future, the LinkedIn link above contains a sample script to recursively access Microsoft Graph API. However cut and pasting the code will not work as there are a number of errors - which are described in the comments at the bottom. The corrected code can be found here:
// ############## BEGIN QUERY ############################ let // Define a Function Called GetUserDetail that takes a single parameter Path // that is the MS Graph API Endpoint GetUserDetail = (Path)=> let // call out to the web endpoint and convert the result to a JSON // Document object which is saved in the Source variable Source = Json.Document(Web.Contents(Path)), // Assign the resultant List object to NextList variable NextList= @Source[value], // recursively call GetUSerDetail function using the value of the // @odata.nextLink as the path // Since @odata.nextLink is not guaranteed to always exist in the case where // the result is less than 100 we need to catch this and deal with it // using a try otherwise basically means if you error out because no // @odata.nextLink exisit then just return the resulting list of values you did get result = try @NextList & @GetUserDetail(Source[#"@odata.nextLink"]) otherwise @NextList in // Return the result variable as the function return result, // Use the function GetUserDetail with the initial path to the object. // The function will run recursively until it gets all the record and then // loads the full result to UserDetail Variable UserDetail = GetUserDetail("https://graph.microsoft.com/beta/users?$expand=manager")
in
UserDetail
// ##################### END QUERY ####################### - dkay84_PowerBI9 years agoMicrosoft Employee
Unfortunately this is a current limitation of the service. I believe the PG is actively working on or discussing enabling this type of query to be refreshable but AFAIK there is no workaround.
- wlknsnBI6 years agoHelper IICan't get this to work on power bi service. Anyone else tried it?