web api with cursor pagination

[BE50]

With Power Query in Excel I am retrieving data via an Open API that is using pagination (1000 pages per call).

I use a function to get one page:

//fnOnePage structure
(iCursor) =>
let
    sCursor=Number.ToText(iCursor),
    sUrl = "https://somewebsite.com/timeentries/all?" & "&cursor=" & sCursor, 
    source = try Json.Document(Web.Contents(sUrl, 
        [Headers=[sHeader1, sHeader2]] ))
in
    source

When invoked fnOnePage(1) returns [Value][List] and[Value][cursor]. The list has up to 999 records. After adding a few steps I can load to workbook.

To get the full list (based on date interval or other filters) I call the function from this snippet (working, no errors):

let
 iCursor = 1,
 GeneratedList= List.Generate(
                ()=> iCursor,
                each iCursor<>null,
                each [res = try fnOnePage(iCursor)[Value][items] otherwise null],
                each [res])
in
    GeneratedList

It returns a list of lists with the records I need. Now I could convert to table etc. but there are 2 issues:

1) First item on the list is an error: Expression.Error: We cannot apply field access to the type Number. Details: Value=-1, Key=res

2) There seems to be an infinite number of lists and records. After expanding etc. I load to worksheet. During load I se "123.456 rows loaded". When "Loading ..." reach 1 million I cancel. In PQ I use Statistics > Count values on the list of lists: It nevers stop counting.

SOLUTION: I got this to work (by surfing and trial and error):

    sCursor="1",
    sFilter = "filter=date$gte:2023-01-01$and:date$lte:2023-04-30",
    Result = List.Generate(      
        () => [inCursor = sCursor, data = fnOnePage(sCursor)[Value][items]],
        each [inCursor] <> null,
        each [inCursor = try fnOnePage([inCursor])[Value][cursor] otherwise null, data = fnOnePage(inCursor)[Value][items]],
        each [data]
    ),

This is the basic code. The actual solution has more parameters, and fnOnePage adds headers, tokens and other stuff.

[lbendlin]

Please follow the documentation of Web.Contents to use Relative Path and Query options. If you don't do that your query will not work in the service.

Before you can combine the [res]  values you need to translate the JSON into a table format.