1. Introduction

Handling large datasets in Power Apps can be challenging due to delegation limits and performance issues. SharePoint, by default, only retrieves the first 500 records (or up to 2000 records if configured). This guide will explain how to efficiently load large SharePoint data and display it in a Gallery.

2. Understanding Delegation in Power Apps

Delegation is the process where Power Apps offloads data processing to the data source. SharePoint has delegation limits, and exceeding these can result in incomplete data being fetched.

• Default limit: 500 records
• Maximum limit: 2000 records (can be changed in app settings)
• Non-delegable functions: Some formulas force Power Apps to retrieve only the first 500/2000 records instead of querying the full dataset.

3. Creating a SharePoint List

Create a SharePoint list named EmployeeRecords with the following columns:

• Title (Single line of text) – Employee Name
• Age (Number) – Employee Age
• Department (Choice) – Employee Department
• JoiningDate (Date) – Date of Joining

4. Setting Up Power Apps Gallery

1. Open Power Apps Studio.
2. Insert a Gallery (Vertical layout) into the screen.
3. Connect the Gallery to SharePoint:

Gallery1.Items = EmployeeRecords

4. This will only return 500/2000 records if delegation issues exist.

5. Fetching Large Data Using Pagination

Step 1: Create a Collection for Large Data Handling

1. Set the OnStart property of the app:

ClearCollect(colEmployees, EmployeeRecords)

2. This approach is limited to 2000 records. For larger datasets, use pagination.

Step 2: Implement Pagination

1. Create two global variables to track pagination:

Set(CurrentPage, 1);
Set(PageSize, 100);

2. Modify the Gallery Items property to show paginated data:

FirstN(Skip(colEmployees, (CurrentPage - 1) * PageSize), PageSize)

3. Add Next and Previous Buttons to navigate pages:

• Next Button OnSelect:

If((CurrentPage * PageSize) < CountRows(colEmployees), Set(CurrentPage, CurrentPage + 1))

• Previous Button OnSelect:

If(CurrentPage > 1, Set(CurrentPage, CurrentPage - 1))

6. Using Power Automate to Retrieve More Than 2000 Records

Power Automate can be used to bypass the delegation limit.

Step 1: Create a Power Automate Flow

1. Go to Power Automate and create a new Instant Cloud Flow.
2. Add ‘Get Items’ action for SharePoint and select EmployeeRecords.
3. Set Pagination in Settings and set Threshold = 100000.
4. Add ‘Response’ action to return JSON data.

Step 2: Call Power Automate from Power Apps

1. Create a Power Automate Flow connection.
2. Use the below formula in the OnVisible property of the screen:

ClearCollect(colEmployees, FlowName.Run().response)

7. Optimizing Performance

1. Limit Columns: Retrieve only necessary columns using ShowColumns().

ClearCollect(colEmployees, ShowColumns(EmployeeRecords, "Title", "Age", "Department"))

2. Reduce Gallery Items: Use Lazy Loading with FirstN().
3. Use Delegable Filters: Prefer StartsWith() over Search() for filtering.
4. Enable Data Row Limit in Settings: Increase to 2000.

8. Conclusion

By using pagination, Power Automate, and performance optimizations, you can efficiently retrieve and display large datasets from SharePoint in Power Apps.