Handling Pagination and Large Datasets in Power Automate

The first version of a flow usually works because the test list has fifty rows. Production has sixty thousand rows, wide columns, slow connectors, and a run duration limit waiting to punish every optimistic design. Pagination is not a checkbox you enable at the end; it is a data access strategy.

Default limits are intentionally conservative

Know what the connector returns before you design the loop. Many list actions return a limited page by default. SharePoint Get items and Dataverse List rows are designed to protect the service and the flow runtime, not to dump an entire operational dataset into memory.

Turning on pagination in the action settings allows the connector to retrieve additional pages up to the threshold you specify. For common Dataverse and SharePoint scenarios, makers often set thresholds up to 100000, but the correct number is the smallest number that satisfies the business process. Pulling one hundred thousand rows into a flow just to find twelve records is a design bug.

The threshold is not the same as page size. It is the maximum number of items the action is allowed to return across pages. The connector still pages under the hood, tracks continuation, and assembles the result for downstream actions.

Setting or concept	What it controls	Practical guidance
Pagination toggle	Whether the action follows additional pages	Enable it only when the flow truly needs more than the default
Threshold	Maximum items returned across pages	Set a business limit, not the theoretical maximum
Top Count or $top	Requested page size or row count	Use for controlled batches and predictable payloads
$select	Columns returned	Always reduce wide payloads
$filter	Rows returned	Push filtering to the data source
@odata.nextLink	Continuation URL for another page	Useful in custom HTTP paging patterns

Pagination follows continuation links under the hood

The platform handles continuation for built-in actions. When pagination is enabled, actions such as Dataverse List rows and SharePoint Get items keep requesting pages until the threshold is met or the data source has no more rows. In OData-based APIs, the continuation is commonly exposed as @odata.nextLink.

When you use HTTP directly against an OData endpoint, you may need to manage that continuation yourself. The pattern is simple: call the first URL, process the response, store the next link, and repeat until the next link is empty.

First request → process value array → read @odata.nextLink → request next page → repeat until blank

A common expression to read the continuation link from an HTTP response is:

body('HTTP_List_Rows')?['@odata.nextLink']

For a loop condition, keep the risky expression inside an action or condition field, not scattered through several Compose actions. Every extra place you expose pagination state becomes another place to leak complexity into run history.

$top and threshold solve different problems

Use $top when you want fewer rows. Use threshold when you are willing to retrieve more pages. In Dataverse, Top Count limits how many rows you ask for. In OData queries, $top does the same job. Pagination threshold tells Power Automate how far it may keep going when more pages exist.

These examples show intent more clearly than prose:

$select=accountid,name,modifiedon&$filter=statecode eq 0&$top=5000
$select=Title,Status,Modified&$filter=Status eq 'Ready'&$top=2000

If your flow needs the newest five thousand active rows, ask for that. If it needs to process every open item nightly, use a filter and a threshold that matches expected volume plus a safety margin. If the result can exceed that margin, split the workload by date, business unit, region, or another stable partition.

Payload reduction is the cheapest performance win

Every unnecessary column is paid for multiple times. It is retrieved by the connector, serialized into the run history, passed into loops, and often copied into variables. Large payloads amplify memory pressure and make troubleshooting slower.

Use $select in Dataverse and SharePoint whenever the connector exposes it. Do not retrieve attachments, long text, audit-style notes, or lookup expansions unless the flow actually needs them. For Dataverse, be deliberate with expanded navigation properties because they can multiply payload size fast.

Prefer server-side filtering over client-side conditions. A flow that retrieves fifty thousand rows and then uses a Condition action to keep two thousand has already lost. Push status, date, owner, and category filters into the list action. When the source supports indexed columns, align filters with indexes to avoid throttling.

Flow runtime limits still matter

Pagination does not make a flow infinite. Large actions can hit connector throttling, timeout, memory pressure, action count limits, and long-running loop behavior. Apply to each loops over tens of thousands of rows are fragile, especially when each iteration calls another connector.

For high-volume work, batch the operation. Dataverse has batch-friendly APIs and native query capabilities. SQL can aggregate and filter far more efficiently than a flow loop. SharePoint can work for moderate business lists, but it is not a data warehouse. If you need joins, analytics, or recurring million-row scans, move that logic closer to the data platform.

A useful decision test:

If the flow reads many rows and updates a few → improve filters
If the flow reads many rows and updates many → batch or move to Dataverse or SQL
If the flow only routes events → process changes incrementally instead of scanning

Incremental patterns beat heroic pagination

The best large dataset is the one you never load. Use trigger conditions, change tracking, modified date windows, or status transitions so each run processes a small slice. Store a watermark such as the last successful modified time when the process is deterministic. For Dataverse, consider change tracking or a purpose-built query when the business requirement is synchronization.

Pagination is a tool, not a license to ignore data shape. Set thresholds intentionally, reduce payloads aggressively, and push work to systems built for querying when the volume crosses from workflow into data processing. The senior move is not proving a flow can drag back one hundred thousand rows; it is designing so it rarely has to.