Paginated APT M code Iterating through pages not getting last page

[jimbob2285]

Hi

I've begged, borrowed and stolen the below M code to iterate through the pages of a paginated API, bringing them together into a single table, but it's not picking up the last page:

let
    // Base URL and parameters
    BaseUrl = "[API URL]",
    ApiToken = "[API Token]",
    Limit = 50,
    InitialStart = 0,

    // Function to fetch one page of results
    GetPage = (Start as number) =>
        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(
        () => GetPage(InitialStart),
        each [More] = true,
        each GetPage([NextStart]),
        each [Data]
    ),

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

I've obviously removed the API URL & Key from this code snippet

When the data set contains 120 rows, for instance, it'll only get 100 rows, it's only fetching in multiples of the limit, as if it stops short of the last page because it's only got 20 rows in it.

I get that the code is looping through, fatching each subsequent page until there's no more data, but i don't understand the M code well enough to figure out why it's not getting the last page when there's less entries to fetch than the limit.

I've temporarily set the limit to 1, but there's a token cost with the API for each call, so this isn't a long term solution... especilly as the limit is actually 500

Can anyone please explain why this is happening, or help me modify the code to overcome this

Cheers

Jim

[v-dineshya]

Hi @jimbob2285 ,

Thank you for reaching out to the Microsoft Community Forum.

The issue lies in the List.Generate loop condition.

"each [More] = true", This condition causes the loop to stop before fetching the last page if the last page has More = false, even though that page still contains data.

Your GetPage function does return the last page data, but the loop exits before calling it because, [More] = false right after fetching the previous page, and so the loop condition each [More] = true prevents it from calling GetPage with the final next_start.

Solution: We need to, Always return the data from the current call including when More = false. Loop until NextStart = null, not just based on More = true.

Please refer below modified M code.

let
BaseUrl = "[API URL]",
ApiToken = "[API Token]",
Limit = 50,
InitialStart = 0,

GetPage = (Start as number) =>
let
Url = BaseUrl & "start=" & Text.From(Start) & "&limit=" & Text.From(Limit) & "&api_token=" & ApiToken,
Response = Json.Document(Web.Contents(Url)),
Data = Response[data],
NextStart = try Response[additional_data][pagination][next_start] otherwise null
in
[Data = Data, NextStart = NextStart],

AllPages = List.Generate(
() => [Result = GetPage(InitialStart)],
each [Result] <> null,
each
if [Result][NextStart] <> null then
[Result = GetPage([Result][NextStart])]
else
[Result = null],
each [Result][Data]
),

Combined = List.Combine(AllPages),
RawTable = Table.FromRecords(Combined)

in
RawTable

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[DataNinja777 · Accepted solution]

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,

[wardy912]

Hi @jimbob2285 

 You're correct, the issue is with the limit, the List.Generate loop stops prematurely because of how the state is managed. Specifically, you're not correctly carrying forward the NextStart value between iterations. As a result, it only fetches two pages (100 items if limit = 50), even if more are available.

here's the revised code

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

    // Function to fetch one page of results
    GetPage = (Start as number) =>
        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, Start = Start],

    // Generate all pages
    AllPages = List.Generate(
        () => GetPage(0),
        each [More] = true or [Start] = 0,
        each GetPage([NextStart]),
        each [Data]
    ),

    // Flatten all results into one list
    Combined = List.Combine(AllPages),

    // Convert to table
    RawTable = Table.FromRecords(Combined)
in
    RawTable

Fixes:

The state now includes Start, More, NextStart, and Data.
The loop condition each [More] = true or [Start] = 0 ensures the first page is always fetched.
each GetPage([NextStart]) correctly passes the updated Start value for the next iteration

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!