Handling Pagination and Throttling When Querying Large Datasets in Power Automate

Introduction

You've built a flow that pulls records from SharePoint, Dataverse, or a REST API. It works great in testing with your 50-row sample dataset. You promote it to production, point it at the real data — 40,000 customer records, 18 months of sales history, a product catalog that grows by hundreds of items a week — and the flow either silently returns 256 rows or crashes with a 429 error you've never seen before. Sound familiar?

This is the wall that separates flows that work in demos from flows that work in production. Pagination and throttling aren't edge cases or advanced topics you'll deal with "someday." They're the normal operating conditions of any serious data integration. APIs deliberately limit how much data they'll return in one shot and how fast you can ask for it. If you don't account for that in your design, your flows will quietly lie to you — returning partial results with no error — or thrash against rate limits until they time out.

By the end of this lesson, you'll know exactly how pagination and throttling work at the protocol level, how Power Automate exposes (and sometimes hides) these behaviors, and how to build flows that reliably retrieve complete datasets from SharePoint, Dataverse, REST APIs, and SQL without blowing through rate limits. You'll also build a complete working solution that handles both problems together in a realistic scenario.

What you'll learn:

• How pagination works in SharePoint, Dataverse, and generic REST APIs — and why each one handles it differently
• How to use Power Automate's built-in pagination settings and when they're not enough
• How to build manual pagination loops that follow nextLink tokens and continuation headers
• What throttling (HTTP 429) means, why it happens, and how to implement exponential backoff retry logic
• How to structure large-dataset flows to stay within action limits, avoid timeouts, and process data efficiently

---

Prerequisites

This lesson assumes you're comfortable building multi-step flows in Power Automate, understand how to use variables and Apply to each loops, and have worked with at least one connector (SharePoint, HTTP, or Dataverse). You should know what an HTTP action is and understand basic JSON structure. You don't need to be a developer, but you should be past the beginner stage.

---

Why APIs Limit Data: The Real Reason for Pagination

Before we get into mechanics, it helps to understand why APIs paginate. When you query a database through an API, the server has to serialize every result into JSON, compress it, and transmit it over a network. For 50,000 rows, that might be 20MB of JSON. That payload consumes server memory, network bandwidth, and client processing time — multiplied by every concurrent user hitting the API simultaneously.

Pagination is the API's way of saying: "I'll give you a manageable chunk. Come back with a token to get the next chunk." This protects the server and makes responses faster and more predictable for everyone. The tradeoff is that you now have to write code to follow the chain of pages.

Different systems implement this differently:

• SharePoint uses a skiptoken embedded in a @odata.nextLink URL
• Dataverse uses @odata.nextLink similarly, but with different URL structure and different limits
• REST APIs vary wildly — some use next URLs in the response body, some use Link headers, some use offset/limit query parameters
• SQL Server (via the SQL connector) handles pagination internally, but has its own 2048-row default limit

Understanding which mechanism your data source uses determines which technique you need.

---

SharePoint: The Threshold Problem and Built-In Pagination

SharePoint is probably the most common data source in Power Automate flows, so let's start there. When you use the Get items action against a SharePoint list, you run into two separate limits that are easy to confuse:

1. The 5,000-item list view threshold — SharePoint itself won't return more than 5,000 items in a single query unless you filter by an indexed column. This is a SharePoint limitation, not a Power Automate one.
2. The connector's default 100-row cap — By default, Get items returns at most 100 rows per call, regardless of how many match your query.

Enabling Pagination in Get Items

Power Automate has a built-in setting for this. In the Get items action, click the three-dot menu (the ellipsis) in the top right corner of the action card and select Settings. You'll see a Pagination toggle. Turn it on and set a Threshold — this is the maximum total number of items you want the action to retrieve across all pages.

When you enable this setting, Power Automate handles the page-following loop for you internally. It will keep calling SharePoint with successive skiptoken values and concatenate the results until it either runs out of items or hits your threshold. The output of the action looks like a single array — you never see the individual pages.

Set the threshold to 100,000 if you're not sure of your upper bound. Power Automate will stop at the actual total if the list has fewer items.

Warning: The pagination threshold maxes out at 100,000 items. If your SharePoint list has more than 100,000 rows, built-in pagination won't cover you. You'll need a manual approach using indexed columns and $filter to query in batches, or consider whether SharePoint is the right storage layer for that volume.

When Built-In Pagination Isn't Enough

Built-in pagination works well for simple "get all the records" scenarios, but it has limitations. Because Power Automate fetches every page before handing you the array, your flow holds the entire dataset in memory before you can process anything. For large lists, this means:

• The action may take several minutes to complete while pages are fetched
• If you exceed the flow run duration limit (30 days for standard, but individual actions have practical limits), it will time out
• You can't process records incrementally — you get everything or nothing

For very large lists, a better pattern is to query in date-range or ID-range batches, process each batch, then move to the next. We'll build that pattern in the hands-on exercise.

---

Dataverse: OData Pagination with nextLink

Dataverse uses OData v4 conventions and exposes pagination through a @odata.nextLink property in its response. When you use the List rows action in the Dataverse connector, you have similar built-in pagination settings — same menu, same toggle, same threshold approach.

However, Dataverse has a hard server-side page size of 5,000 rows. You can request fewer with the Row count setting, but you can't get more than 5,000 in a single request. The built-in pagination will follow the chain of pages up to your threshold.

Manual OData Pagination for Dataverse

When you need more control, or when you're hitting Dataverse through the HTTP connector directly (a common pattern in more complex flows), you need to follow @odata.nextLink manually. Here's the structure of a manual Dataverse pagination loop:

Initialize variable: varNextLink (String) = ""
Initialize variable: varAllRecords (Array) = []

Do Until: varNextLink equals "DONE"

  Condition: Is varNextLink empty?
    Yes → HTTP GET to initial Dataverse URL
          e.g. https://yourorg.crm.dynamics.com/api/data/v9.2/accounts
               ?$select=accountid,name,revenue,createdon
               &$top=5000
               &$orderby=createdon asc
    No  → HTTP GET to varNextLink

  Set variable: varAllRecords = union(varAllRecords, body('HTTP')?['value'])

  Condition: Does body contain @odata.nextLink?
    Yes → Set varNextLink = body('HTTP')?['@odata.nextLink']
    No  → Set varNextLink = "DONE"

The key expression for extracting the next link is:

if(
  contains(body('HTTP_Get_Dataverse_Page'), '@odata.nextLink'),
  body('HTTP_Get_Dataverse_Page')['@odata.nextLink'],
  'DONE'
)

Tip: Always include $orderby in your Dataverse queries when paginating. Without a stable sort order, the server may return duplicate or missed records across page boundaries if data changes while you're iterating.

---

Generic REST APIs: Reading the Spec Before Writing the Flow

REST APIs don't have a standard pagination mechanism, which means you need to read the API documentation before you can build the loop. There are four common patterns:

Pattern 1: nextLink / next URL in Response Body

The most common pattern in modern APIs. The response body contains a URL pointing to the next page:

{
  "data": [...],
  "pagination": {
    "next": "https://api.example.com/v2/orders?cursor=eyJpZCI6MTAwfQ"
  },
  "total": 84293
}

Your loop checks body('HTTP_Call')?['pagination']?['next'] and continues until it's null or absent.

Pattern 2: Link Header

Some APIs (GitHub is a classic example) put pagination URLs in the HTTP response Link header:

Link: <https://api.github.com/repos/org/repo/issues?page=2>; rel="next"

Power Automate's HTTP action exposes response headers via outputs('HTTP_Call')['headers']. You'd extract the Link header and parse out the next URL with a string manipulation expression — messy, but doable.

Pattern 3: Offset / Limit

Older-style APIs use explicit page offsets:

GET /api/records?limit=500&offset=0
GET /api/records?limit=500&offset=500
GET /api/records?limit=500&offset=1000

Your loop increments an offset variable by the page size on each iteration, stopping when the returned array is empty or its length is less than the page size (indicating you've hit the last page).

Pattern 4: Page Number

Similar to offset/limit but using a page number parameter. Increment page by 1 each iteration:

GET /api/records?pageSize=100&page=1
GET /api/records?pageSize=100&page=2

Building a Generic Pagination Loop

Here's a complete loop structure for the offset/limit pattern that you can adapt for any REST API. In Power Automate, this translates to:

Initialize variable: varOffset (Integer) = 0
Initialize variable: varPageSize (Integer) = 500
Initialize variable: varHasMore (Boolean) = true
Initialize variable: varAllOrders (Array) = []

Do Until: varHasMore equals false

  HTTP GET: https://api.orders.internal/v2/orders
    Headers:
      Authorization: Bearer @{variables('varAPIToken')}
      Content-Type: application/json
    URI: concat(
      'https://api.orders.internal/v2/orders?limit=',
      variables('varPageSize'),
      '&offset=',
      variables('varOffset')
    )

  Append to array variable: varAllOrders
    Value: body('HTTP_Get_Orders')?['data']

  Condition: length(body('HTTP_Get_Orders')?['data']) is less than varPageSize
    Yes → Set varHasMore = false
    No  → Set varOffset = add(variables('varOffset'), variables('varPageSize'))

Warning: The union() function in Power Automate merges arrays but removes duplicates based on object equality. For appending pages of records, use Append to array variable inside the loop and add each page as a chunk, or use union() only if you're certain records have no duplicate detection issues. For most pagination scenarios, Append to array variable with the page's array value is the safer choice.

---

Throttling: Understanding HTTP 429 and Why It Happens

Throttling is a different problem from pagination. Where pagination is about how much data you can get per request, throttling is about how fast you can make requests.

When you exceed an API's rate limit, it returns an HTTP 429 response: Too Many Requests. Often (but not always) this response includes a Retry-After header telling you how many seconds to wait before retrying:

HTTP/1.1 429 Too Many Requests
Retry-After: 30
Content-Type: application/json

{
  "error": "rate_limit_exceeded",
  "message": "You have exceeded 100 requests per minute."
}

Power Automate connectors handle some throttling automatically. The SharePoint, Dataverse, and Office 365 connectors will retry on 429s with built-in backoff. But the generic HTTP connector does not — a 429 response is treated as a successful (non-error) HTTP call, and your flow will happily read the error body and continue, silently failing to get the data you needed.

Where Throttling Actually Hits You

In pagination flows, throttling typically becomes a problem when you're:

1. Running large pagination loops — hundreds of HTTP calls in rapid succession
2. Processing in parallel — Apply to each with concurrency turned up while each iteration makes API calls
3. Calling Microsoft's own services — SharePoint, Graph API, and Dataverse all have rate limits, even though they're "internal" to the Microsoft 365 ecosystem
4. Running multiple flows concurrently — if three flows all paginate the same API simultaneously, you'll hit limits three times faster

Microsoft Graph API Rate Limits

If you're calling Graph API (for Teams, Exchange, OneDrive data), the limits are per-tenant and per-application. As of current documentation, Graph enforces approximately 10,000 requests per 10 minutes per app per tenant — which sounds generous until you realize a single flow paginating a large mailbox can consume thousands of calls.

---

Implementing Retry Logic for Throttling

Since the HTTP connector doesn't handle 429s automatically, you need to build retry logic yourself. The pattern is called exponential backoff: wait a little, retry. If it fails again, wait twice as long. Keep doubling until you either succeed or hit a maximum retry count.

Here's how to structure this in Power Automate:

Initialize variable: varRetryCount (Integer) = 0
Initialize variable: varMaxRetries (Integer) = 5
Initialize variable: varRetryDelay (Integer) = 10
Initialize variable: varSuccess (Boolean) = false

Do Until: varSuccess equals true OR varRetryCount >= varMaxRetries

  HTTP GET: [your API endpoint]
    (Run After: previous action failed OR succeeded)

  Condition: outputs('HTTP_API_Call')['statusCode'] equals 429
    Yes:
      Delay: @{variables('varRetryDelay')} seconds
      Set varRetryDelay = mul(variables('varRetryDelay'), 2)
      Set varRetryCount = add(variables('varRetryCount'), 1)
    No:
      Condition: outputs('HTTP_API_Call')['statusCode'] equals 200
        Yes → Set varSuccess = true
              [process the response]
        No  → [handle other errors - 401, 500, etc.]
              Set varRetryCount = add(variables('varRetryCount'), variables('varMaxRetries'))
              [this forces the loop to exit on non-retriable errors]

Tip: When the API returns a Retry-After header, use that value instead of your calculated backoff delay. It's more reliable than guessing: int(outputs('HTTP_API_Call')['headers']?['Retry-After']). Note that not all APIs include this header, so always have a fallback delay.

Respecting Retry-After Headers

When a Retry-After header is present, extract it with:

if(
  contains(outputs('HTTP_API_Call')['headers'], 'Retry-After'),
  int(outputs('HTTP_API_Call')['headers']['Retry-After']),
  variables('varRetryDelay')
)

Store this in a variable and feed it to your Delay action. The Delay action accepts a dynamic value in the seconds field.

---

Combining Pagination and Retry Logic

In production flows, you'll often need both: a pagination loop with retry logic on each individual page request. The structure nests the retry loop inside the pagination loop:

Outer Loop (pagination):
  Do Until: varNextLink equals "DONE"

    Inner Loop (retry):
      Do Until: varSuccess equals true OR varRetryCount >= varMaxRetries

        HTTP GET: current page URL (initial URL or varNextLink)

        Check for 429 → backoff and retry
        Check for 200 → extract next link, append data, set varSuccess = true
        Check for other errors → log and exit

      Reset varSuccess = false
      Reset varRetryCount = 0
      Reset varRetryDelay = 10

    Update pagination state (varNextLink or varOffset)

This is the production-grade pattern. Resetting varSuccess, varRetryCount, and varRetryDelay at the end of each outer loop iteration is critical — otherwise the retry state from page 1 bleeds into page 2.

---

Power Automate's Action Limits and Flow Design Constraints

Here's a constraint that surprises people: Power Automate flows are limited to 100,000 actions per flow run in most plans. Every action — including each iteration of a loop — counts toward this limit. If you're paginating 50,000 records with pages of 100 items, that's 500 HTTP calls plus 500 iterations of overhead = 1,000+ actions just for retrieval, before you process anything.

This is why your flow architecture matters for large datasets:

Chunking with Child Flows

For very large datasets, consider splitting the work across a parent flow and child flows. The parent flow breaks the data into date ranges or ID ranges and triggers a child flow for each chunk. Each child flow handles its own pagination within a smaller scope.

Parent Flow:
  For each month in last 12 months:
    Trigger child flow with:
      - startDate: first day of month
      - endDate: last day of month

Child Flow (triggered by HTTP or manually):
  Receives startDate and endDate
  Paginates API for records in that date range
  Writes results to destination (SQL table, SharePoint list, Dataverse)

This keeps each flow run well within action limits and makes it easier to retry just the failed chunk if something goes wrong.

Incremental/Delta Loading

For ongoing flows (not one-time migrations), build incrementally instead of re-fetching everything. Store a "last run timestamp" in a SharePoint list, environment variable, or Azure Table Storage. On each run, only query records modified after that timestamp:

Get variable: varLastRunTime from SharePoint config list

Query API with filter:
  modifiedAfter=@{variables('varLastRunTime')}

[process records]

Update varLastRunTime to utcNow() in SharePoint config list

This dramatically reduces how much data you need to paginate on each run and reduces your throttling exposure proportionally.

---

Hands-On Exercise: Building a Full Pagination + Retry Flow

Let's build a realistic flow: pulling all customer orders from a REST API for the past 30 days, handling pagination and throttling, and writing the results to a SharePoint list for downstream reporting.

Scenario: You're automating a nightly order sync from an e-commerce API (using offset/limit pagination) to a SharePoint list. The API has a limit of 200 requests per minute and returns 250 records per page.

Step 1: Initialize Variables

Create a new instant cloud flow (you'll schedule it later). Add Initialize variable actions for each of the following:

• varOffset — Integer — 0
• varPageSize — Integer — 250
• varHasMorePages — Boolean — true
• varRetryCount — Integer — 0
• varMaxRetries — Integer — 4
• varRetryDelay — Integer — 15
• varAPIToken — String — (your API bearer token, ideally from an Azure Key Vault action)
• varSyncDate — String — formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-dd')

Step 2: Build the Outer Pagination Loop

Add a Do Until action. Set the condition: varHasMorePages equals false.

Inside the Do Until, set a Timeout of PT2H (2 hours) and a max count of 500 to prevent infinite loops.

Step 3: Build the Inner Retry Loop

Inside the outer Do Until, add another Do Until. Condition: varRetryCount is greater than or equal to varMaxRetries OR a success flag is true.

Actually, let's simplify by using a scope action. Add a Scope action (for logical grouping) and inside it, add the HTTP action:

HTTP Action: HTTP_Get_Orders
  Method: GET
  URI: concat(
    'https://api.ecommerce.example.com/v2/orders',
    '?limit=', variables('varPageSize'),
    '&offset=', variables('varOffset'),
    '&created_after=', variables('varSyncDate'),
    '&sort=created_asc'
  )
  Headers:
    Authorization: concat('Bearer ', variables('varAPIToken'))
    Accept: application/json

Step 4: Handle the 429 Response

After the HTTP action, add a Condition that checks:

outputs('HTTP_Get_Orders')['statusCode']

equals 429.

If yes branch:

• Add a Compose action to calculate retry delay:

  if(
    contains(outputs('HTTP_Get_Orders')['headers'], 'Retry-After'),
    int(outputs('HTTP_Get_Orders')['headers']['Retry-After']),
    variables('varRetryDelay')
  )
  

• Add a Delay action: outputs('Compose_RetryDelay') seconds
• Set varRetryDelay = mul(variables('varRetryDelay'), 2)
• Set varRetryCount = add(variables('varRetryCount'), 1)

If no branch:

• Add another Condition: status code equals 200

  If 200:

  • Add Parse JSON on body('HTTP_Get_Orders') with schema matching your API response
  • Set varOffset = add(variables('varOffset'), variables('varPageSize'))
  • Condition: length(body('Parse_JSON')?['orders']) is less than variables('varPageSize') → Set varHasMorePages = false
  • Add Apply to each on body('Parse_JSON')?['orders']:
    • Inside: Create item in SharePoint with mapped fields
  • Reset varRetryCount = 0
  • Reset varRetryDelay = 15

  If not 200:

  • Add a Compose action to log the error: concat('API Error: ', string(outputs('HTTP_Get_Orders')['statusCode']), ' at offset ', string(variables('varOffset')))
  • Set varRetryCount = variables('varMaxRetries') (force exit of retry loop)
  • Set varHasMorePages = false (force exit of pagination loop)
  • Optionally send yourself a Teams message or email with the error

Step 5: Test with a Small Dataset First

Before pointing this at your full 30-day order history, change varSyncDate to yesterday's date only. Run the flow manually, verify the SharePoint list gets populated correctly, check the run history to confirm page counts match what you'd expect. Then expand the date range.

Tip: During testing, add a Terminate action after the first successful page completes — set it to Succeeded. This lets you verify one page works perfectly before letting the full loop run. Remove it before production deployment.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting to Reset Retry Variables Between Pages

If you initialize varRetryCount and varRetryDelay outside the pagination loop and never reset them inside it, after the first 429 your retry delay permanently doubles. By page 10, you might be waiting 2,560 seconds between retries. Always reset these at the start or end of each pagination iteration.

Mistake 2: Treating a 429 as a Flow Error

By default, the HTTP action returns status 429 as a successful action (the HTTP call itself worked — it got a response). Your flow won't error out; it'll happily process the 429 response body as if it were real data. You must explicitly check statusCode in your conditions. If you Parse JSON on a 429 response body expecting order data, you'll either get a parse error or process garbage.

Mistake 3: Not Setting a Loop Timeout or Maximum Count

A bug in your termination condition can send your flow into an infinite loop that runs for 30 days consuming actions and (if it's making API calls) potentially exhausting rate limits for your entire tenant. Always set a maximum count on your Do Until actions. A value of 1,000 is usually safe and acts as a circuit breaker.

Mistake 4: Using Apply to Each with High Concurrency During Pagination

If each page has 500 records and you process them with Apply to each set to max concurrency (50), you're making 50 simultaneous calls to your destination system. Multiply that by however many pages you have, and you'll throttle yourself on the write side even if you've handled the read side correctly. Tune concurrency based on the destination system's limits, not just "faster is better."

Mistake 5: Ignoring the SharePoint List View Threshold

If you're using Get Items with a filter and that filter column isn't indexed, SharePoint applies the filter after loading 5,000 rows — meaning queries that would return 200 matching items still fail if there are more than 5,000 total rows in the list. Always index columns you filter on in large SharePoint lists. You can add indexes from List Settings → Indexed columns.

Troubleshooting: Flow Returns Fewer Records Than Expected

1. Check if pagination is enabled on the Get items action (easy to miss)
2. Verify the threshold is high enough
3. Check if a SharePoint list view threshold is limiting your OData filter
4. Look for a @odata.nextLink in your HTTP responses that you're not following
5. Check if your termination condition is triggering prematurely (off-by-one on page size comparison)

Troubleshooting: Flow Randomly Fails on Large Runs

Large flows with many actions sometimes fail due to flow infrastructure timeouts rather than logic errors. Signs: the flow run history shows "running" for hours then fails with a generic error. Fixes: break the work into smaller child flows, add checkpointing (store progress in SharePoint so you can resume), or schedule multiple smaller runs instead of one large run.

---

Summary & Next Steps

Pagination and throttling are two sides of the same coin: APIs protect themselves by limiting what they give you in one response and how often you can ask. Your job as a flow developer is to respect those limits while still getting complete, accurate data.

Here's what you've learned to do:

• Enable and configure built-in pagination for SharePoint and Dataverse when it's sufficient
• Build manual Do Until pagination loops that follow @odata.nextLink, offset/limit patterns, or cursor-based URLs
• Implement exponential backoff retry logic for HTTP 429 throttling responses, including reading Retry-After headers
• Nest retry logic inside pagination loops and properly reset retry state between pages
• Architect flows for large datasets using child flows, incremental loading, and circuit breakers to stay within Power Automate's action limits

The patterns here compose. Once you've built the offset/limit paginator for one API, adapting it to a cursor-based API is a minor change in how you calculate the next request URL. Once you've built exponential backoff in one flow, you can paste it into any other flow that uses the HTTP action.

Where to go next:

• Dataverse performance optimization — learn about $select, $expand, and query execution plans to reduce the volume of data you need to page through in the first place
• Azure Logic Apps — the same pagination and throttling patterns apply, but Logic Apps offers more control over retry policies at the action level and longer execution times for very large jobs
• Power Automate Desktop + Power Automate Cloud — combining desktop flows for local data sources with cloud flow pagination patterns for hybrid data architectures
• Error handling and alerting in production flows — building robust error capture, logging to Application Insights, and automated alerting when pagination flows fail

Large-dataset flows are where Power Automate either earns its place in your architecture or exposes its limits. With the patterns in this lesson, you're equipped to push it much further than most practitioners realize is possible.