Forum Discussion
how to fully load data from limited HTTP API?
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?
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.
- 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 ####################### - wlknsnBI6 years agoHelper IICan't get this to work on power bi service. Anyone else tried it?