Token-based pagination In Power Query

[vojtechsima]

What is Token-based pagination?

When you request data for the first page, the response includes both the data and a token that points to the next page. You then use this token to continue fetching subsequent pages, looping over each token until you reach the last page (often marked by an isLast field, set to true or false). This method ensures that no data is skipped, and you won’t need to build a custom loop to retrieve all pages.

Example:

Request

GET /api/items?limit=5

Response

{"data": ["item1", "item2", "item3", "item4", "item5"], "next_token": "abc123"}

Requesting next page

GET /api/items?limit=5&token=abc123

Response

{"data": ["item6", "item7", "item8", "item9", "item10"], "next_token": "def456"}

How to make it work in Power Query on Real-life example?

Example is Power BI REST API; Get Activity Events; follow up from Time-based pagination

As the name implies, each response includes a token that we’ll use to fetch the next page. In our example, we can either use the continuationToken directly or go with the continuationUri. The latter requires modifying the address to work correctly in Power Query, however, it's recommended to use the provided Uri by the API due to possible DNS issues.

"DNS issues" meas that the host in the suggested next page's URL might differ from the host used in your original request. This discrepancy requires you to translate the address to the preferred host designated by the API developers. Historically, this translation could be faulty in our example endpoint.

 In this example, our initial response without any pagination, just calling our endpoint one time, we get something like this:

Initial call code:

request = (start as text, end as text) => 
    Json.Document(
        Web.Contents(
            "https://api.powerbi.com", 
            [
                RelativePath = "v1.0/myorg/admin/activityevents", 
                Query = [
                    startDateTime = "'" & start & "'", 
                    endDateTime = "'" & end & "'"
                ], 
                Headers = [
                    Authorization = "Bearer " & temp_token
                ]
            ]
        )
    )

Response

To access the next page, we'll use the provided URI. We'll split it into two parts: the new host (which we must unfortunately write statically due to concerns with dynamic sources) and the relative path (this part can and should remain dynamic, as it inherently needs to be). Additionally, we'll remove the timestamp parameters since they are no longer required.

We can construct a new template request using this approach for cleaner and more maintainable code.

nextPageRequest = (uri as text) => 
    Json.Document(
        Web.Contents(
            "https://wabi-west-europe-e-primary-redirect.analysis.windows.net", 
            [
                RelativePath = Text.AfterDelimiter(uri, "/", 2), 
                Headers = [
                    Authorization = "Bearer " & temp_token
                ]
            ]
        )
    )

Text.AfterDelimeter() will gives the second part of the uri, text part after third "/" symbol (the index starts from 0, therefore 2 means third appearance).

removed part: https://wabi-west-europe-e-primar-redirect.analysis.windows.net/

remaining part: v1.0/myorg/admin/activityevents?continuationToken='eyJTdGFydER...'

Now we have all the essentials, and we're ready to start making our calls.

token_based_call = 
    List.Generate(
        () => request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999"), 
        each not _[lastResultSet], 
        each nextPageRequest(_[continuationToken]), 
        each _[activityEventEntities]
    )

Disclaimer for "not reading, fast copying" visitors, this is not yet final solution

As you can see, we're once again using our reliable List.Generate() function. Let’s break down what’s happening within it.

token_based_call = 
    List.Generate(
        () => initial call using our "request" function with timestamps,

        loop over until a value that indicates last page is TRUE, 

        using continuation token, call function "nextPageRequest",

        for each iteration save data from a field "activityEventEntities"
    )

This works perfectly—until it doesn’t. We expected 3 pages for this particular request, but we’re only getting 2.

The issue is that we’re not retrieving the data from the last page. Since lastResultSet is set to TRUE, the function stops prematurely.

There are several ways to fix this. You could use functions like List.Accumulate() or try writing a recursive function. To keep things straightforward, though, let me introduce a cheeky workaround.

token_based_call = 
List.Generate(
    () => 
        [
            call = request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999Z"), 
            nextUri = call[continuationUri]?, 
            isLastPage = call[lastResultSet]?
        ], 
    each not [isLastPage], 
    each 
        [
            call = nextPageRequest(nextUri), 
            nextUri = _[call][continuationUri]?, 
            isLastPage = _[call][lastResultSet]?
        ], 
    each [call][activityEventEntities]?
)

Now, this you can copy ("not reading, fast copying" visitors)

In this workaround, there aren't as many changes as it might initially seem. Essentially, for each iteration, we create our own record where we control both the data and the iteration condition. This approach allows us to "lag" one iteration behind, allowing us to load and save data for the last page.

Here's a little breakdown of the code:

token_based_call_commented = 
    List.Generate(
        // The initial value: constructing a record
        () => 
            [
                // Initial API call using a timestamp parameters.
                call = request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999Z"), 
                
                // Extract the "continuationUri" from the call.
                nextUri = call[continuationUri]?, 
                
                // Extract the "lastResultSet" from the call.
                isLastPage = call[lastResultSet]?
            ], 
        
        // Condition: Keep iterating as long as "isLastPage" is not true.
        // The value comes from estabilished record.
        each not [isLastPage], 
        
        // Transform: Define how the state evolves for the next iteration.
        // We use same field names and update them
        each 
            [
                // Call next page request using uri from previous step.
                call = nextPageRequest(nextUri), 
                
                // Update the "continuationUri" from the previous response.
                nextUri = _[call][continuationUri]?, 
                
                // Update the "lastResultSet" from the previous response.

                isLastPage = _[call][lastResultSet]?
            ], 
        
        // Selector: Extract the required data ("activityEventEntities") from each response.
        each [call][activityEventEntities]?
    )

If we would display this approach in a table, it could look like this: