Demonstrating Delegation Issues with Excel

Our data source starts as an Excel spreadsheet with links from my Music Genius application for iOS, MacOs, Google Play, and Windows Store.² Figure 11-1 shows the first 16 rows of the 3522 in the file.

A table of 4 columns and 16 rows. The column headers are group, song, album, and link.

We ensure our data is an Excel table³ and then upload this file to a SharePoint document library. We then use the Excel Online (Business) data source , as shown in Figure 11-2.

A screenshot of a data tab. The tab lists options such as add data with select a data source option. A cursor points to excel online and is highlighted.

Since there is no index column in the table, the Excel Online connector will create one for us. But we are not able to see it programmatically and thus can’t use it for our “getting around delegation” solution. But we could have added one and used the same solution as we will show later for SharePoint.

Figure 11-3 shows when there are no delegation issues.

A screenshot of an excel data source with no delegation issues. The source lists the names of 3 songs with the album, artist, and link.

Figure 11-4 shows the delegation warning.

A screenshot of an excel data source with delegation issues. A cursor points to the delegation warning and the number of items, 500 is highlighted.

Note that the Number of items changes to 500, which is the default Data row limit, as shown in Figure 11-5.

A screenshot of a settings panel. The options listed for the general option under settings are icon, background fill, icon fill, auto-save, and row limit.

We can increase this value to 2000, but we are stuck beyond that. Power Apps goes ahead and grabs that number of records, but all filtering occurs with just those 500 or 2000 records.

Demonstrating Delegation Issues with SharePoint

Before we copy our spreadsheet into a SharePoint list and demonstrate the delegation issue, we skip forward toward our solution⁴ by noting that we need a numeric column other than the SharePoint list’s Id column in order to read our data source and load it into a collection. For that, we will need a Power Automate flow to run each time we create a SharePoint list item. We might as well create that first so we get this ID_Numeric column to load for each row we paste in.

We create the MusicGeniusLinks list with the columns to match the Excel spreadsheet (Group, Song, Album, Link) as well as the new ID_Numeric column. As shown in Figure 11-6, we create an automated cloud flow with the trigger of when an item is created.

A screenshot of build an automated cloud flow tab. The tab has a flow name, a checkbox for when an item is created, and a create option at the bottom.

We choose the site address and the name of our list, add a step, and then search for SharePoint actions (Figure 11-7).

A screenshot of adjacent tabs. The tab titled when an item is created points to choose an operation box. A cursor points to the SharePoint option under operation.

We search for Update so we can find the Update item action (Figure 11-8).

A screenshot of the SharePoint tab. A cursor points to the update item option under actions.

A screenshot of the update item tab. A cursor points to I D under dynamic content on the right. Arrows from I D point to I D and I D numeric of the item tab.

We need to give values for each required column (shown by the * to the left of the column name). We choose ID for both the Id column⁵ AND the new ID_Numeric column.

Now that we have our flow set up, it is time to import our data. As shown in Figure 11-10, I like to copy the Excel rows and then go to grid view in SharePoint and then select the columns I want to paste.⁶

A screenshot of a tab with options and the new option highlighted. A cursor points to a box at the bottom to add a new item.

We then paste via Ctrl + V. This ends up taking quite a bit of time as our flow has to run for each of our 3522 rows. We can go back to Power Automate and check our run history to make sure all is working correctly (Figure 11-11).

A screenshot of a tab of a 28-day run history. The history has 3 columns and 7 rows. The column headers are the start, duration, and status.

We use the button because there are other times⁷ where we also want to reload the collection, such as when we click on a “Refresh” button and after we add, edit, or delete records from our data source.

A screenshot of Share Point data source issues. The Screens option under Tree view on the left is highlighted. On the right is a load collection button.

We start with some comments and then Refresh our data source. This is needed in case we or another user recently added, edited, or deleted a record. We then clear all the variables/collections that we use in the code to come. linksCollection is our final output and what we will use in our filter. Since it is a collection internal to Power Apps, it does not have the same delegation restrictions as our Excel or SharePoint data sources.⁸

This is basically an array of ten cells that will hold sets of records. We can go to File ➤ Collections in Power Apps to see a preview of how it looks (Figure 11-13).

A screenshot of a collections panel. A cursor points to the col Num temp option under collections. col Num temp has values from 1 to 5 on the right.

This operates on the colNumTemp collection that we created earlier. For each value (1, 2, 3, etc.), it creates a colDummy collection, with a single item named dummy and a value of 1. Figure 11-14 shows how this looks in the preview mode.

A screenshot of the collections panel. col Dummy option under collections is highlighted. On the right, the dummy lists 5 items marked by 1.

This adds items to the colNumbersTable collection and creates a Number with a value of the number of rows in the colDummy collection (1 then 2 then 3, etc.). Figure 11-15 shows the result.

A screenshot of a collections panel. col Numberstable option under collections is highlighted. colNumberstable has numbers from 1 to 5 on the right.

The inside loop runs ten more times, giving us 100 items in both colDummy and colNumbers. The outside loop runs ten times as well, leading to 1000 items in both collections.

Looking first at the FirstRecord variable , the Sort() function takes three parameters: the table, the formula, and an optional sort order (Ascending or Descending). Power Apps again delegates this operation to the data source (SharePoint in this case), but luckily SharePoint doesn’t impose the 500- or 2000-item cap in this case. Since we want the first record, we sort Ascending. Our formula is to do our sorting by the ID column. We then use the First() function to grab the first item. For LastRecord, we just change the sort to Descending, which puts the highest ID first.¹⁰

In our case, LastRecord.ID = 3522 and FirstRecord.ID = 1. This gives us 7.04 but we round up to 8 (with no decimal places).¹¹ It is important that the divisor (500 here) either match or is smaller than the Data row limit (see Figure 11-5). Otherwise, delegation limits will kick in again.¹² Figure 11-16 shows how we can see these variable values within the Power Apps environment.

A screenshot of a variables panel. Use the collection to avoid delegation under the variables highlighted. On the right are options to select a variable.

Our last variable, varCounter, stores which ID we are on. We name its value min_Num and set it to FirstRecord.ID. We can already guess that we will loop through sets of records and update this variable to keep track of where we are.

Working from the inside, the FirstN() function takes a table as the first parameter and the number of records to return as the second parameter. As we saw earlier, colNumbersTable has eight items in our case. colIterations starts with 0 rows and adds a row each time. So we read one record the first time, two the second time, and so forth. Since we get the Last() record and then grab its Number value, this will be 1, 2, 3, etc.

For min_Num, we refer back to Listing 11-4 to note that varCounter.min_Num means the ID of our FirstRecord variable. So this will be 1 the first time around. To get max_Num, we add 499 to it. It is important that this is one less than the value we used in defining our Iterations variable in Listing 11-4. That was 500 in our case.

The Patch() function is used to update data sources, but that can be a collection as well. It has three parameters: the data source, which record to update, and the value(s) to be changed. varCounter is the data source and its first record is what to update. Since our formula for min_Num parallels that for the colIterations collection, we can refer to Figure 11-17 for insight into the values.

A screenshot of a collections panel. The collterations option under collections is highlighted. On the right, collterations has num, max n, and min n table.

The first time around, we are in the first row and max_Num will be 500. varCounter.min_Num will equal that value + 1 (e.g., 501).

The ForAll() loop will start again and jump to populate the next row of colIterations with the first five items showing in Figure 11-17.

The Final Result

Figure 11-18 shows our completed app. Since our Excel file was sorted by Group, we test by searching for ZZ Top and notice that we are retrieving items 3478 and 3479, way above even the maximum 2000 record limit if we were querying SharePoint directly. Whew!

A screenshot of the SharePoint data source issues. I Ds 3478 and 3479 of song slash album are highlighted. Load collection button is on the right.

Summary

In this chapter, we learned how to use Power App collections and methods like Collect(), ClearCollect(), ForAll(), and Patch() to avoid the dreaded delegation issues with SharePoint or Excel data sources. While there is a bit of code, you will be able to breathe a sigh of relief once you have this nailed down. Otherwise, what looked to be the perfect solution to a key business problem instead could fail once you exceeded 2000 records. Once you have this technique at your disposal, you can then look closely at your data to decide when it makes sense to add this extra step to your applications.