Iterating Post rest API call using cursor paging

[LearnPowerBI01]

Hi All,

I've a requirement to fetch data from an rest api which is designed for POST call, in which from and to date need to be passed in the body and since the limit of each call is 100 records, cursor of the last retrieved record needs to be passed for paging and iterating thru the subsequent api calls.

I've written a function getUserData, to which I'm passing from and to date and cursor as parameters.

And below is how I'm trying to iterate thru this function, but I'm able to retrieve only the first 100 records, it seems like the cursor is not being set to the last record's cursor value, so next records are not being retrieved

let
inCursor = null,
from_date = Date.ToText(Date.From(Date.AddDays((DateTime.LocalNow()), -90)),"yyyy-MM-dd") ,
to_date = Date.ToText(Date.From(Date.AddDays((DateTime.LocalNow()), -1)),"yyyy-MM-dd") ,

GeneratedList =
List.Generate(
()=>[i=0, res = try getUserData(from_date,to_date,inCursor) otherwise null],
each [i] < 20,
each [i=[i]+1, inCursor = try getUserData([from_date],[to_date],[inCursor])[records][cursor] otherwise null, res = getUserData(from_date,to_date,inCursor)[usersAggregateActivityStats]],
each [res])
in
GeneratedList

The first iteration results in the below output:

Under records, I can see that the cursor details are present. I've tried to retrieve and use this, but somehow I'm unable to get the last record's cursor value and null is passed to inCursor, causing it to fetch the first record over and over until end of iterations.

I've gone thru multiple posts on api iteration and have tried multiple approaches but didn't seem to help, hence writing the query here. Can you please advise/help with this issue.

Thank you!

[LearnPowerBI01]

Sorry I'm unable to share the API details since it's a third party API and the AP keys etc are used as part of the function.

But the real issue I'm trying to seek help with is why is the cursor not being updated post each API call, as I need to pass the last cursor value with the next call, so as to fetch the next 100 set of records.

let
inCursor = null,
from_date = Date.ToText(Date.From(Date.AddDays((DateTime.LocalNow()), -90)),"yyyy-MM-dd") ,
to_date = Date.ToText(Date.From(Date.AddDays((DateTime.LocalNow()), -1)),"yyyy-MM-dd") ,

GeneratedList =
List.Generate(
()=>[i=0, res = try getUserData(from_date,to_date,inCursor) otherwise null],
each [i] < 20,
each [i=[i]+1, inCursor = try getUserData([from_date],[to_date],[inCursor])[records][cursor] otherwise null, res = getUserData(from_date,to_date,inCursor)[usersAggregateActivityStats]],
each [res])
in

I'm trying to get the cursor details using the highlighed statement, do you see any issues with the way I'm extracting it?
GeneratedList

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons). You have to figure that one out on your own, or you can involve a Microsoft partner in your area.

[lbendlin]

This is the definitive article on that subject: Handling paging for Power Query connectors - Power Query | Microsoft Learn

[LearnPowerBI01]

Thank you for the response I've gone through the post, but unable to work out how to retrieve the next cursor from the result, and pass it to the next iteration. 

I need help with that.

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons)