Using Large Excel files in Power Apps

We all know that Excel isn’t a good place to store your Power Apps data in. Still way to often developers use large Excel files for their Power apps data. But why is Excel not a good data source? Why do people still use it? And why are Excel files supported as datasources in Power apps? What are the limitations that we need to consider?

Excel data in Power Apps

Earlier this week I was asked about accessing data in Large Excel files from within Power Apps.

Now I’ve never been a fan of Excel as a data source. Excel as a data destination (aka reports) is fine but storing large amounts of data in Excel and treating Excel as a database is not a good idea.

There are a few different scenarios that we need to consider. Imagine I have created myself a Large Excel file with 500000 rows.

Yes, this is a great example of poor data in Excel. Now When I try to use this file in Power Apps as a datasource I will get the following error message. The Excel Connector simply doesn’t support larger Excel files with unique ID columns

Now, if I try and do the same with an Excel file that contains just less than 64000 rows the import will succeed.

Adding more than 64000 rows to an Excel file

Now if we add more than 64000 rows to an Excel file using a Power App, then Power Apps will happily do this for us.

Importing the tables with generated ids or a column

In the above examples I got Power Apps to generate me the unique ID column. This will be useful in some cases, but if the data already has a unique column anyway then we could use this column as the key instead.

The huge benefit of course is that we can now import more than 64000 records without any problem.

And even my table with 500000 records now loads without any problems in my app. However if we start the query the data in any kind of way, then we find quite quickly that delegation issues will arise. As shown inn the below example simple queries will already show delegation warnings.

Now if you are still not convinced by the issues that Excel will give you when used as a datasource in Power Apps. You could indeed use a flow in Power Automate to collect a record for you

For this last option you may need to enable pagination option on the Excel action. Otherwise you will receive 256 records only.

Now in general I wouldn’t recommend loading 1000s of records into an app anyway. There will be better techniques available. Quite often I see solutions needing all records only because of a need to count all records or to implement a sort of the records. When you want sort or filter in your app, you could of course get a flow to run the queries for you however that would mean that the user has to wait for a flow to run to collect records form that Excel file. This is not a good idea if UX is important at all.

So what are the alternatives?

Most commonly I see people use one of the following:

• SharePoint lists (Cheap and avoiding premium Power Platform licences)
• Dataverse (Part of the Power Platform and a stable database option)
• SQL (ideally in Azure)

I’m including SharePoint here and although SharePoint lists are better than Excel, Dataverse and SQL should be the preferred option when picking a data source for your new app.