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 important that you know about this limitation.
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.

If you don’t enable the “Pagination” the “Threshold” won’t work. Please make sure that the “Pagination” is “on”.

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:

We’ll get:

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.

You may get more items than defined in the “Threshold” field. Power Automate will return multiples of the the items returned without the pagination enabled. For example, SharePoint returns 100 elements without pagination, but with pagination it will return multiples of 100 until the max number of records is reached.

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.

Final thoughts

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.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Bruno Oliveira on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

2 thoughts on “Power Automate: What is Pagination?

  1. Your site is very interesting, but I cannot see any of the images on any of the pages in your websit

    1. Hi Gerhard,
      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
      Manuel

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: