Adapt M code for pagination from Start and Limit to Cursor and Limit

[jimbob2285]

Hi

The below M code iterates through the pages of a paginated API and brings them together into a single table of data.

However, one of the endpoints uses a different version of the API (v2), which supports Cursor and Limit, rather than Start and Limit, and I don't have the skills or knowledge in my toolbox to make the necessary adjustments.  I'm only just getting started with M code.

Is anyone able to show me what I need to change to make the switch from Start to Cursor:

let
    // Base URL and parameters
    BaseUrl = "[URL]/[Endpoint]?",
    ApiToken = "[API Key]",
    Limit = 500,
    InitialStart = 0,

    // Function to fetch one page of results
    GetPage = (Start as number) =>
        if Start = null then
            [Data = {}, More = false, NextStart = null]
        else
            let
                Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                NextStart = try Response[additional_data][pagination][next_start] otherwise null
            in
                [Data = Data, More = More, NextStart = NextStart],

    // Loop through all pages using List.Generate
    AllPages = List.Generate(
        () => [Result = GetPage(InitialStart), Continue = true],
        each [Continue],
        each [
            Result = GetPage([Result][NextStart]),
            Continue = [Result][More]
        ],
        each [Result][Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),
 
    // Convert to table, automatically detecting columns
    RawTable = Table.FromRecords(Combined)
    
in
    RawTable

Thanks

Jim

@v-kpoloju-msft 

[jimbob2285]

Hi @DataNinja777 

I've just noticed that while the code you modified for me works, i.e. it doesn't throw an error, it's only returning the first page, not all pages.  I've reposted my code below, just to make sure it's nothing I've done, but I'm pretty confident it's the code you provided.

Would you ne anel to take a look for me please and tell me what I'm doing wrong?

Could it be the NextCursor column, if that's not the correct field name, would it work, but just return the first page because it can't get a cursor for subsequent pages?  I can't see the NextCursor field in the data set to check the field name

I've also anotated in the code where I think I need to get the  NextCursor field name correct is that the right place.

If I fetch the endpoint without pagination, then i can see the NextCursor column is called "additional_data.next_cursor", it's part of group fields that need to be expanded, but I don't know if it'll need the "additional_data" prefix, but I've tried several variants and it still doesn't work.  Do i need to expand the "additional_data" field into it's seperate columns in order to see the field????

let
    // Base URL and parameters
    BaseUrl = "[URL]/[Endpoint]?",
    ApiToken = "[API Token]",
    Limit = 100,

    // Function to fetch one page of results using a cursor
    GetPage = (Cursor as nullable text) =>
        // If the cursor is null, it means the previous page was the last one.
        // We return an empty record to safely terminate the loop.
        if Cursor = null then
            [Data = {}, More = false, NextCursor = null]
        else
            let
                // For the very first API call, we don't have a cursor yet.
                // For all other calls, we add the cursor parameter to the URL.
                Url = BaseUrl & "limit=" & Text.From(Limit) & "&api_token=" & ApiToken & 
                      (if Text.StartsWith(Cursor, "FIRST_PAGE") then "" else "&cursor=" & Cursor),
                      
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                
                // IMPORTANT: Verify this path matches your v2 API response for the next page's cursor.
                NextCursor = try Response[additional_data][pagination][next_cursor] otherwise null
            in
                [Data = Data, More = More, NextCursor = NextCursor],

    // Loop through all pages using List.Generate
    AllPages = List.Generate(
        
        // Initial Call: Use a placeholder "FIRST_PAGE" to signify the first request.
        () => [Result = GetPage("FIRST_PAGE"), Continue = true],
        
        // Continue while the 'Continue' flag from the prior step is true.
        each [Continue],
        
        // Next Call: Get the next page using the 'NextCursor' from the previous result.
        each [
            Result = GetPage([Result][NextCursor]), // IS IT HERE THAT I NEED TO GET THE NEXT CURSOR FIELD NAME CORRECT
            Continue = [Result][More]
        ],
        
        // Select the data from each page's result.
        each [Result][Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),
 
    // Convert to table, automatically detecting columns
    RawTable = Table.FromRecords(Combined)

in
    RawTable

Cheers

Jim

[jimbob2285]

Hi @DataNinja777 

I just wanted to let you know that I've sussed it.  From what I can see, the endpoint doesn't contain a "more_items_in_collection" field, so I;ve modified the code to rely solely on the "next_cursor"  field instead, changing

More = try Response[additional_data][more_items_in_collection] = true otherwise false,

to:

More = try Response[additional_data][next_cursor] <> "null" otherwise false

which is working now

I also had to modify [additional_data][pagination] to just [additional_data], to suit this API.

interestingly, while trying to figure this out I couldn't expand the "next_cursor" column from the table, it's as if it just isn't there, but we know that it is.  But when i conenct to the endpoint without paginating, the auto-generated code expands this column fine - Any ideas why this is?

Thanks again for all your help with this, and i now understand M code a little bit

Cheers

Jim

[jimbob2285]

Hi @DataNinja777 

Thanks, that worked a treat, I can't thank you enough for your help

"Gracefully stopping the pagination process"... I like it 😂

Cheers

Jim

[DataNinja777 · Accepted solution]

Hi @jimbob2285 ,

You can adapt your M code from a Start and Limit pagination model to a Cursor and Limit model by modifying the function that fetches each page and how you initiate the loop. The core logic of using List.Generate to iterate through the pages remains an excellent approach.

Here is the updated code snippet adjusted for cursor-based pagination. It preserves your original structure while incorporating the necessary changes for the new API version.

let
    // Base URL and parameters
    BaseUrl = "[URL]/[Endpoint]?",
    ApiToken = "[API Key]",
    Limit = 500,

    // Function to fetch one page of results using a cursor
    GetPage = (Cursor as nullable text) =>
        // If the cursor is null, it means the previous page was the last one.
        // We return an empty record to safely terminate the loop.
        if Cursor = null then
            [Data = {}, More = false, NextCursor = null]
        else
            let
                // For the very first API call, we don't have a cursor yet.
                // For all other calls, we add the cursor parameter to the URL.
                Url = BaseUrl & "limit=" & Text.From(Limit) & "&api_token=" & ApiToken & 
                      (if Text.StartsWith(Cursor, "FIRST_PAGE") then "" else "&cursor=" & Cursor),
                      
                Response = Json.Document(Web.Contents(Url)),
                Data = Response[data],
                More = try Response[additional_data][pagination][more_items_in_collection] = true otherwise false,
                
                // IMPORTANT: Verify this path matches your v2 API response for the next page's cursor.
                NextCursor = try Response[additional_data][pagination][next_cursor] otherwise null
            in
                [Data = Data, More = More, NextCursor = NextCursor],

    // Loop through all pages using List.Generate
    AllPages = List.Generate(
        // Initial Call: Use a placeholder "FIRST_PAGE" to signify the first request.
        () => [Result = GetPage("FIRST_PAGE"), Continue = true],
        
        // Continue while the 'Continue' flag from the prior step is true.
        each [Continue],
        
        // Next Call: Get the next page using the 'NextCursor' from the previous result.
        each [
            Result = GetPage([Result][NextCursor]),
            Continue = [Result][More]
        ],
        
        // Select the data from each page's result.
        each [Result][Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),
 
    // Convert to table, automatically detecting columns
    RawTable = Table.FromRecords(Combined)
    
in
    RawTable

The main adjustment is within the GetPage function. It now accepts a Cursor as text instead of a Start number. To handle the first API call, which doesn't have a cursor, we initiate the List.Generate loop with a special placeholder string, "FIRST_PAGE". The GetPage function checks for this placeholder and omits the &cursor= parameter from the URL for that initial call. For all subsequent calls, it uses the cursor provided by the previous API response.

When the API indicates there are no more pages, it will return a null value for the next_cursor. When this null is passed to the GetPage function in the next iteration, the function returns an empty record, which causes the Continue = [Result][More] condition in the loop to become false, gracefully stopping the pagination process. Remember to verify that the JSON path Response[additional_data][pagination][next_cursor] correctly points to the cursor value in your v2 API's response, as this can vary between APIs.

Best regards,