Paginated APT M code Iterating through pages not getting last page

Hi jimbob2285 ,

This is a classic pagination issue where the loop stops one step too soon. The problem is that your List.Generate function checks the more_items_in_collection flag after it fetches a page. When it retrieves the final page, the flag is false, causing the loop to terminate immediately without actually adding that last page's data to your results.

To fix this and also address your concern about API token costs, you can make two adjustments to your M code. First, modify your GetPage function to prevent it from making a final, unnecessary API call when it runs out of pages.

// 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],

Next, replace your existing AllPages step with the following List.Generate logic. This new structure cleverly uses the More flag from the previous step to decide whether to continue, which ensures the loop runs one last time to capture the final page of data.

// 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]
),

These changes work together to create a more robust pagination loop. The List.Generate function now correctly fetches every page, including the last one. The updated GetPage function supports this by gracefully handling the final step where there is no next_start value, preventing an extra API call and saving on token costs.

Best regards,