Looping through paginated API query without known total - help pulling pages 2+

[Anonymous]

Hi All,

I'm fairly new to Power BI and Power Query, though I've been trying to reach myself how to use it in my free time. I've spent a considerable amount of time reading similar threads to this one and trying to apply the suggestions to my own project, but I've struggled to get my query fully working. I feel like this should be fairly simple based on other similar discussions, but was hoping for some assistance.

Background:

• I'm trying to query this API endpoint (GET users) to produce a simple list of all users in our community platform.
• By default, the query returns 30 records, though I believe that can be increased to 100 per page.
• Results are paginated, and from what I can tell there is not an accessible variable to indicate the total number of records. (There does not appear to be a way to access this via a different API call either.)

Current Query:

• I'm currently using the below query, which successfully authenticates and returns the first page of results.
• We currently have 248 users in our staging environment, but I haven't been able to trigger the successful looping/return of the full list of user records.

This is the current code:

let
    Pagination = List.Skip(List.Generate(
        () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][page] otherwise null,// determine the LastKey for the next execution
            WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://community.website.com/api/v2/users", [Headers=[Authorization="Bearer TOKEN"]])) else Json.Document(Web.Contents("https://community.website.com/api/v2/users/?page="&Last_Key&"", [Headers=[Authorization="Bearer TOKEN"]])), // retrieve results per call
    		Counter = [Counter]+1// internal counter
        ],
   		each [WebCall]
    ),1)
in
    Pagination

Any help identifying the issue or suggesting an alternate approach would be appreciated. I've tried multiple forms of List.Generate and this was one of the few adaptations where I was able to return results without producing errors related to accessing list items, which I wasn't able to troubleshoot in earlier tries.

Thanks so much for any help in advance.

[ImkeF]

So your current query returns a list with just one row?

Then adjust the query like so and check the error-message that will be generated (then) for the 2nd item in the list:

let
Pagination = List.Skip(List.Generate(
() => [Last_Key = "init", Counter=0], // Start Value
each [Last_Key] <> null or [Counter] < 4, // Condition under which the next execution will happen
each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][page] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://community.website.com/api/v2/users", [Headers=[Authorization="Bearer TOKEN"]])) else Json.Document(Web.Contents("https://community.website.com/api/v2/users/?page="&Last_Key&"", [Headers=[Authorization="Bearer TOKEN"]])), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1)
in
Pagination