Blog

Excel - Reasons NOT to use Excel as a data source

Before you choose to Excel as a data source, you must read this.

The post highlights the problems that can arise when working with large Excel spreadsheets. For demonstration purposes, I'll use a sample Excel spreadsheet that I created with 30,000 records. This Excel file contains example Employee data.

Excel File Size Limitation

The first issue with PowerApps/OneDrive is that there's a file size limit. In my sample app, PowerApps wasn't able to load Excel spreadsheets greater than 2MB. Therefore, this imposes a natural limit on the number of records we can access in PowerApps.


It's also worth noting that PowerApps assigns a unique identifier to each row by adding a column called __PowerAppsId__. If your source spreadsheet is less than 2MB, connecting to the data source in PowerApps can increase the file size to greater than 2MB through the inclusion of this column.

To continue this experiment, I reduced the number of number of records to 25,000. This resulted in a file size of 1.7MB.

What is the maximum number of records you can show in a Gallery?

By default, the gallery control shows the first 500 records. The setting that controls this is the data row limit for non-delegable queries. The maximum permissible value is 2000.


If we increase this to 2000, the gallery control will display 2000 records.

What impact does this 2000 limit have on Gallerys and Search?

This limit prevents us from accessing rows above the limit. Here's a screenshot of the rows around record id 2000.


We can succesfully search for 'Kisha' - record 2000. But when we search for 'Misti' (record 2001), PowerApps does NOT return the recrod that we expect. As the screenshot beneath shows, a search for 'Misti' returns record 1535, but not record 2001.


Are there any functions to retrieve rows above record 2000?

Let's take a look at some of the typical functions that we can call in PowerApps. Two common functions are the Filter and LookUp functions. These both failed to return row 2001.

Filter(Employees, Name="Misti Bowen")
LookUp(Employees, Name="Misti Bowen").Name

Can we filter by numeric ID values ?

Can we use the Filter function to return records above row 2000? The first snag I encountered was that PowerApps interpreted the id column as a text column rather than a numeric column. This means that we can't filter with comparison operators.


After correcting this error by reformatting the column as 'Number' and removing/re-adding the data source, the Gallery control returned 0 records with the filter expression 'id > 2000'.



How about editing records?

Is it possible to edit record 2001? It's not possible to use the Patch function because this function requires us to supply the record to update. Since the LookUp function isn't able to retrieve record 2001, we can't use this to supply the record to patch. Note that the syntax below falied to work.

Patch(Employees,{id:2001}, {Name:"Misti Bowen Updated"})
Similarly, the UpdateIf function also failed to update the record.

UpdateIf(Employees, id=2001, {Name:"Misti Bowen Updated"})

How about Aggregate functions?

As you probably expect by now, Aggregate functions can only operate on the first 2000 records. This function returns 2000, rather than 25,000.

CountRows(Employees)

This makes it impossible to produce accurate summaries of data (eg: Sum of salaries, Count of records with Surname='Smith' etc).

What work arounds are there?

The typical workaround with SharePoint data sources is to load all the records into a local collection. We do this in batches with the ForAll function. With Excel, this isn't a viable solution because Filter fuction isn't able to retrieve records above row 2001. Therefore, there is no realistic work-around and the best solution is to choose a data source other than Excel.

If you don't need to update the data source...

If you don't need to update your Excel data source, you can import it into your app via Insert > Data Sources > 'Add Static data to your app'. 


If we do choose to use this method, there is one thing to be aware of. The 'Add static data to your app' feature can only add a maximum of 15,000 records. The spreadsheet that I uploaded contained 25,000 records - a CountRows of the imported data shows only 15,000.


To work with static data that contains more than 15,000 rows, a workaround is to split the Excel file into batches of 15,000 records, and to import the data as multiple data sources (ie, Employees1 and Employees2). We can then write code to combine these data sources into a single local collection.

ClearCollect(EmployeesCombined,Employees1);
ClearCollect(EmployeesCombined,Employees2);
Note that we could also use the Concurrent function here to speed up the process.

Conclusions

Although Excel is a simple to use data source, it isn't suitable for large data sets. This is because it isn't possible to work with more than 2000 records. In these situations, the best approach is to use a different data source (eg SQL Server, SharePoint, CDS).
Previous
Next
Related posts