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

“‘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.

Add a Comment

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