Power Apps SharePoint Pagination: Load 500 Records at a Time Using Index_Id

When working with large SharePoint lists in Power Apps, you may hit the delegation limit if you try to load more than 2,000 records at once. To build an efficient app that scales beyond this, we can use a custom pagination technique - loading data 500 records at a time using a numeric Index_Id column.

In this post, I’ll walk you through a fully working solution that loads SharePoint list items in chunks, supports “Next” and “Previous” buttons, and avoids delegation issues by leveraging indexed fields.

Requirements

• A SharePoint list connected to Power Apps (e.g., 'Test Data for sp')

• A numeric Index_Id column in SharePoint - must be indexed

• Items are assumed to be ordered sequentially by Index_Id

Pagination Strategy

We:

• Use a local collection (NewData) to hold 500 records at a time

• Track the last shown Index_Id using a variable (lastID)

• Control records per page using a variable max1 (e.g., 500)

This method:

• Avoids delegation warnings

• Improves performance

• Scales to thousands of records safely

Implementation Steps

---

1. Screen OnVisible

ClearCollect(NewData, FirstN(Sort('Test Data for sp', Index_Id, Ascending), 500));
Set(lastID, Last(NewData).Index_Id);
Set(max1, 500);

This initializes the gallery with the first 500 records, sorted by Index_Id

2. Next Button OnSelect

If(
    IsEmpty(Filter('Test Data for sp', Index_Id > lastID)),
    Notify("No more records.", NotificationType.Information),
    ClearCollect(NewData,
        Filter(
            'Test Data for sp',
            Index_Id > lastID && Index_Id <= lastID + max1
        )
    );
    Set(lastID, Last(NewData).Index_Id)
)

Loads the next 500 records based on the current lastID.

3. Previous Button OnSelect

Set(tempID, First(Sort(NewData, Index_Id, Ascending)).Index_Id);
If(
    tempID <= 1,
    Notify("You're already at the first page", NotificationType.Information),
    ClearCollect(NewData,
        Filter(
            'Test Data for sp',
            Index_Id <= tempID - 1 && Index_Id > tempID - max1 - 1
        )
    );
    Set(lastID, Last(NewData).Index_Id)
)

Loads the previous 500 records by recalculating the window using the lowest currently loaded Index_Id.

Tips

• Use a number column (not calculated column) for Index_Id

• Ensure Index_Id is indexed in SharePoint to enable delegation

• Add variables like CurrentPage if you want to show page numbers

https://mspowerplatformtips.blogspot.com/2025/07/power-apps-excel-pagination-load-500.html