I’m sure this happened to you. You use a SharePoint “Get Items” action, and you only get 100 items. Of course, you know there are many more, but why we’re not getting the rest of them? The short answer is that Power Automate limits the number of records to 100, but you can get more items. To do that, Power Automate has a configuration called “Pagination” that allows you to define the number of items you want to get.
Let’s see what “Pagination” is and how to use it.
What is Pagination?
After that, we’ll get the advanced options like this:
As we can see above, we can enable the pagination and optionally define the “Threshold” that limits the number of records returned. Please note that this is different than an OData query, but we’ll get there below.
So let’s first see what we get with no limitation. We’ll use a simple SharePoint “Get Items” action as an example with a “Compose” action with the length function to tell us how many items were returned.
We get the following result:
Now let’s limit to 5:
The same for when we define 102:
Not what you would expect, right? I wanted to show you this example for you to understand what’s happening in the background.
When Power Automate executes the SharePoint “Get Items” action, it will call SharePoint and fetch 100 items. If we define the threshold as 5, Power Automate will fetch the data once and get 100 elements so it won’t discard the other 95 items. The same with 102, where it will fetch twice and return 200 items.
If you want to have a specific number of records, you should use the “Top Count” parameter if available.
The OData query and other parameters in your action will take precedence since it filters the data source. For example, if you define the “Threshold” to 200 but limit it to the top 5 in the “Top Count,” then Power Automate will only return five items.
Fetch only what you need
Pagination can have a massive impact on the performance of your Flow. If you’re not careful, you can have long-running Flows because they are fetching the whole dataset. Depending on the number of rows, it may take a long time to fetch everything. So only enable “Pagination” if you need more information than the returned by default.
If you don’t need the entire dataset, define limits in the “Top Count” so that you get only the information you need.
Pagination can both be your best friend or kill the performance of your Flow. So pay special attention to your Flow when you enable “Pagination” and always define a threshold if you can.