Hi @J94 ,
It appears you've encountered a very common issue when paginating through an API in Power Query. The problem stems from a feature called "lazy evaluation," where the engine tries to optimize performance by caching the result of functions like Web.Contents. When used inside a loop or a function like List.Transform, it can mistakenly reuse the result from the first API call for all subsequent calls, which is why you see the same page of data repeated. The solution is to force Power Query to recognize each call as a unique request.
The most direct way to fix your query is to add a Query option to your Web.Contents function. This adds a unique query string (like ?page=1, ?page=2) to the end of the base URL for each iteration. Even though your API reads the page number from the request body, this change signals to the Power Query engine that each call is distinct and should be executed fresh instead of using a cached result. The rest of your logic for determining the page count and combining the results is perfectly fine and can remain as is.
Here is your original code with the small but critical modification applied.
let
token = "my_token",
baseUrl = "https://apiv2.my_site.com/v2/users/search",
perPage = 100,
GetPage = (page as number) =>
let
body = Text.ToBinary("{""page"":" & Number.ToText(page) & ",""perPage"":" & Number.ToText(perPage) & "}"),
response = Json.Document(
Web.Contents(baseUrl,
[
Headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer " & token
],
Content = body,
// This Query option is the key change that forces each call to be unique
Query = [page = Number.ToText(page)]
]
)
)
in
response,
FirstPage = GetPage(1),
TotalPages = FirstPage[totalPages],
PageNumbers = List.Numbers(1, TotalPages),
AllPagesRaw = List.Transform(PageNumbers, each GetPage(_)),
AllContent = List.Transform(AllPagesRaw, each _[content]),
CombinedContent = List.Combine(AllContent),
ResultTable = Table.FromRecords(CombinedContent)
in
ResultTable
As an alternative, many developers consider the List.Generate function to be a more robust and memory-efficient pattern for pagination. It constructs the list of results iteratively, fetching one page at a time based on the result of the previous step. This approach is especially powerful when an API doesn't tell you the total number of pages upfront. While your API does provide totalPages, List.Generate is still an excellent technique to be aware of for future projects.
The following query accomplishes the same goal using the List.Generate pattern.
let
token = "my_token",
baseUrl = "https://apiv2.my_site.com/v2/users/search",
perPage = 100,
GetPage = (page as number) as record =>
let
body = Text.ToBinary("{""page"":" & Number.ToText(page) & ",""perPage"":" & Number.ToText(perPage) & "}"),
response = Json.Document(
Web.Contents(baseUrl,
[
Headers = [#"Content-Type"="application/json", Authorization="Bearer " & token],
Content = body,
Query = [page=Number.ToText(page)]
]
)
)
in
response,
FirstPageResponse = GetPage(1),
TotalPages = FirstPageResponse[totalPages],
AllPagesContent = List.Generate(
() => [Page = 1, Content = FirstPageResponse[content]],
each [Page] <= TotalPages,
each [Page = [Page] + 1, Content = GetPage([Page] + 1)[content]],
each [Content]
),
CombinedContent = List.Combine(AllPagesContent),
ResultTable = Table.FromRecords(CombinedContent)
in
ResultTable
To apply these changes, you can navigate to the Power Query Editor, select your query, and open the Advanced Editor from the Home tab. There, you can replace your script with one of the versions provided above. After ensuring your real token is in place, your query should successfully retrieve and combine the data from all pages.
Best regards,
