Handling More Than 2000 Records in Power Apps from SharePoint

When working with Power Apps and SharePoint, one common limitation developers face is the delegation limit. By default, Power Apps can retrieve only 500 records, and the limit can be increased to 2000 records in the app settings. However, many SharePoint lists contain more than 2000 records, which makes it difficult to load all the data directly.

In this blog, we will see a practical approach to load more than 2000 records from SharePoint into Power Apps using batching and an indexed column.

---

Why This Problem Occurs

Power Apps applies a delegation limit when retrieving data from SharePoint. If your SharePoint list contains more than 2000 items, Power Apps cannot retrieve all records in a single query.

Because of this limitation:

• Only partial data is loaded

• Some records are missing

• Filters or searches may not work correctly

To solve this, we can load the data in batches using a custom index column.

---

Solution Overview

The solution works in three steps:

1. Create a custom Index column in SharePoint.

2. Automatically populate the column using Power Automate.

3. Use Power Fx batching logic in Power Apps to retrieve records in chunks.

---

Step 1: Create an Index Column in SharePoint

Create a new column in the SharePoint list.

Column Name: Index_Id

This column will store a value that matches the ID column of the list.

Why this is required:

• SharePoint ID column cannot be indexed

• Delegation works better with indexed columns

---

Step 2: Enable Indexing in SharePoint

To improve performance, we need to index the column.

Steps:

1. Go to SharePoint List Settings

2. Select Indexed Columns

3. Click Create a new index

4. Select Index_Id

5. Save

Indexing improves performance when retrieving large datasets.

---

Step 3: Populate the Index Column Using Power Automate

Whenever a new item is created, we update the Index_Id column to match the SharePoint ID.

Flow steps:

1. Trigger: When an item is created

2. Action: Update item

3. Set:

Index_Id = ID

This ensures every item has a unique indexed value.

---

Step 4: Load Data in Batches in Power Apps

Now we retrieve records in batches of 2000 using Power Fx.

Add the following formula in Screen OnVisible.

// Batch size
Set(varBatchSize, 2000);

// Get max Index_Id
Set(
    varMaxID,
    First(Sort('Test Data for sp', Index_Id, SortOrder.Descending)).Index_Id
);

// Calculate number of batches
Set(
    varBatches,
    RoundUp(varMaxID / varBatchSize, 0)
);

// First batch
ClearCollect(
    NewData,
    FirstN(
        Sort('Test Data for sp', Index_Id, SortOrder.Ascending),
        varBatchSize
    )
);

// Load remaining batches
ForAll(
    Sequence(varBatches),
    With(
        {
            last: Last(NewData).Index_Id,
            nextLimit: Last(NewData).Index_Id + varBatchSize
        },
        Collect(
            NewData,
            Filter(
                'Test Data for sp',
                Index_Id > last && Index_Id <= nextLimit
            )
        )
    )
)

---

How This Formula Works

1. Define Batch Size

Set(varBatchSize, 2000)

We load data in chunks of 2000 records.

---

2. Get the Maximum Record ID

Set(varMaxID, First(Sort('Test Data for sp',Index_Id,Desc)).Index_Id)

This finds the largest Index_Id in the list.

---

3. Calculate Number of Batches

RoundUp(varMaxID / varBatchSize, 0)

This calculates how many batches are required.

Example:

If the list has 6500 records

6500 / 2000 = 4 batches

---

4. Load First Batch

ClearCollect(NewData, FirstN(...))

This loads the first 2000 records into a collection.

---

5. Load Remaining Batches

Using ForAll + Sequence, Power Apps loops through batches and loads remaining records.

Each iteration retrieves:

Index_Id > last && Index_Id <= nextLimit

This ensures only the next batch of records is retrieved.

---

Result

After running this logic:

• All records are stored in NewData collection

• You can bind galleries or tables to NewData

• The app can handle more than 2000 records

Example:

Gallery.Items = NewData

---

Advantages of This Approach

✔ Handles large SharePoint lists

✔ Works beyond the 2000 delegation limit

✔ Improves performance with indexed columns

✔ Data loads in controlled batches

---

Important Notes

• Always index the column used for filtering.

• Avoid loading very large lists at once if not required.

• Consider pagination or filtering for better performance.

---

Conclusion

Handling large datasets in Power Apps can be challenging due to delegation limits. By using a custom indexed column, Power Automate, and batch loading in Power Fx, we can efficiently retrieve more than 2000 records from SharePoint.

This approach is useful when building enterprise applications where SharePoint lists contain thousands of records.