Connecting to and Extracting Data from REST APIs with Pagination in Power Query: Handling Next-Page Tokens, Offset Parameters, and Rate Limits

Introduction

Imagine your company's sales team uses a CRM platform — let's say HubSpot or Salesforce — and you need to pull every customer contact into a Power BI report. You connect to the API, run your first query, and get back... 100 records. But you know there are 47,000 contacts in the system. What happened? The API gave you the first page of results and quietly stopped, expecting you to ask for the rest.

This is the reality of working with REST APIs in the real world. APIs don't hand you all their data in one shot. They paginate — breaking large datasets into manageable chunks — for performance, server protection, and bandwidth reasons. As a Power Query developer, if you don't know how to navigate that pagination, you're making business decisions on a fraction of your actual data.

By the end of this lesson, you'll know how to write M code that follows an API through its pages from start to finish, handles the most common pagination styles you'll encounter in the wild, and plays nice with rate limits so your queries don't get blocked. This is one of the most practically valuable skills in the Power Query toolkit.

What you'll learn:

• How REST APIs return data and why pagination exists
• How to make authenticated web requests in Power Query using Web.Contents
• How to handle cursor/next-page token pagination (the most common modern style)
• How to handle offset/limit pagination (common in older and simpler APIs)
• How to respect rate limits and avoid getting your IP or API key blocked
• How to structure your M code for readability and maintainability

---

Prerequisites

You should be comfortable with the basics of Power Query before diving in here. Specifically, you'll benefit from knowing how queries are structured and how steps connect. If you're brand new, start with Power Query 101: Connect, Transform, Load first.

You should also have a working understanding of the M formula language. We'll be writing M directly — not just clicking buttons in the UI. If M feels foreign to you, take a detour through Understanding the M Formula Language: Syntax, Data Types, and Expression Basics before continuing.

Finally, you'll need an API to practice with. We'll use the free JSONPlaceholder API for basic examples (no key required), and reference HubSpot's Contacts API for the pagination token examples.

---

How REST APIs Return Data

A REST API (Representational State Transfer Application Programming Interface) is a web service that responds to HTTP requests with structured data, almost always in JSON format. You send a request to a URL (called an endpoint), and the server sends back a response.

Think of it like ordering from a restaurant. You send your order (the request), and the kitchen sends back your food (the response). If you're feeding a crowd, the kitchen might bring out your food in batches rather than all at once — that's pagination.

When you call https://api.example.com/contacts, you might get back something like this:

{
  "results": [
    { "id": 1, "name": "Alice Chen", "email": "alice@example.com" },
    { "id": 2, "name": "Ben Okafor", "email": "ben@example.com" }
  ],
  "paging": {
    "next": {
      "after": "NTI1Cg%3D%3D",
      "link": "https://api.example.com/contacts?after=NTI1Cg%3D%3D"
    }
  }
}

The results array holds the actual records. The paging.next section tells you there's another page, and gives you a token (after) to include in your next request to get it. This is the cursor-based pagination pattern, and it's what modern APIs like HubSpot, Notion, and Twitter use.

Note: JSON responses in Power Query come back as records and lists — not flat tables. Before you can work with the data, you'll need to expand nested structures. If this is new to you, Unpacking Nested JSON and XML Structures in Power Query: Expanding Lists, Records, and Tables covers this in depth.

---

Making Your First API Call with Web.Contents

In Power Query, Web.Contents is the function you use to fetch data from a URL. Open Power Query Editor, create a new blank query (Home → New Source → Blank Query), then open the Advanced Editor (Home → Advanced Editor) and paste in:

let
    url = "https://jsonplaceholder.typicode.com/posts",
    response = Web.Contents(url),
    parsed = Json.Document(response)
in
    parsed

This fetches all posts from JSONPlaceholder and parses the JSON. You'll see a list of records appear. That's your data.

Now here's the important part: Web.Contents accepts an optional second argument — a record of options — where you can specify headers, query parameters, and other settings:

let
    url = "https://jsonplaceholder.typicode.com/posts",
    options = [
        Headers = [
            Authorization = "Bearer YOUR_API_KEY",
            #"Content-Type" = "application/json"
        ],
        Query = [
            _limit = "10",
            _page = "1"
        ]
    ],
    response = Web.Contents(url, options),
    parsed = Json.Document(response)
in
    parsed

The Query record appends parameters to your URL as a query string (so you don't have to manually concatenate ?_limit=10&_page=1). The Headers record lets you pass authentication tokens and content types.

Warning: Never hardcode API keys directly into your M code if the query will be shared or stored in source control. Use Power Query parameters instead. See Parameterized Queries and Dynamic Data Sources in Power Query to learn how to externalize credentials safely.

---

Handling Offset/Limit Pagination

Offset pagination is the simpler of the two main styles. The API accepts two parameters: limit (how many records per page) and offset (how many records to skip). To get page 3 of 100 records per page, you'd request offset=200&limit=100. You keep incrementing the offset until you get back fewer records than your limit — that's the signal that you've hit the last page.

Here's how to do this in M using List.Generate, which is Power Query's way of building a list by repeating a process until a condition is false. Think of it as a while loop:

let
    BaseUrl = "https://api.example.com/contacts",
    ApiKey  = "YOUR_API_KEY",
    PageSize = 100,

    // List.Generate(initial, condition, next, transform)
    AllPages = List.Generate(
        // Start: fetch page at offset 0
        () => [
            Offset = 0,
            Data   = Json.Document(
                Web.Contents(BaseUrl, [
                    Headers = [Authorization = "Bearer " & ApiKey],
                    Query   = [limit = Text.From(PageSize), offset = "0"]
                ])
            )
        ],
        // Continue while the current page returned a full page of results
        (state) => List.Count(state[Data][results]) = PageSize,
        // Next: bump the offset by one page
        (state) => [
            Offset = state[Offset] + PageSize,
            Data   = Json.Document(
                Web.Contents(BaseUrl, [
                    Headers = [Authorization = "Bearer " & ApiKey],
                    Query   = [
                        limit  = Text.From(PageSize),
                        offset = Text.From(state[Offset] + PageSize)
                    ]
                ])
            )
        ],
        // Extract just the results list from each page
        (state) => state[Data][results]
    ),

    // Combine all page lists into one flat list, then convert to table
    Combined  = List.Combine(AllPages),
    AsTable   = Table.FromList(Combined, Splitter.SplitByNothing()),
    Expanded  = Table.ExpandRecordColumn(AsTable, "Column1",
                    {"id", "name", "email"})
in
    Expanded

Let's unpack List.Generate. It takes four arguments:

1. Initial state — a record describing where to start (offset = 0, plus the first API response)
2. Condition — keep going as long as this is true (we got a full page of results)
3. Next state — how to compute the next iteration (bump offset, fetch next page)
4. Transform — what to extract from each state to include in the output list (just the results array)

Key insight: The condition List.Count(state[Data][results]) = PageSize is the idiomatic way to detect the last page with offset pagination. If the API returned fewer records than you requested, you've exhausted the dataset. Some APIs also include a total count in the response — you can use that for an even cleaner stopping condition.

---

Handling Cursor/Next-Page Token Pagination

Modern APIs use cursor-based pagination because it's safer and more consistent than offsets, especially when data is being written while you're reading. Instead of "skip 200 records," the server gives you an opaque token that points to your exact position in the dataset.

The pattern looks like this:

1. Fetch page 1 (no token needed)
2. Extract the nextPageToken from the response
3. Fetch page 2 using that token
4. Repeat until the response has no nextPageToken

Here's a working M implementation using HubSpot's Contacts API structure (adapt field names to your specific API):

let
    BaseUrl = "https://api.hubapi.com/crm/v3/objects/contacts",
    ApiKey  = "YOUR_PRIVATE_APP_TOKEN",
    PageSize = 100,

    AllPages = List.Generate(
        // Start with null token — first page has no "after" cursor
        () => [
            Token = null,
            Data  = Json.Document(
                Web.Contents(BaseUrl, [
                    Headers = [Authorization = "Bearer " & ApiKey],
                    Query   = [limit = Text.From(PageSize)]
                ])
            )
        ],
        // Continue while the response includes a next-page token
        (state) => Record.HasFields(state[Data], "paging")
                   and Record.HasFields(state[Data][paging], "next"),
        // Next: use the token from the previous response
        (state) =>
            let
                nextToken = state[Data][paging][next][after]
            in [
                Token = nextToken,
                Data  = Json.Document(
                    Web.Contents(BaseUrl, [
                        Headers = [Authorization = "Bearer " & ApiKey],
                        Query   = [
                            limit = Text.From(PageSize),
                            after = nextToken
                        ]
                    ])
                )
            ],
        // Extract results from each page
        (state) => state[Data][results]
    ),

    Combined = List.Combine(AllPages),
    AsTable  = Table.FromList(Combined, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1",
                   {"id", "properties"})
in
    Expanded

Notice the condition step uses Record.HasFields to safely check whether the paging.next key exists before trying to read from it. This is important — if you try to access a field that doesn't exist on the last page, M will throw an error. Defensive checks like this are the difference between a query that works reliably and one that breaks unpredictably.

Tip: Some APIs use different field names for their next-page cursor: nextCursor, next_page_token, continuationToken, links.next, or @odata.nextLink (that last one is Microsoft's OData convention). Always check the API documentation for your specific source. The M logic is the same — only the field names change.

---

Handling Rate Limits

Rate limits are the API provider's way of preventing any single client from overwhelming their servers. Most APIs enforce something like "100 requests per minute" or "10 requests per second." If you exceed this, the API returns an HTTP 429 (Too Many Requests) error and may temporarily block your API key.

Power Query doesn't have a built-in sleep or delay function, which makes traditional rate limit handling tricky. However, there are a few practical approaches.

Strategy 1: Reduce parallelism with buffering. By default, Power Query may evaluate multiple steps in parallel. Wrapping your page-fetch logic in List.Buffer forces sequential evaluation:

AllPages = List.Buffer(
    List.Generate(
        ...your existing logic...
    )
)

List.Buffer forces the list to be fully evaluated and cached in memory before proceeding. This prevents Power Query from firing multiple simultaneous requests and overwhelming a rate-limited API.

Strategy 2: Add a computational pause. There's no Sleep() in M, but you can manufacture a small delay by doing a trivial calculation in a let expression before each request:

// Next state with a deliberate pause
(state) =>
    let
        // This list generation creates a small computational overhead
        // acting as a rough rate-limit buffer. Adjust size as needed.
        _pause = List.Count(List.Repeat({1}, 500000)),
        nextToken = state[Data][paging][next][after],
        nextData = Json.Document(
            Web.Contents(BaseUrl, [
                Headers = [Authorization = "Bearer " & ApiKey],
                Query   = [limit = Text.From(PageSize), after = nextToken]
            ])
        )
    in [
        Token = nextToken,
        Data  = nextData
    ]

Warning: The List.Repeat pause trick is a rough approximation and not a guaranteed delay — execution time varies by machine speed and Power Query engine load. For APIs with strict rate limits, you'll get more reliable results building your ingestion layer outside Power Query using Azure Data Factory, Python, or Power Automate, and querying the already-extracted data from Power Query. Power Query is an excellent transformation tool but a blunt instrument for rate-limited orchestration.

Strategy 3: Reduce page count by increasing page size. Fewer total requests means less chance of hitting rate limits. If your API supports page sizes up to 200, use 200. Simple, but often overlooked.

---

Structuring Your Query for Maintainability

Once you have a working pagination loop, it's worth taking a moment to organize your M code so future-you (and your colleagues) can understand and modify it. A common pattern is to split your logic into multiple named let-bindings inside a single query:

let
    // ── Configuration ─────────────────────────────────────────
    BaseUrl  = "https://api.example.com/contacts",
    ApiKey   = "YOUR_KEY",
    PageSize = 100,

    // ── Helper: build request options ─────────────────────────
    BuildOptions = (cursor as nullable text) =>
        if cursor = null
        then [
            Headers = [Authorization = "Bearer " & ApiKey],
            Query   = [limit = Text.From(PageSize)]
        ]
        else [
            Headers = [Authorization = "Bearer " & ApiKey],
            Query   = [limit = Text.From(PageSize), after = cursor]
        ],

    // ── Helper: extract next cursor from response ──────────────
    GetNextCursor = (data as record) =>
        if Record.HasFields(data, "paging")
           and Record.HasFields(data[paging], "next")
        then data[paging][next][after]
        else null,

    // ── Pagination loop ────────────────────────────────────────
    AllPages = List.Generate(
        () => [Cursor = null, Data = Json.Document(Web.Contents(BaseUrl, BuildOptions(null)))],
        (s) => GetNextCursor(s[Data]) <> null or s[Cursor] = null,
        (s) =>
            let nextCursor = GetNextCursor(s[Data])
            in  [Cursor = nextCursor, Data = Json.Document(Web.Contents(BaseUrl, BuildOptions(nextCursor)))],
        (s) => s[Data][results]
    ),

    // ── Combine and shape ──────────────────────────────────────
    Combined = List.Buffer(List.Combine(AllPages)),
    AsTable  = Table.FromList(Combined, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1", {"id", "name", "email"})

in
    Expanded

Breaking the helper functions out as named lambdas (BuildOptions, GetNextCursor) dramatically improves readability. This approach is explored in more depth in Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns.

---

Hands-On Exercise

Let's put it all together with a real, free API. JSONPlaceholder doesn't natively paginate, but we can simulate offset pagination using their _start and _limit parameters.

1. Open Power Query Editor (in Excel: Data → Get Data → Launch Power Query Editor; in Power BI Desktop: Home → Transform Data)
2. Create a new blank query: Home → New Source → Blank Query
3. Open Advanced Editor and paste this code:

let
    BaseUrl  = "https://jsonplaceholder.typicode.com/posts",
    PageSize = 10,

    AllPages = List.Generate(
        () => [
            Offset = 0,
            Data   = Json.Document(
                Web.Contents(BaseUrl, [Query = [_start = "0", _limit = Text.From(PageSize)]])
            )
        ],
        (state) => List.Count(state[Data]) = PageSize,
        (state) => [
            Offset = state[Offset] + PageSize,
            Data   = Json.Document(
                Web.Contents(BaseUrl, [
                    Query = [
                        _start = Text.From(state[Offset] + PageSize),
                        _limit = Text.From(PageSize)
                    ]
                ])
            )
        ],
        (state) => state[Data]
    ),

    Buffered = List.Buffer(List.Combine(AllPages)),
    AsTable  = Table.FromList(Buffered, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(
                   AsTable, "Column1",
                   {"userId", "id", "title", "body"})
in
    Expanded

4. Click Done. Power Query will fetch all 100 posts across 10 pages of 10 records each. You should end up with a table of 100 rows.

5. Now modify the PageSize variable to 25 and observe that Power Query fetches 4 pages instead of 10. This reinforces that your pagination logic is actually working — changing one number adjusts the entire loop.

Tip: If you get a "Formula.Firewall" error, it's because Power Query is protecting you from combining data sources with different privacy levels. Go to File → Options and Settings → Query Options → Privacy and set the privacy level to "Ignore the Privacy Levels" for development purposes. For production, read about proper privacy configuration in Implementing Custom Privacy Levels and Data Source Credentials Management in Power Query for Secure Enterprise ETL Pipelines.

---

Common Mistakes & Troubleshooting

"Expression.Error: We couldn't convert the value null to type Text" This usually means you're trying to use a token or field value that doesn't exist on the last page. Add Record.HasFields checks before accessing nested fields. This is the most common error in pagination code.

Your query returns only one page of data Check your stopping condition in List.Generate. If it evaluates to false immediately, the loop runs exactly once. Print the List.Count of your first response and make sure it actually equals PageSize. Also verify the API really is paginating — some APIs return all records for small datasets.

Your query never stops / runs infinitely The opposite problem: your stopping condition never becomes false. This can happen if you check for a next-page token but that token is always present (even when there are no more records). Read the API docs carefully — some APIs return an empty results array alongside a next-page token on the last request.

HTTP 401 Unauthorized Your API key is wrong, expired, or not being passed correctly. Double-check that your Authorization header value matches the format the API expects (some use Bearer TOKEN, others use Token TOKEN, others use a custom header name like X-Api-Key).

HTTP 429 Too Many Requests You've hit the rate limit. Implement the List.Buffer strategy described above, increase your page size to reduce request count, or consider extracting data outside of Power Query and reading from a cached intermediate source. For strategies on managing refresh scheduling, see Scheduling and Managing Power Query Refresh Failures in Power BI Service: Alerts, Diagnostics, and Recovery Workflows.

Query is very slow Pagination loops with many API calls are inherently slow because each iteration is a network round-trip. Consider Power Query Performance: Master Folding, Buffering & Optimization Techniques for strategies to optimize, but be aware that some slowness is just the cost of making 50+ sequential HTTP requests.

Key insight: Always test your pagination logic against a small dataset first. Set PageSize = 2 and add a hard stop after 3 pages by adding a counter to your state record. Once you're confident the loop works correctly, remove the hard stop and set the production page size.

---

Summary & Next Steps

You've now learned the full anatomy of API pagination in Power Query. You understand why pagination exists, how to make authenticated Web.Contents calls, and how to implement both offset-based and cursor/token-based pagination using List.Generate. You also know the practical strategies for respecting rate limits and how to structure your M code for long-term maintainability.

The key concepts to hold onto:

• List.Generate is your pagination engine — it's a while loop that builds a list of pages
• Offset pagination increments a skip counter; stop when the page is smaller than your limit
• Cursor pagination follows a token from each response; stop when the token field is absent
• List.Buffer forces sequential evaluation and is your first defense against rate limits
• Defensive field checks with Record.HasFields prevent null-access errors on the last page

From here, you have several natural directions to explore. If the data coming back from your API is deeply nested with lists inside records inside lists, Unpacking Nested JSON and XML Structures in Power Query: Expanding Lists, Records, and Tables will be essential. If you want to combine your API results with data from other sources — like a SQL database or SharePoint list — Combining Data from Multiple Sources with Append and Merge Queries in Power Query shows you how to join them cleanly. And if you're thinking about only fetching new records since your last refresh rather than re-pulling everything every time, that's the domain of Automating Incremental Data Refreshes in Power Query with Persistent State and Change Tracking.

REST APIs are one of the richest and most widely available data sources in the modern data ecosystem. Now that you can paginate through them reliably, a huge range of platforms — CRMs, project management tools, marketing platforms, financial data providers — are genuinely accessible from your Power Query environment.