Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to get over 100 items limit with the ‘Get items’ in Power Automate

Posted on August 8, 2021August 8, 2021 by Tom

“‘Get Items’ returns only 100 entries from all the items it should return, how can I overcome this limit and get all the relevant items with Power Automate?”


When you use the Power Automate actions designed to get data from SharePoint, e.g. ‘Get items’ or ‘Get files’, there’s also a field ‘Top Count’ under the ‘advanced options’. This field defines how many items it should return. If you want just a single item, you enter 1 in that field. If you want the last 10 items, you enter 10. And if you want all the items, you might keep the field empty based on the description: “Total number of entries to retrieve (default = all).” But the ‘all’ value doesn’t really mean ‘all’ items in the actions. If you keep the field empty, it’ll return only the first 100 items and ignore the rest.

Increase the items limit

To return all the items you must type in a number in the ‘Top Count’ field. A number between 1 and 5000 will override the ‘all’ limit of 100 items or files, and give you the corresponding number of items (if they exist).

Power Automate Get items 100 limit

The actual number you use is up to you. You can use 5000, the maximum, in all the actions. Since it’ll return only the existing items, it won’t slow the flow down. Or you can decrease the number of items down to 1 if you’re implementing the lookup functionality.

There might be of course situations when 5000 is not enough, in those you can try to filter the items with Filter Query, or check Paul’s article on overcoming the 5000 items limit.

Summary

I think that ‘Get items’ is one of the most important actions in Power Automate, and the default limit of 100 items shouldn’t be there. Especially not with such a confusing description in the ‘Top Count’ field. It’s hard to notice this limitation right away as you probably won’t have over 100 items during the development, but it can start making problems later. Whether you build some reminder flow or report with SharePoint items, one day you might start missing items just because of this limit.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 thoughts on “How to get over 100 items limit with the ‘Get items’ in Power Automate”

  1. Mayur says:
    May 5, 2022 at 2:17 pm

    This was a very helpful tip.

    Reply
  2. RD says:
    February 2, 2023 at 5:54 pm

    Very helpful. However, I found that it slowed my flow down substantially, so it seems like it is in fact pulling the number of items specified, rather than just all the items in the list. For example, I am “getting” ~500 items from a list, but this number is going to fluctuate so I used 5,000 as the top count. My flow run time went from ~1.5 minutes to go through the default 100 items, to just over 14 minutes. Just something to be aware of.

    Reply

Leave a Reply Cancel reply

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

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes