How to Handle API Pagination in Power BI

Getting data from web sources in Power BI is often straightforward with an HTTP request to a web service. The Power Query M formula language provides the Web.Contents function, which can retrieve results from APIs. However, most APIs impose limits on the number of results returned per request – a mechanism known as pagination. To retrieve all available data, the client must send multiple requests to fetch additional pages.

Handling pagination in Power Query for Power BI is an advanced technique that requires some experience. This blog post presents a solution using recursive function calls, which is also supported in the Power BI Service without the dreaded “Can’t Refresh Dynamic Data Source” error.

All steps are using the open Star Wars API at https://swapi.dev so anyone can reproduce the steps on their own.

The Problem

There are two main problems we need to solve to get all the data:

1. Handle the pagination
2. Ensure the solution works in Power BI Service

Lets have a look at the pagination. Via Web.Contents we can get data from an API. Here is an example written in a new query:

From this API Call, we get the first 10 of 60 results and a next link:

Now we need to build a function which starts from our first API call and iteratively calls the next link if it is existing while concatenating the results. This is called a recursive function: an algorithm that calls itself iteratively with modified parameters. If we implement this in Power Query, we have solved problem 1 and it would work fine in Power BI Desktop.

However, in Power BI Service the implementation with a complete next link will raise problem 2: the query can’t be refreshed within the Power BI Service. This is because it would result in a so called “Dynamic Data Source”, a data source in which some or all of the information required to connect can’t be determined until Power Query runs its query.

To solve the second problem, we can use the query parameters of the Web.Contents function. As long as the base URL for the data source is not completely altered, we are allowed to change the relative path and query parameters freely. Power BI Service will identify the static web URL as the source and therefore won’t treat it as a dynamic data source:

So to implement it correctly, we need to extract the next page number from the next link and add it to the query parameters.

The Solution

First we need to build our function which can call itself with changed query parameters (full code to copy paste at the end of the post). The picture below shows a possible implementation of such a function:

Now we just need to call our function with empty query parameters and Power Query will iterate as long as there is a next link provided. Afterwards, it’s just converting the results list into a table and expanding the records:

To validate the query and its data inside, we pull together some rudimentary visuals in a report. The picture below shows that 60 planets have indeed been retrieved:

The Conclusion

There are multiple ways in which pagination is implemented but all variants should be solvable with the general approach presented here. Note that this solution would terminate early if an API call results in an error because no next link would be retrieved in this case and the function would not retry the current call.

Let me know if you want the full Power BI file.
Cheers.

---

Code

let
    // Recursive function to call a api based on the initial parameters
    FetchData = (query as nullable record) as list =>
        let
            // Calling the web service with the fixed parameters for headers and relative path while adding the variable query data
            Source = Json.Document(Web.Contents(url, [Headers = headers, RelativePath = relative_path, Query = query])),
            // Store the retrieved results
            data = try Source[results] otherwise {}, 
            // Try to get the next link if it is present and extract the page number
            next_page = try Text.Split(Text.Split(Source[next], "?page="){1}, "&"){0} otherwise null,
            // if there is a next link, the function calls itself again with the new query parameters to get the next page
            next_result = if next_page <> null then @FetchData([page = next_page]) else {}
        in
            // Combine current page data with next page data to a list
            List.Combine({data, next_result}),

    url = "https://swapi.dev/api", // the base url of our api
    // using relative paths and query settings we can ensure the function call 
    // does not result in a dynamic data source and will work in the power bi service
    relative_path = "planets", // This can also just be in the base url but its a more flexible approach if other endpoints should also be queried
    query = [], // This holds query parameters and will be useful once we start iterating. For the moment, just keep an empty record.
    headers = [#"Content-Type" = "application/json"], // Here you could add authorization parameters if necessary #"authorization" = "Bearer " & Token
    
    // Call the recursive function starting from the base url
    results = FetchData(query),

    // Converting the results into a usable table
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "rotation_period", "orbital_period", "diameter", "climate", "gravity", "terrain", "population"}, {"name", "rotation_period", "orbital_period", "diameter", "climate", "gravity", "terrain", "population"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Column1","unknown",null,Replacer.ReplaceValue,{"name", "rotation_period", "orbital_period", "diameter", "climate", "gravity", "terrain", "population"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"population", Int64.Type}, {"rotation_period", type number}, {"orbital_period", type number}, {"diameter", type number}})
in
    #"Changed Type"

---