“‘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).
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.
This was a very helpful tip.
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.
Still very helpful. Thanks for far!