Web based cursor pagination issue

[BRVL]

Hi I’ve been working on trying to use List.generate to create a paginated list from the api. My issue is that my api uses Cursor-Based Pagination. A single request will give me two sections if a cursor exists i.e next page

 or one section if it's the final page

If "cursor" is exists, I need to make another request, using the "cursor" value given to get the next page of company records, "payments" is the actual information I need.

The issue is that in the last record the field "cursor" does not exist.

I can't seem to figure out how to write the query to get all the records but to stop list.generate when the field cursor does not exist.

when i try  - each Record.HasFields( _ , "payments" ) = true  , this returns all the pages/rows but causes the error above as the final page has no cursor field.

when i try - each Record.HasFields( _ , "cursor" ) = true  , this returns only the first two pages but not the final page.

I've tried - try each Json.Document(Web.Contents("https://connect.squareup.com/v2/payments?cursor=" & ( try Text.From([cursor]) otherwise null) , [Headers=[Authorization=" token "]] )) otherwise null ) and other variations but can't seem to get it to what i want.

Any help would be great.

[v-yanjiang-msft · Accepted solution]

Hi @BRVL ,

Have you tried to use if function like this:

each if Record.HasFields(_,"cursor"）=true then Json.Document(... else...

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yanjiang-msft · Accepted solution]

Hi @BRVL ,

Have you tried to use if function like this:

each if Record.HasFields(_,"cursor"）=true then Json.Document(... else...

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BRVL · Accepted solution]

Nevermind, I've figured it myself.

[amreet11]

Hi,

Would you mind sharing how you resolved the issue as I am struggling with a similar problem.

Thanks,

Amreet

[BRVL]

I used the below and it solved it for me: 

data = let
Pagination = List.Generate( ()=> Source,
each [payments] <> null ,
each if Record.HasFields(_,"cursor") and [cursor] <>null then Json.Document(Web.Contents("https://connect.squareup.com/v2/payments?cursor=" & Text.From([cursor]), [Headers=[Authorization="Token"]] )) else Record.FromList({null, null}, {"payments","cursor"} ))

in Pagination,
#"Converted to Table1" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)

[jrush4]

Any chance you could give a little more context on how you got this working? I'm currently trying to do something similar with pagination, but can't crack it. Your above code has gotten me closer, but I'm still not there!

[BRVL]

Hi thanks for your reply but this does not change the result.