Introduction

PowerApps is super cool. I love PowerApps. I’ve been able to create some really awesome solutions for myself, my team, and my entire organization. I wish something like PowerApps had existed a dozen years ago when I ran my own business. My clients would have loved it!

I also really hate PowerApps sometimes. Particularly the first time I beat my head against the large list wall on an app I developed for my organization. It was a sort-of calendar/activity tracking app that served over 700 users distributed across dozens of teams across the country. The SharePoint list data source had a ton of columns to track Important Things for each activity. The app also had many different role-based views and screens with custom approval workflows for the items (Ed. note: Power Automate’s built-in approval flows aren’t great for apps with a lot of users, because they can’t be managed in the app. You’re better off putting something together yourself. Maybe I’ll do a write-up on it!).

We knew this app would generate 10’s of thousands of records every year, and the many different variables we needed to filter by meant big delegation problems.

The Delegation Problem

There are a ton of blog, forum, and support posts on the issue of the delegation limits for non-delegable queries in PowerApps for various data sources, particularly SharePoint. I won’t rehash the whole subject here, but I’ve noticed that the write-ups and answers usually don’t fully acknowledge the limitations faced by the so-called “citizen developers” that low/no-code tools like PowerApps are marketed towards. You probably already noticed this too.

“Use a real database” they often say. If only it were that easy. If you’re anything like me when I was learning how to build things in PowerApps, you don’t have the luxury of provisioning a “real” relational database (such as SQL, or even Dataverse) in the environment you’re working in. The Power Platform is probably just a bonus to your company’s Microsoft 365 license that nobody has paid much attention to, because it doesn’t cost extra. Try asking your IT department for more expensive licenses and elevated roles…I’ll wait.

How did it go? Not well I imagine. If you have a progressive and helpful IT department to rely on, you probably wouldn’t be here. Or, you’re an actual citizen developer or maybe small business owner with a basic Microsoft 365 plan that includes the Power Platform and simply don’t have the luxury of more sophisticated solutions support.

“Use a real database. SharePoint isn’t designed to work with large datasets.”

Someone trying to be helpful on the PowerApps forums

The fact of the matter is that SharePoint is absolutely designed to handle large numbers of records. Microsoft quotes something like 30 million records PER list or library! If you’re worried about that limit, well, you probably should invest in a “real” database and not bother with a low-code platform like PowerApps.

Ok, we’ve fully established that you’re stuck with SharePoint and you’re just going to have to work around it’s limitations. First thing we’re going to do is generate a large list in SharePoint to work on, for which we’re going to use Power Automate. I’ll intersperse this post with tips and screenshots for all of the tools we’re going to use for this exercise.

Creating a lot of records in a SharePoint list with Power Automate

First we have to Initialize the variables that we’re going to use to control the Do until loops we need to create items in the list. Initialize Variable cannot be a dependent action in Power Automate, so all of your variables will come before the rest of your actions.

I try to be consistent in my nomenclature and preface all variables with “var.” In this example, were simply creating integer variables that we’ll increment with each loop so that the loops stops at 6000 items. We’re going to have to create 2 similar branches for this flow due to a hard limit of 5000 loops for each Do until action in Power Automate.

PRO TIP: You could break up the flow into multiple identical parallel branches to dramatically improve the speed of the flow. 6 branches of 1000 loops will run much faster than 2 branches with 3000!

Next, we add a Do Until action where the loop will continue until varItemNumber is equal to 3000. We’ll also add a second parallel action at this point. You’ll note below that I changed the limits to a Count of 3000 to ensure that all 3000 items are created. Strictly speaking, we could simply create an open ended condition where “varSomething equals true” so that the loop would continue until the limit was reached. However, you’ll note in the next action for Create item, I used varItemNumber variables in each branch as dynamic content in the item Title field for illustrative purposed. Often the variable may serve multiple functions in a flow.

THE FOLLOWING IS A CRITICAL STEP IN DEALING WITH LARGE LISTS!

We’re going to add an Update item in the next step to copy the ID for the item to a RecordNumber field I created for this purpose. The ID field in SharePoint is only delegable for =, even though it’s a numeric field. Regular numeric fields in SharePoint are delegable for =,<,>,<=,>=, which is going to be important later in PowerApps and will make your life much easier. You can achieve this step in a separate flow or directly in your app, but you should get into this habit when you’re building your lists.

Finally, we added a step to increment the varItemNumber variables by 1 with each loop as illustrated above. This flow will produce 6000 items in our list with the title for each similarly incremented and unique as a result.

And here’s our list! Ignore the difference in the Title compared to the RecordNumber. This wasn’t really a blank list. Close enough!

Working with the Big SharePoint List (BSPL?) in PowerApps

Time to play with the BSPL!

I created a simple 3 screen app from the BSPL in PowerApps. You’ll note below that we’re already getting an error due to the default 3 screen app template using both a non-delegable query and querying the Title column with is not an Indexed column in the source SP list. You’ll get this list view threshold error for any non-delegable query on a non-indexed column.

This list view threshold error is easy to fix. Click the image to play the GIF that shows the steps to index a column in SP.

That fixed the list view threshold error. However, we still have a delegation issue.

The Items property filter that refers to the search box in the base template uses StartsWith, which IS delegable to SharePoint. But how often would a user know precisely what the field they’re searching for starts with on a 6000 item list? Not very useful is it? So like many others you’ve probably decided that using the In operator would be preferable so that you can search for a string that occurs anywhere in a field. And now we encounter our delegation issue again.

Other non-Delegable operators that you probably want to use include:

• CountRows, which you might use to evaluate the list based on certain filters.
• DateAdd, which you might use calculate date ranges for past due items.
• Not (alternatively !), to exclude a true statement from a filter.
• <> (not equals), similar to Not, but for numbers!
• Pretty much any math operator except =, really.
• And so on, and so forth for many helpful operators.

So, despite many friendly PowerApps forum folks suggesting that your app is getting too complex and/or you need a real database, you’ve decided to persevere like I did!

Large List Collection Method One

Your first thought is probably to try inserting a button and putting something simple on the OnSelect property like this:

ClearCollect(colBSLP,'6000 Items List')

And at first you think you’ve got it, because, frustratingly, PowerApps does not display a delegation error when you try to collect from the BSPL, as you’ll note in the screenshot below. But you immediately notice that you’re not collecting all the rows (because you created a label to display the number of rows in the collection using the CountRows function).

Now, I finally bring you the first and perhaps simplest collection method for a very large list.

This method works best if you want to just collect all of the records from the list, you know how big your list is going to get, and the total number of records is staying well under 6 figures.

ClearCollect(
    colBSPL,
    Filter(
        '6000 Items List',
        RecordNumber >= 1 && RecordNumber <= 2000
    ),
    Filter(
        '6000 Items List',
        RecordNumber >= 2001 && RecordNumber <= 4000
    ),
    Filter(
        '6000 Items List',
        RecordNumber >= 4001 && RecordNumber <= 6000
    ),
    Filter(
        '6000 Items List',
        RecordNumber >= 6001 && RecordNumber <= 8000
    )
)

This formula simply breaks the list up into Delegable chunks of 2000 records each using a known value. Remember how we copied the item ID over into a new RecordNumber column earlier? Using the ID field would function fine in this formula, but you’d get a ton of Delegation warnings, which is super annoying.

Another way to do this is to create a sort of “Collection of Collections,” like so:

Concurrent(
    ClearCollect(
        colBSPL1,
        Filter(
            '6000 Items List',
            RecordNumber >= 1 && RecordNumber <= 2000
        )
    ),
    ClearCollect(
        colBSPL2,
        Filter(
            '6000 Items List',
            RecordNumber >= 2001 && RecordNumber <= 4000
        )
    ),
    ClearCollect(
        colBSPL3,
        Filter(
            '6000 Items List',
            RecordNumber >= 4001 && RecordNumber <= 6000
        )
    ),
    ClearCollect(
        colBSPL4,
        Filter(
            '6000 Items List',
            RecordNumber >= 6001 && RecordNumber <= 8000
        )
    )
);
ClearCollect(
    colBSPL,
    colBSPL1,
    colBSPL2,
    colBSPL3,
    colBSPL4
)

You’ll notice I wrapped the collections in a Concurrent function so all the sub-collections execute at the same time. This is the method that you’ll see most often suggested on the PowerApps forums. I’ve tried both of these methods in a live environment and haven’t noticed a substantive difference in speed. I think the first is a much simpler expression and it seems to run pretty “concurrently” as is.

Limitations for BSPL Collection Method One

The code can get out of hand pretty quick once you get into many 10’s of thousands since you have divide everything into chunks of 2000. Do the math, at 100k records you’re up to 50 iterations of the filter formula and combining 50 collections. And imagine if your filter formula is more complex than just the RecordNumber? You ARE going to end up complicating that filter for your business purposes and applying it to more specific situations. Imagine manually editing each of those 50 strings every time you need to change the filter!

All of the above is manageable if the data is fairly static, of course. (If it was completely static, you’d just be using a spreadsheet as a static data source, right?). The next limitation you’ll run into if the data is being edited fairly frequently is that ClearCollecting all of those records multiple times during a user’s session is going to be a pretty crappy user experience.

If your BSPL has a ton of columns, particularly LookUps, it’s just going to take more and more time for the items to collect. (Ed. Please, just don’t with the LookUps, BTW. They’re so tempting when you’re working in SP, but are a nightmare in PowerApps and add a tremendous amount of overhead to queries. Trust me.) The more Columns you have, the longer it will take, too. You may want use the ShowColumns function before the Filter to limit the collection to only the relevant data.

Large List Collection Method Two

This method builds on the concept of Method One, but it utilizes a table to define the ranges that you’re going to use for the filter formulas in the collection. We’ll then use the ForAll function to loop through the range table to create our collection. You can use a static spreadsheet for this table, another SharePoint List, or simply Collect the table dynamically in the app via OnStart, OnVisible, or some other behavioral property. I’ll demonstrate how to Collect in app, but you can get really creative with the other methods.

Collect the “range” table in app

ClearCollect(
    colBSPLLoopValues,
    {
        BeginRecordNumber: 1,
        EndRecordNumber: 2000
    },
    {
        BeginRecordNumber: 2001,
        EndRecordNumber: 4000
    },
    {
        BeginRecordNumber: 4001,
        EndRecordNumber: 6000
    },
    {
        BeginRecordNumber: 6001,
        EndRecordNumber: 8000
    }
   
)

For our test app, I simply put the above formula in the OnVisible for my testing screen. This creates a table I named colBSPLLoopValues that consists of 2 columns named BeginRecordNumber and EndRecordNumber and 4 rows with the relevant range values. You could add other relevant values to this table that might be used in the following collection formula for filtering.

ForAll loop to create BSPL collection

Clear(colBSPL);
ForAll(
    colBSPLLoopValues,
    Collect(
        colBSPL,
        Filter(
            '6000 Items List',
            RecordNumber >= BeginRecordNumber && RecordNumber <= EndRecordNumber
        )
    )
)

And there’s how we use the colBSPLLoopValues table to create are BSPL. English translation for the code above:

First, we Clear(colBSPL) in a separate step, because ClearCollect within the loop wouldn’t make any sense and PowerApps won’t let you do it anyway.

Then we execute ForAll on the colBSPLLoopValues table. For each of the 4 rows in the loop table, we’re instructing PowerApps to Collect records from the ‘6000 Items List’ into the colBSPL table where the list’s RecordNumber value is greater than or equal to the BeginRecordNumber value in the table AND the RecordNumber is less than or equal to the EndRecordNumber. Collect then adds each chunk of 2000 records to the collection to bring the entire BSPL into the app.

Method Two Benefits and Limitations

This method may seem more complicated then the Method One options, because we’re breaking up the overall formula into 2 separate behavioral actions. But I think it simplifies the overall expression and gives you more flexibility by isolating the range formula from the Collection filter formula.

Method Two clearly can achieve identical results to Method One. I think it works particularly well when you’re working with a BSPL and don’t really need to collect all items all every time. Your goal should be to reduce the “working list” down to a manageable number of records in the app.

I’ve found great success in using date ranges in the loop table to define the scope of records I want to work with. You might create start month and end month values in the loop table. Or perhaps quarters. It kind of depends on how you’re filtering the BSPL in the next step. So long as each chunk of ranges resolves to fewer than 2000 records for each loop of your BSPL collection, it’ll work great. Don’t forget to make sure your filters are all delegable though!

Large List Collection Method Three

This is the one to use if you really must collect all items in a list, and you want to be able to dynamically generate the loop table based on whatever the total number of records are at the current moment. I’m not going to pretend take credit for this one! I’ve been using versions of this for a couple of years and can no longer find the source. If you know who came up with it, post a link in the comments and I’ll update! I will translate it to English though, which I haven’t seen anyone else really do.

First, the code, in it’s entirety and with comments.

/*
// --- PROCESS START ---
 
PRE-REQUISITE:
1. Set Data row limit for non-delegable queries to 2000
File > App Settings > Advanced Settings > Value: 2000
 
2. If using SharePoint, index your number column to avoid list view limitation.
*/
// Just In Case: Unload staging collections
Clear(colDummy);
Clear(colIterations);
Clear(colNumbersTable);
Clear(colBSPL);
//Pre-load the schema. Nothing else happens with this step, but it helps to eliminate errors if you transform the collection elsewhere in your app via DropColumns, ShowColumns, AddColumns, RenameColumns.
If(
    false,
    ClearCollect(
        colBSPL,
        '6000 Items List'
    )
);
/*
Begin Generate Series
This can retrieve ~ 2 million records
Increase the count if you need additional records to be retrieved (hopefully not)
*/
ClearCollect(
    colNumTemp,
    [
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10
    ]
);
ForAll(
    colNumTemp,
    ForAll(
        colNumTemp,
        ForAll(
            colNumTemp,
            Collect(
                colDummy,
                {dummy: 1}
            );
            Collect(
                colNumbersTable,
                {Number: CountRows(colDummy)}
            )
        )
    )
);
// End Generate Series
// Obtain first record, last record
UpdateContext(
    {
        FirstRecord: First(
            Sort(
                '6000 Items List',
                ID,
                Ascending
            )
        ).ID
    }
);
UpdateContext(
    {
        LastRecord: First(
            Sort(
                '6000 Items List',
                ID,
                Descending
            )
        ).ID
    }
);
// Begin iteration capture
UpdateContext(
    {
        Iterations: RoundUp(
            (LastRecord - FirstRecord) / 2000,
            0
        )
    }
);
// Create a new collection to temporarily store our first ID to capture
ClearCollect(
    varCounter,
    {min_Num: FirstRecord}
);
/*
Using a ForAll loop where column Number is less than or equal to the number of iterations
Update iteration collection with current iteration number, min, and max numbers
Update counter collection
 */
ForAll(
    Filter(
        colNumbersTable,
        Number <= Iterations
    ),
    Collect(
        colIterations,
        {
            Number: Last(
                FirstN(
                    colNumbersTable,
                    CountRows(colIterations) + 1
                )
            ).Number,
            min_Num: First(varCounter).min_Num,
            max_Num: First(varCounter).min_Num + 1999
        }
    );
    Patch(
        varCounter,
        First(varCounter),
        {
            min_Num: Last(
                FirstN(
                    colIterations,
                    CountRows(colIterations) + 1
                )
            ).max_Num + 1
        }
    )
);
// End iteration capture
/*
Finally, with a ForAll loop let's collect all our items
** Cannot use ID column to do delegations! **
** The ID here must be a Number Field **
*/
ForAll(
    colIterations,
    Collect(
        colBSPL,
        Filter(
            '6000 Items List',
            RecordNumber >= min_Num && RecordNumber <= max_Num
        )
    )
);
// Unload staging collections
//Clear(colDummy);
//Clear(colIterations);
//Clear(colNumbersTable);
//Clear(varCounter);
//Clear(colNumTemp);
// --- PROCESS END ---

Ok, let’s break this down. I’m referencing the line numbers above.

Line 16: Presumably, you’re going to want to manipulate this data somehow. Once you have a big app with possibly several different collections based on the source schema of your BSPL, you might run into schema issues if you transform the collection. This fixes that. I think it sort of initializes the schema in PowerApps. I pretty much insert this into all my working collections now.

Line 28: colNumTemp…Pretty straightforward. This creates a table with 10 sequential numbers that we’ll use to dynamically generate our tables.

Line 43: The magic starts happening. The 3 nested ForAlls are kind of like exponents to multiply our colNumTemp table by itself. This creates the colDummy table that will result in a table with 1000 rows, each with a value of 1. ForAll (or for each) of the 10 rows in the colNumTemp table, we execute ForAll on the 10 rows of colNumTemp, and then we execute ForAll on the 10 rows of colNumTemp one more time. 10 x 10 x 10=1000!

Line 53: Right after we create each colDummy record, we Collect a record into the colNumbers Table that’s a function of the current number of rows in the colDummy table via CountRows. This results in a table with 1000 sequential numbers from 1 to 1000. Pretty cool, right?

Lines 62-96: Now we’ll start generating the variables that will be used in our function to create the iteration, or loop, table. We identify the first record in the table, the last record in the table, and then we do the math to determine the number of iterations/loops that we’ll need based on the size of our BSPL. We also store the ID of the first item that we’re going to capture to start the loop.

Line 103: We’re finally ready to create our loop table! First we define the number of records we’ll execute the ForAll on by filtering the 1000 record colNumbersTable to less than or equal to the number of iterations we calculated earlier.

Line 108: For each of the colNumbersTable records, we will collect a range record to colIterations that we’ll use to filter our BSPL. The colIterations table consists of 3 columns: a Number column used to iterated the values in the loop, min_num column for our range, and min_max column for our range. Each instance of the loop calculates the column values dynamically.

Line 111: The number field for the current record is calculated by essentially adding 1 to the row count of the table being collected, colIterations. min_num and max_num are calculated from the varCounter table created earlier where we initially stored the first record for the range. varCounter is updated with every loop via…

Line 121: the Patch function! This simply updates the varCounter table with the new computed values for min_num based on the record that was just collected. This is the last step of the colIterations loop before it restart.

Line 141: We’ve dynamically defined our loop range table above, and now we’re ready to Collect the BSPL! This step is essentially identical to what we covered in Method 2.

Method 3 Benefits and Limitations

And we’re done! The above loop will complete however many times have been defined by the size of our BSPL up to 2 million records. (1000 colNumbersTable rows x 2000 BSPL records). You could increase this limit by simply adding more sequential numbers to our colNumbersTable collection in the first step.

The obvious benefit to this code is that it is theoretically set and forget. Unless your business needs necessitate changes to the final filter formula, you’ll never have to update this code as your BSPL grows. It also can execute almost as quickly as Methods 1 and 2.

I’ve used Method 3 to collect tables with well over 100k records. I’ll caution that the more columns you have the slower it takes, though. And remember the warning about LookUp columns in SharePoint? I’ve never conducted rigorous testing, but I can assure you that ALL SharePoint query formulas feel more painful with lots of LookUp columns. Essentially, for each record in a SharePoint List that contains a LookUp, Sharepoint queries the entire LookUp datasource! Multiply that across many columns, and multiply again by thousands of records, and you can imagine the processing overhead. Here again, you probably want to use the ShowColumns function before the Filter when the colBSPL collection is created in order to limit the query to only relevant data.

And that’s it! Hit the comments with any questions. In another post, I’ll show you how to selectively update your colBSPL table by checking the source SP list for new or modified records since and updating the colBSPL for just those values. This will allow you to avoid re-collecting the entire list multiple times in the app.