Power Query: Handling Paginated REST API Data

Processing paginated API results where the page count is either known or unknown

Published

By John Dalesandro

Photo of Weird Wizarding Dilemmas and their Solutions at Harry Potter Shop, New York.

Power Query makes it possible to query REST APIs, process JSON responses, and transform the resulting data without requiring additional tools. When used with Power BI or Excel, Power Query can often eliminate the need for additional tools or scripts when processing JSON responses.

However, working with REST APIs can be challenging, especially when working with large result sets. To manage processing and bandwidth, APIs often paginate responses, splitting them into multiple pages. For example, if a query is expected to return a million entries, the API might divide the result into pages of 100 entries each. Retrieving the full set would then require ten thousand API requests (100 entries per page across 10,000 pages equals 1,000,000 entries).

This post demonstrates Power Query code to process paginated JSON results from a REST API. It provides three solutions for handling scenarios where the number of pages is known, calculated, or unknown.

REST API

The code in this solution uses a publicly available REST API from the U.S. Department of the Treasury called the Treasury Reporting Rates of Exchange. This API provides the U.S. government’s official quarterly exchange rates for converting foreign currencies to U.S. dollars. This API was chosen because exchange rates are a familiar concept, though not crucial for this exercise. More importantly, it provides enough data to demonstrate pagination. The full U.S. Treasury Fiscal Data website offers various well-documented, free-to-use data sources that do not require sign-ups or API tokens.

The API request retrieves three data fields from the Rates of Exchange API: Country - Currency Description, Exchange Rate, and Record Date. The JSON response includes metadata like count, total-count, and total-pages. These fields are key for verifying that the Power Query code works correctly. The count field shows the number of entries returned, the total-count field shows the total number of entries, and the total-pages field shows the number of pages of results, such as 100 results per page across 162 pages in total (with the 162nd page containing only 10 entries).

Custom Power Query Paging Function

According to the API documentation, there are two parameters for pagination. The page[size] parameter controls the number of entries per page, with the default set to 100 entries. This example uses the default setting. The more crucial parameter here is page[number], which specifies the page index to retrieve a specific page from the data set. For example, if there are 42 total pages and page[number] is set to 7, the response will return the 100 entries on page 7.

GetPage Function

Create a custom Power Query function to handle a dynamic number of pages returned by the API. This function has one parameter, page, which represents the page number to retrieve. This function is used in all three solutions.

  1. In Power BI, Excel, or any other application that supports Power Query, select Get Data and then Blank Query.
  2. The Power Query window opens with a new blank query.
  3. From the Power Query Home ribbon, select Advanced Editor.
  4. Paste the following code into the Advanced Editor, replacing any existing code.
  5. Click Done to close the Advanced Editor.
  6. In the Query Settings pane under Properties, rename the function to GetPage.
(page as text) =>
  let
    Source = try
      Json.Document(
        Web.Contents(
          "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?page[number]="
            & page
            & "&fields=country_currency_desc,exchange_rate,record_date"
        )
      )
    otherwise
      [data = null, meta = null, links = null],
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Removed Columns" = Table.RemoveColumns(#"Converted to Table", {"meta", "links"}),
    #"Expanded data List" = Table.ExpandListColumn(#"Removed Columns", "data"),
    #"Expanded data Records" = Table.ExpandRecordColumn(
      #"Expanded data List",
      "data",
      {"country_currency_desc", "exchange_rate", "record_date"},
      {"data.country_currency_desc", "data.exchange_rate", "data.record_date"}
    )
  in
    #"Expanded data Records"

Each invocation of GetPage results in a separate HTTP request. While this approach works well for many APIs, retrieving hundreds or thousands of pages can take time and may be subject to API rate limits. Always consult the API documentation for any request limits or throttling policies before retrieving large data sets.

Retrieving All Pages

This API provides the total number of pages in its JSON response, making it straightforward to retrieve every page. However, many other APIs do not offer this information. Some only return the total number of entries, while others do not provide any counts. Alternative methods are also included for calculating or retrieving pages dynamically without metadata counts.

To keep things simple, the same API is used for each scenario while ignoring the metadata it provides so each pagination technique can be demonstrated independently.

  1. From the Power Query Home ribbon, select New Source and then Blank Query.
  2. The Power Query window opens with a new blank query.
  3. From the Power Query Home ribbon, select Advanced Editor.
  4. Paste one of the following code sequences into the Advanced Editor (appropriate to the specific scenario), replacing any existing code.
  5. Click Done to close the Advanced Editor.
  6. In the Query Settings pane under Properties, rename the query to Treasury Reporting Rates of Exchange.

Method 1: Page Count Is Known

In the easiest scenario, the API response includes a total page count. No additional calculations are required.

let
  Source = try
    Json.Document(
      Web.Contents(
        "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date"
      )
    )
  otherwise
    [data = null, meta = null, links = null],
  #"Page List" =
    if Source = null or Source[meta] = null then
      {null}
    else
      {1 .. Source[meta][#"total-pages"]},
  #"Converted to Table" = Table.FromList(
    #"Page List",
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "PAGES"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"PAGES", type text}}),
  #"Invoked Custom Function" = Table.AddColumn(
    #"Changed Type",
    "GetPage",
    each if [PAGES] <> null then GetPage([PAGES]) else null
  ),
  #"Expanded GetPage" = Table.ExpandTableColumn(
    #"Invoked Custom Function",
    "GetPage",
    {"data.country_currency_desc", "data.exchange_rate", "data.record_date"},
    {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"}
  )
in
  #"Expanded GetPage"

Method 2: Page Count Is Unknown; Entry Count Is Known

If the API does not provide the total number of pages, it can be calculated using available data. If the API returns the total number of entries and the number of entries per page is known, calculate the number of pages by dividing the total number of entries by the number of entries per page and rounding up.

The main difference from method 1 is in the Page List step. This step generates a dynamic list using {1..Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])}. This calculates the number of pages by dividing the total-count (total entries) by count (entries per page) and rounding up to ensure all entries are included. If there is a remainder after dividing, they will be included in an additional partial page.

For example, with 205 total entries and 100 entries per page, there would be 3 pages to include the extra 5 entries beyond 200. Adjust the calculation if the API pagination starts at zero or a different number.

let
  Source = try
    Json.Document(
      Web.Contents(
        "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date"
      )
    )
  otherwise
    [data = null, meta = null, links = null],
  #"Page List" =
    if Source = null or Source[meta] = null then
      {null}
    else
      {1 .. Number.RoundUp(Source[meta][#"total-count"] / Source[meta][#"count"])},
  #"Converted to Table" = Table.FromList(
    #"Page List",
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "PAGES"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"PAGES", type text}}),
  #"Invoked Custom Function" = Table.AddColumn(
    #"Changed Type",
    "GetPage",
    each if [PAGES] <> null then GetPage([PAGES]) else null
  ),
  #"Expanded GetPage" = Table.ExpandTableColumn(
    #"Invoked Custom Function",
    "GetPage",
    {"data.country_currency_desc", "data.exchange_rate", "data.record_date"},
    {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"}
  )
in
  #"Expanded GetPage"

Method 3: Both Page Count and Entry Count Are Unknown

In this more complex scenario, neither the total number of pages nor the total number of entries is known because the API does not provide any metadata on result counts.

The List.Generate function is used to iterate through pages until a null page is returned, which indicates that the page contains no entries. This requires the custom GetPage function to return an empty or otherwise detectable result when no data remains. In this example, pages with no results expand to rows containing null values, allowing the List.Generate condition to terminate iteration. The condition for detecting a null page should be tailored to the specific API being used.

let
  Source = List.Generate(
    () => [PAGE = 1, RESULTS = GetPage("1")],
    each Table.RowCount(
      Table.SelectRows(_[RESULTS], each not List.Contains(Record.ToList(_), null))
    )
      > 0,
    each [PAGE = _[PAGE] + 1, RESULTS = GetPage(Number.ToText([PAGE] + 1))],
    each _[[PAGE], [RESULTS]]
  ),
  #"Converted to Table" = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Expanded Column" = Table.ExpandRecordColumn(
    #"Converted to Table",
    "Column1",
    {"PAGE", "RESULTS"},
    {"PAGES", "GetPage"}
  ),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column", {{"PAGES", type text}}),
  #"Expanded GetPage" = Table.ExpandTableColumn(
    #"Changed Type",
    "GetPage",
    {"data.country_currency_desc", "data.exchange_rate", "data.record_date"},
    {"GetPage.data.country_currency_desc", "GetPage.data.exchange_rate", "GetPage.data.record_date"}
  )
in
  #"Expanded GetPage"

Summary

This article demonstrates three approaches to retrieving paginated REST API data with Power Query: when the page count is known, when only the entry count is known, and when both counts are unknown. A custom function is created to retrieve individual pages for dynamic pagination and solutions include handling known page counts directly, calculating pages from entry counts, and iterating through pages until no additional data is returned.