How to load 5000 data in Power Apps gallery

In Power Apps, loading 5000 records into a gallery requires careful handling because of delegation limits and performance considerations. By default, Power Apps only retrieves 500 records from non-delegable queries, but you can extend this to 2000 records in app settings. To go beyond that (like 5000), you need to use delegation-friendly data sources and queries.

Steps to Load 5000 Records in a Gallery

1. Use Delegable Data Sources

• Dataverse, SQL Server, SharePoint (with limitations).

• Ensure your query functions (e.g., Filter, Sort, Search) are delegable. Non-delegable queries will cap at 2000 records.

2. Enable Data Row Limit

• Go to File → Settings → Advanced Settings → Data row limit.

• Set it to 2000 (maximum allowed for non-delegable queries).

3. Implement Pagination / Lazy Loading

Since 2000 is the hard cap for non-delegable queries, you need to load data in chunks:

• Use Collections with Collect() or ClearCollect() to fetch multiple batches.

• Example for SharePoint:

powerfx

ClearCollect(
   colData,
   FirstN(Filter(MyList, Status="Active"), 2000)
);
Collect(
   colData,
   LastN(Filter(MyList, Status="Active"), 2000)
);

Repeat until you reach 5000 records.

4. Use Concurrent Loading

To speed up batch loading, use Concurrent():

powerfx

Concurrent(
   ClearCollect(colData1, FirstN(MyList, 2000)),
   ClearCollect(colData2, LastN(FirstN(MyList, 4000), 2000)),
   ClearCollect(colData3, LastN(MyList, 1000))
);
ClearCollect(colData, colData1); Collect(colData, colData2); Collect(colData, colData3);

Now your gallery can bind to colData with ~5000 records.

5. Optimize Gallery Performance

• Use delegable filters to reduce unnecessary data.

• Display only required columns (avoid ShowColumns on huge sets).

• Consider virtual scrolling or Load More button for user-friendly pagination.

⚠️ Key Considerations

• Delegation is the best practice: If your query is delegable, you can load millions of records without hitting the 2000 limit.

• If your source/query is non-delegable, you must rely on batch loading into collections.

• Loading 5000 records at once may impact performance; consider progressive loading (e.g., load 1000 at a time as the user scrolls).