Using Excel Online to search values and display in power app

[Dina]

Hi community, 

I am building an app that will enable users to search for existing values before submitting a new request avoiding duplication. 

The data is being provided by excel and saved in a SPO site. 

Current issues: 

- Too many rows on my excel doc (over 355000) .. the search comes up but doesn't offer all the values as it should. 

- Every time that report is replaced, the power app can't find the records anymore. Even after saving the table with the same formating and name. I have to go to the app, remove the online file and add it back again. 

- Can't import excel to a SPO list to use the list for my data search functionality or export from excel to a list.. getting error messages about the volume. 

Currently trying :

Flow to get items on my excel doc and create a list in SPO. 

I am not a developer (got help from MS support trying these things..  this doesn't feel like low code, no code at all.. 😞 )

Have not tried dataverse for Teams, but the app users are not part of any team, and do not have premium licenses. 

Anyone has any recommendations.. Desperately looking for a solution. Thank you!

(i 

[BCLS776]

Would it work for your use case to load all the Excel data into a Sharepoint list and work with it from there. You could build a Power Automate flow to add/update records in the list whenever you have new Excel data, and Sharepoint can handle more rows than Excel. As well, once you connect the list to your app, you don't have to reconnect it whenever the data changes.

Bryan

[Dina · Accepted solution]

Hi Bryan, thanks for your feedback. 

I've tried that yesterday. Ran into the flow limitation and could only transfer 50176 rows from the excel document. I am adjusting the limit for excel in my flow to 100 000 which is the max.. and see if that will be enough to transfer all the rows to the list.. It will run for hours.. 😞  

[BCLS776]

I hear ya on the slow data transfer from Excel. Remember these words: "Excel is not a database" 🙂

[WarrenBelz]

@Dina ,

Please heed @BCLS776 's advice - you will run into a lot more issues than that if you want to use Excel for anything more than 2,000 rows. You might want to read my blog on this. 
Shane Young @Shanescows also has an opinion. 

[Dina]

Thanks for your help. I’ve reviewed those videos and more! Unfortunately that’s how o get the data so can’t simply say I don’t want excel data.. This is how I solved it.

Create a flow to move content to SPO List but had to run the flow multiple times to accommodate 75000 rows each time it ran. Cleaned my excel each time to run again without duplicating values. Once I had over 300 000 values it ran a good 4 times over the weekend but finally have it all moved. 
now I am running a flow that will look for updates on the excel and keep that list always current.

Solved