Power query - Recursive REST API call / pagination - Total pages not known

[bajesh007]

Hello all,

I need some help with looping for paginated API call.

My scenario is that I need to make two calls to rest api. First call to be made with some parameters, which will fetch a scrollid and first set of records. Following which a subsequent call to be made/looped until there is no data is returned, meaning the total number pages in the subsequent call is unknown.

Details/steps are as follows:

1) Call the initial/base url once; with some parameters which will fetch a scrollId and first set of dataset.

2) Subsequent calls to be made using same url by providing the only the scrollid as the parameter which is taken from the base url. This call has to be looped until this api call fetches no data.

Rest Endpoint Url details:
a) Base url : http://server:9950/ExcpnReport?context=ab-20210511-40433&book=abcd&pagesize=25
b) subsequent url : http://server:9950/ExcpnReport?context=ab-20210511-40433&scrollid=AbcdefghIjkl

Sample Json output from base url (FIRST CALL):
{"ScrollId": "AbcdefghIjkl", "Exceptions":[{TradeId:"ab1cd","expected":0,"unexpected":50......}]}

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

Any help will be much appreciated or even tips to point me in the right direction would be amazing.

Thanks in advance.

[PhilipTreacy · Accepted solution]

Hi @bajesh007 

This is the same question as this ?

List.Generate function condition not met - REST AP... - Microsoft Power BI Community

See my response there and close this one if you can.

Regards

Phil