List.Generate function condition not met - REST API - Paginated results

[bajesh007]

Hello all,

Need your help with List.Generate function. I need to call paginated REST api end point recursively to fetch data until no more data exists as total number of pages is unknown. (Note : Only one parameter is supplied which is a standard scrollid generated by another rest end point).

Based on other posts, I tried calling a function and checking if [Result] is null, but doesnt seems to work as it is fetching more records than the original record count. (list is getting duplicated again and again!)

How to check if the List named "Exceptions" (shown below) has any data so that i can exit the custom function.

Url details:
http://server:9950/ExcpnReport?scrollid=AbcdefghIjkl

Sample Json output from above endpoint:
{"ScrollId": "AbcdefghIjkl", "Exceptions":[{TradeId:"XYZ","expected":10,"unexpected":15......}]}

Thanks in advance.

[PhilipTreacy · Accepted solution]

Hi @bajesh007 

You seem to have posted 2 separate questions about the same thing.  That's confusing!  Best to stick with just 1 post on each topic.  Close the other one.

There's a couple of things here that make this difficult.  You haven't said under what condition to terminate the API calls.  What value returned by the API tells you that there's no more data?

Secondly, the JSON data you've provided isn't complete so I can't write a query to parse that.  Need an entire response from the API to understand what data it returns, initially, while there ar emore records to retrieve, and when it's returned the last records.

I'd probably tackle this using something like this with List.Generate, but can't offer more without the information I just requested.

let
    Source = List.Generate(
    () => [ data = Json.Document(Web.Contents("http://server:9950/ExcpnReport?context=ab-20210511-40433&book=abcd&pagesize=25")), 
            ScrollID = ""
          ],

    each [ScrollID] < "???" ,  // What is the condition to terminate the loop ?

    each [ ScrollID = [data][ScrollID], data = Json.Document(Web.Contents("http://server:9950/ExcpnReport?context=ab-20210511-40433&scrollid="&[ScrollID])) ],

    each [data]
)
in
    Source

Regards

Phil