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)

[BRVL]

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