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

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,