Loading large datasets into Power Apps galleries or data tables can be a significant challenge in app development, especially when dealing with SharePoint’s 5,000-item limit and Power Apps’ 2,000-delegation row limit. In this blog, we will be discussing how you can easily load all SharePoint items into Power Apps with a simple solution.

These constraints often result in incomplete datasets being retrieved from a SharePoint list or library. In practice, delegation issues are difficult to avoid, especially when search, filter, or sort operations are applied to complex columns.

The following formula offers a dynamic solution to the challenge of loading large datasets into Power Apps and has been used successfully in a real-world app we developed for our client: 

To implement this solution, begin by creating a number column named “IndexID” in the SharePoint list or library. Then, either create or use an existing automated flow to copy the item ID into this column during item creation. 

The core idea of the formula is to break the entire dataset into chunks based on Power Apps’ data row limit settings and then load each chunk into a collection.

Two variables, _minID and _maxID, define the range, since items are often archived periodically in many cases. The variables _end and _start serve as “page numbers” to generate sequential pages (_seq) in descending order (the formula can be adjusted if ascending order is preferred). The collection, colDocuments, returned by the formula includes all items in descending order. 

A final tip: since formulas that involve behavior actions cannot be saved as named formulas, to reuse the solution in multiple places in the app, add the formula to the OnSelect method of a hidden button. You can then call it with Select(<hidden button name>). 

We hope this blog was helpful and you can now easily load all your SharePoint items into Power Apps without delegation limitations. If you have another project in mind, get in touch with us by filling out the form at the bottom of the page.

Thanks for reading! Make sure to subscribe to our blog. We publish technology tips, tricks, and updates every week.     

Discover More