Understanding Pagination in Power Automate

Pagination, also known as paging, is the process of dividing a document into discrete pages, either electronic pages or printed pages

For API's pagination in the process to split large queries into smaller easier to handle. So lets say you want 10,000 results, but the API limit is 1000, pagination would split the API into 10, calling 1-1000 first, then 1001-2000 next and so on.

Most API's use either one of two methods:

1. Built in pagination parameter
2. Offset Parameter

In Power Automate the out of the box Microsoft Connectors have pagination, though for some reason they are turned off. With them turned off you will only get 1 page of data.

Once turned on you will see a Threshold input, and this is where it can get a little confusing, check out below:

Pagination is set to 100 for both an Excel and SharePoint Get Items, yet Excel returns 256 and SharePoint 100.

This is because it uses Method 1, so it doesn't request 100, but enough page requests to get to 100. Excels page is 256 items, SharePoint 100 items. So even though I have set the threshold to 100, with Excel that is one page, it then rounds up to 256. Its the same with SharePoint, if I had a threshold of 101 I would get 200 (as needs 2 pages).

So the threshold isn't a hard limit, but used to find the page number.

But if you have ever used the SharePoint API you know you can return more then 100 results in one call, and that's because we are just calling with the page parameter, Power Automate just happens to work on default page limits which for SharePoint is 100.

What about Dataverse, just the same but with default page limit of 5000:

Outlook, well its a measly 25 and there is no pagination in the connector.

You have to use the HTTP connector to add pagination within the query.

The key take away is the threshold needs to be higher then the total items you want to return. The upper limit seems to change (Until recently SharePoints 5000 limit was enforced), currently its set to 1000 page requests, so SharePoints max is 100,000 and Excels is 256,000.

Though page limit is less if you dont have a premium license.

One more curve ball, and that's limiting returns with top count, as this limits returns per page not total (that's threshold). So setting one you would think you would get 1 response, but actually you still get the threshold you set, as now it just takes more pages and API calls (and more Power Platform API calls from your daily limit).

As you can imagine, setting a high threshold and low top count will slow your process down as it has to do multiple API calls, which most likely will get throttled. Below is same process, getting 400 items, both with pagination, but one with top count set to 1. If you want 1 you have to turn off pagination or set threshold to 1 as well.

Not all connectors have pagination, if your connector doesn't you can use Do Untils to create your own version, with the Do Until running until less then a full page of items is returned.