Forum Discussion
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,
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!