Forum Discussion
REST api request and loop by offset until no further records found
First, check the request metadata to see if it tells you how many records there are.
E.g.
let
webData = Web.Contents("https://..."),
webMetadata = Value.Metadata(webData)
in
webMetadata
For doing a while loop in power you can instead use recursive functions like:
let my_func = (startIndex) => let webRequest = Web.Contents(...), ...., results = ..., numRecords = ...., if numRecords = page_size then results & my_func(startIndex + page_size) else results in my_func(0)Note the @ used for recursion
Now, i am close to a solution by compiling this invoke function, except on thing ... please HELP
let
Source = (runID as number,offset as number, counter as number) => let
counter = counter +1,
webData = Json.Document(Web.Contents("https://serverurl.com",
[RelativePath="/index.php?/restapiFunc/" & Number.ToText(runID) & "&offset=" & Number.ToText(offset),
Headers=[#"Content-Type"="application/json"]])),
resultList = if List.Count(webData) = 250 then
webData & @all_results_by_id(runID, offset + counter*250, counter)
else
webData
in
resultList
in
Source
----------------------------------------
If the rest responses 2*250 records and the last one for instance 46 records, the overall result of this function keeps 500 and not 546. Means: The last set of less than 250 is missing....
please help before it becomes to frustrating!
Hi Jbruewer,
I have a similar requirement to fetch data from Web : "https://webexapis.com/v1/meetings?meetingType=meeting&from=2019-01-22T04:00:00Z&to=2020-12-31T04:00:00Z"
and im trying to alter the above code you have provided. Would you mind explaining what is "@all_results_by_id" is?
I'm getting an error "The name 'all_results_by_id' wasn't recognized. Make sure it's spelled correctly."
Hi LP2803,
the name of the costom function i've created to invoke into the report is named: "all_results_by_id" and inside of the function it's call it self (recursive) by Filtered Result to date with MoM overview
//joerg