Top

Overcome 2000 items limit using Power Apps Collect function


This is going to be a short post. I want to share with you my approach for overcoming the threshold called data row limit, that prevents function “Collect” to get more than the set number of items. The data source in my case is SharePoint.

I was inspired by @mrdang‘s answer to a community question, where he advised to achieve it using batches. So I created this improved and shorter version of that draft idea.

Basics

For some of use it is a real surprise when we learn, that functions Collect and ClearCollect are actually… non-delegable. So when using a Collect(datasource) function, the maximum number of returned rows is limited by the data row limit setting:

By default, this is set to 500 and you can increase it to max. 2000.

In my case, the reason why I needed to load more data to a collection, was that later in the application I had to do complex filtering operations, which were non-delegable as well, so in the end I would never receive the data I was expecting.

Prerequisites

The only downside of my solution, that is caused by SharePoint, is 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 (source). If you are using other sources such as CDS or SQL Server, that shouldn’t be an issue.

So for SharePoint you need to have another column, that is the number type and that preferably has the same values as the corresponding ID column values.

Unfortunately too, it is not possible to use calculated column for this purpose as even if it’s set to “Number” Power Apps still recognizes it as text and doesn’t allow to use in range comparisons.

So how to do it?

First you need to calculate number of iterations, that will be required to download all data from data source. To do that, I am taking ID values of the first record and the last. Next, I divide their difference by the number of rows returned by single iteration. This number cannot be bigger than a set row data limit of course 🙂

Set(
    firstRecord,
    First('Large List')
);
Set(
    lastRecord,
    First(
        Sort(
            'Large List',
            ID,
            Descending
        )
    )
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500,
        0
    )
);
Collect(iterations, Sequence(iterationsNo,0));

Last step is to create collection, where each item is a number of an iteration. So eg. if I have 1000 records and want to get them in 500 per each iteration, I will have two iterations, so the iterations collection will contain: [0, 1] .

Next for each iteration, I am calculating the lower and upper boundary, to download items with IDs between. So eg. for first iteration I will need to get items having ID between 0 and 500:

ForAll(
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                LargeListSP,
                Filter(
                    'Large List',
                    ID_val > prevThreshold && ID_val <= nextThreshold
                )
            )
        )
    )
);

The Filter expression using Numeric column for range comparisons is delegable, so it doesn’t cause any issues 🙂 And that’s it. By following this pattern you are able to download in batches thousands of items from any data source. The value of ID_val in my case is just that additional column, with values like the corresponding values in the ID column.

This is how it looks in action:

No, I have no idea why rows count shows 3 items less than actually is, but well… Nothing is perfect 🙂


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

16 Comments
  • Catalin Palfi

    Hi Tomasz,

    This helped us a lot. Keep up the good work.

    Cheers,
    Catalin

    December 14, 2020 at 5:08 pm Reply
  • Monica Jagani

    This is perfect !!!

    To fix the issue of 3 items less, i could find one minor issue in code – replace setting of “iterationNo” variable as below –
    Set(
    iterationsNo,
    RoundUp(
    (lastRecord.ID +1 – firstRecord.ID) / 1000,
    0
    )
    );

    January 29, 2021 at 6:33 am Reply
    • Tomasz Poszytek

      Thanks for sharing 🙂 I also realized recently this is not getting me last couple of records, so what I am doing is simply incrementing number of calculated iterations by one. Just to be on a safe side 🙂

      February 5, 2021 at 11:52 am Reply
  • Mark Ireland

    Hi, Would it be possible for you to supply a export of the app above please?

    Thanks

    Mark

    February 5, 2021 at 10:32 am Reply
    • Tomasz Poszytek

      I won’t. The description of the approach is really well explained with all the required code described. Good luck!

      February 5, 2021 at 11:57 am Reply
  • Sebastian Morales

    Very helpfully information, thank you Tomasz!!
    I just had to make a little work around by Firstrecord und Lastrecord, because Powerapps told me that it was not delegable. But i put the the Sharepointlist in a gallery and then took the last ID.
    The only Problem that i cannot resolve yet, is to generate automatically the “ID_val” number 🙁 Any Idea how overcome that?

    Best regards,
    Sebastian Morales

    February 10, 2021 at 11:16 am Reply
    • Tomasz Poszytek

      Thanks for your kind words. You can create a flow that always updates ID_val with the corresponding ID value.

      February 13, 2021 at 10:28 pm Reply
  • Nimesh Fernando

    Thank you very much. this article helps me a lot.. ❤

    February 19, 2021 at 3:55 am Reply
  • Isaac Orantes

    Hello Thank you for posting this, I am trying to get the last 2000 records only instead of the whole data based on iterations. what do you suggest to do?

    February 22, 2021 at 10:45 pm Reply
    • Tomasz Poszytek

      Basically you can use the same logic, but once you calculate how many iterations you should make, just execute the last one, so only retrieve data in the last batch.

      February 27, 2021 at 1:50 pm Reply
  • Saurav Karna

    I am sorry but how did you create that ID_Val column in sharepoint?

    March 4, 2021 at 12:16 pm Reply
    • Tomasz Poszytek

      I have created a regular number column and used flow to populate it once an item is created.

      March 5, 2021 at 9:56 am Reply
  • Rodrigo P Chiesa

    Very helpfully information!

    Could you clarify to me what is the data in “(Value)”? Is the ID Column?

    prevThreshold: Value(Value) * 500,
    nextThreshold: (Value(Value) + 1) * 500

    March 18, 2021 at 2:14 am Reply
    • Tomasz Poszytek

      It’s current value from the iterations collection.

      March 25, 2021 at 10:05 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.