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
With some REST APIs, you can get a records count with $count. I usually do that in one step and divide it by the number of records returned per call (250 in your case), and then use List.Numbers(0, countstep/250, 250). That make a list that increments by 250 as many times as needed to get all the records. You then convert that to table and the number column to text, and then concatenate that with the web call in a custom column. From there, you can expand that column to combine the data from all the calls.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thanks for your contribution Mahoneypat,
but my rest service doesn't provide any information about the total amount of records or how many pages have to be visited.
I honestly have the challenge to iterate unitl the amount of records per page are zro.
//joerg
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) => letcounter = 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
resultListin
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!
The requests you are making are:
0 - 250
250 - 500
750 - 1000
1500 - 1750
...
Instead of incrementing by 250 each time, you are incrementing by 250, 500, 750, ect...
As I mentioned, you don't need counter, only offset.
Thanks for the feedback artemus!
but it's working as expected. The interface has two additional parameter: limit and offset.
The offset has to increase as the offset number is used to start by each request.
1 request: offset = 0
2 nd request offset =250
3 rd request offset = 500
....
This is woking as the max. limit is 250 by default.
Anyhow, the function is now working as expected and i've compiled a custom function for reuse in other cases.
//joerg
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
