October 16, 2024

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 have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

6 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

  2. 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?

    1. 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:
      How to do an OData search for a lookup parent column
      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.

      1. Actually, looking at my action this what I am already doing when the error occurred

        Get Item Action
        Site Address:
        List Name:
        Id :

        # 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

        Site Address:
        List Name:

        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?

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon