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.
It’s quite common to all kinds of actions, not only SharePoint. So if you have more either create an OData query or read on.
Let’s see what “Pagination” is and how to use it.
What is Pagination?
Pagination was introduced by Microsoft to enable users to fetch more records from a data source. So how to access it? Using the SharePoint “Get Items” action, we need to go to the “settings.”
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.
Photo by Bruno Oliveira on Unsplash
6 thoughts on “Power Automate: What is Pagination?”
Your site is very interesting, but I cannot see any of the images on any of the pages in your websit
Thanks for reaching out, and my apologies. It’s an issue in only a few countries, but unfortunately, yours is one of the few affected.
I’m working with the hosting company to sort it out.
Thanks again and sorry for this
I am hitting this issue in a SharePoint list with 5000+ items . I want to bring back a single item based on child integer key in my main list. I have set both the Pagination to 5000 and the Top Count in the Get Items Action to 5000 as well ( was set to 1) . I guess my question is , should I adjust either of these to avoid performance issues?
If I understand this correctly, you want to filter the data based on the parent id (probably a lookup id) in a list? To do this, it’s better to use the “Filter Query” and build an OData expression that will bring only the data you need. No pagination, top count, etc. required.
For example, if I have a lookup column in SharePoint called “ParentList”, I can then filter the “ChildList” as follows:
The performance issues come from the volume of data since SharePoint sends a lot of information per item to Power Automate. Also, more data will make the trigger slower—no need to deal with all of this if you fetch only the data you need.
Thanks I will try this and let you know how I get on.
Actually, looking at my action this what I am already doing when the error occurred
Get Item Action
# now I have got my parent Item I can look at my child approval items to find the related item.
So in my Get Items Action
Filter Query: ChildId = ID
So I guess it already is per your comment. It worked fine until the child list exceeded 5000 items. In this case do I stick with the pagination and top count at 5000 respectively?