Breaking Boundaries: How to Get more than 2000 Rows from SharePoint to Power Apps from SharePoint

 Did you know that functions like Collect and ClearCollect in Power Apps are non-delegable?

This means that when you use Collect(datasource) function, the maximum number of returned rows is limited by the data row limit setting. By default, this limit is set to 500, but you can increase it to a maximum of 2000.

The problem is when you needed to load more than 2000 data to a collection, and in the application you have to do complex filtering operations, which were non-delegable as well, so in the end you will never receive the data I was expecting: you will risk having wrong or missing data.

And this is explained by the fact, that column ID cannot be used for range comparisons (higher, lower, etc…) because using it for such makes the whole query non-delegable .

You can find more information about delegable functions and operations here .

Solution

So the solution is very easy .

Step 1 :

We need only to create an additional column of type "number" that ideally has matching values to the corresponding ID column.

Step 2:

The next step is to determine the number of iterations needed to download all the data from the data source. So we will calculate the difference between the ID values of the first and last record, and then dividing that difference by the number of rows returned in a single iteration.

It's important to keep in mind that this number cannot exceed the maximum row data limit!!

So let's put this code in the on visible of the screen in which you want to dispaly the data.

Set
   ( firstRecord,
    First('the name of your sharepoint list')
);
Set(
    lastRecord,
    First(
        Sort(
            'the name of your sharepoint list',
            ID,
            Descending
        )
    )
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500,
        0
    )
);
Collect(iterations, Sequence(iterationsNo,0));

The final step is to generate a collection containing the iteration numbers, with each item representing a single iteration. For example, if there are 1000 records to be downloaded in batches of 500 per iteration, the iteration collection will contain the values [0, 1] to represent the two required iterations.

For each iteration, the lower and upper boundary values are calculated to specify the range of item IDs to be downloaded. For example the first iteration,will retrieve items with IDs between 0 and 500.

Clear(your collection name);
ForAll(
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                your collection name,
                Filter(
                    'the name of your sharepoint list',
                    ID_val > prevThreshold && ID_val <= nextThreshold
                )
            )
        )
    )
);

So the code must be like this:

Set
   ( firstRecord,
    First('the name of your sharepoint list')
);
Set(
    lastRecord,
    First(
        Sort(
            'the name of your sharepoint list',
            ID,
            Descending
        )
    )
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500,
        0
    )
);
Collect(iterations, Sequence(iterationsNo,0));

Clear(your collection name);
ForAll
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                your collection name,
                Filter(
                    'the name of your sharepoint list',
                    ID_val > prevThreshold && ID_val <= nextThreshold
                )
            )
        )
    )
); 

Results:

The Filter expression using a Numeric column for range comparisons is delegable, meaning that it won't cause any performance issues. By following this approach, it's possible to download thousands of items from any data source in batches.

In my case, the value of ID_val refers to the additional column I created, with values corresponding to those in the ID column. Here's how it looks in action: