QTT - Power Apps Excel is a terrible data source 

Hello, Great Video. One thing to add: performance. I measured the time of writiing a single row of 3 data to Excel (There is great feature for performance analises - Monitor in PowerApps) . It usually takes 2-3 seconds to write a single record to Excel. In case of Dataverse for Teams it was only 200 miliseconds - Dataverse was about 10 to 15 times faster.

I experienced first hand issues myself using Excel as my data source. Thanks Shane for confirming Excel is a bad data source for production. Couldn't agree more.

I'm in the process of converting from Excel to SharePoint for my data source. It's mind-numbing and tedious but its underway. I'm getting a TON of delegation errors but working through each one. Thanks again for the advice.

Used to have excel as a read only data source for an app. The file needed to be updated daily by employees and excel is familiar to them. It all worked beautifully. Then there was a need for added functionality to the app, which was possible but required writing back to the excel file. Trouble. My solution was to have employees keep their process and have them update the excel file. A flow then triggers which updates a designated sharepoint list. The app then talks to sharepoint instead of excel. Employees happy because they get to keep working with excel. Power apps happy because it gets to work with sharepoint. I guess the lesson is that even though excel as a read-only datasource works for the moment doesn't mean that it will not be a limitation in the future.

Thanks a lot shane! So many people don‘t understand what excel really is - is it everything but not a datasource! I love your videos. I‘ve created so much apps with powerapps because or your videos!

I have been developing Power Apps for the last couple of years and never ever used Excel as a data source, exactly for the reasons you mention in this video. I have apps connected to Sharepoint lists and SQL Server only. I'm considering to start building apps in Teams with dataverse as it looks very promising as well. Thanks for your videos Shane, I have learnt lots from you.

102% of my apps uses SharePoint as a data source, only because I grew up with it, back in the SharePoint/InfoPath 2003 days. Lucky for me that I have never learned to use Excel properly :) THX for the tips......... and have a great day

Thanks a lot Shane. I started with excel 6 months back. And am a pro SharePoint list person now 😀

Another very useful video. Totally agree with you that Excel has big limitations. It is, like you say, great for learning Power Apps, but you run into issues if you use it for production apps. Could have saved me some heavy googling by watching this video first ;) Keep up the good work!

Shane thanks so much for sharing this video. Great tips. It breaks my heart as a beleiver of Excel but I got the point. I understand that Sharepoint also has limitations? I'see if you have a video about it. Also, I checked tour webiste to learn more. Thanks again.

Man, I have not finished watching your video and I am loving it.

Hi Shane, very nice to hear that after some videos working with excel. I think this is the reason why my app didnt work but I didnt know. I will see how easy is to send my excel file to share point. to be honest I hear something abt that in another chanel but I was so faithful to you....

Great video! Very nice ! Main reasons for not using Excel as a data source: 1st Excel cannot be hosted in SharePoint, it must be in OneDrive Business. 2nd The tables that you intend to bring to Power Apps need is formatted as Tables in excel. 3rd When sending data from Power Apps to Excel you cannot have it open. 4th If you work with a lot of lines in your data source you will have problems with delegation. #Brazil

Thanks a lot Shane, I've learned so much from your videos they helped so much. I understand your recommendation to use a SharePoint list, however I have data over 5k rows. How would you recommend overcoming that limitation?

@Shane Young While watching this video, I was reminded of an issue that comes up over and over to me and the answer I keep running into in the wild is to simply use concatenate, which seems more like a hack to the solution...let me explain. Often I am trying t get a "value" from some data structure or control that is naturally wrapped up in a record or table. I don't know how in PowerApps to simply get one field "value" out of that structure. Everyone I have asked says, "oh, just use concatenate". That solution doesn't digest well with me, because that's not what concatenate is for. My instinct is that MS has provided a way to get a single field or value from a record or table. For me, it's often a single row "record", so specifying which row is not necessary wither. Can you please help me with this? I am struggling every day to do this the "right way".

Great Video Shane! I have been watching your videos for a long time. Keep up the great work!

had few weeks ago a P-App talk with other Dept. and I was actually pretty much ''quoting you'' to avoid excel as source :D (.....tips from video helped), thanks

Thanks Shane for all the details you give in your videos. The way you present is so easy for beginners like me to understand. I am having one more issue with my Excel Database. The Table has a VB code for RefeshAll as soon as the main datasheet is updated. The datasheet is a list of documents with a last and next annual review date and a column with days remaining for the review. This works well but as soon as PowerApps connects with the table and creates its own ID column there, the automatic refreshing of data from the main sheet in the table stops. Is there a way around this? Please assist when you find the opportunity. Thanks.

Never never use excel as a datasource. I'm 100% agree with you

Thanks Shane. I am a beginner in PowerApps and have learned a lot from your videos. You said SharePoint, did you mean SharePoint list ? I heard from someone that speed performance is an issue when SharePoint list has over 2000 records. Is that true? Also can PowerApps read Excel in SharePoint?

I love Excel, it's basic, amazing, and powerful, like driving a manual car. However, since I've been following you on powerapp, my life has been taken to a new dimension, can never go back... Excel still offers that total freedom nevertheless, but the ability to do things with powerapp and sharepoint list is just too much that Excel could not compare

Hi Shane, thanks for your great videos, they are super informative. Question: which data source do you recommend I use in place of Excel? I've tried Sharepoint Lists but the functionality is a bit limited. I would like to run my Business from one large central database but I don't know which one. Access is cool but also outdated. Thanks!

Great video. Our team use a network shared Excel workbook to store data - not in onedrive, sharepoint or anywhere like that - just a networked shared folder - which I know is a bad thing but I'm not about to change that yet. It has about 70k rows in it. I have a list form that needs to "lookup" from it, based on list form user input, so I guess I'm going to need to do some VBA in the excel file to send/sync the data to another source which can serve as an intermediate data source for the Powerapps List form. What would you recommend to use as an intermediate data source that excel can write to, and Powerapps can read from - without suffering from delegation issues? I guess that could also serve as a "back-end" to the existing workbook when we decide to start migrating over properly.

Hi Shane, thanks again for this wonderful video. I was hoping you could help me if you have a video that shows how to attach and upload excel in power apps, save the rows of data in a collection, put the collection in a gallery (or others) for editing then have a button to patch it to sharepoint list.

Hello Shane! Great video! In your opinion, what is the best Data Source for Power Apss when dealing with big tables (+1M rows) ? Thanks a lot, Cheers, João Martins

Your videos are very fruitful. Learnt a lot from it. Great going. I have a question. I am building a app with read only data. There will be no patch by the app users (around 100 users). I get the data quarterly from another department in csv file so it’s easy for me to update on excel file in OneDrive. My question for read only data in Powerapps is excel Recommended?

I was listening to a podcast and one problem they had in the UK with tracking the Covid cases, the automation they used was saving to a XLS instead of the XLSx file. The difference was the number of lines that can be saved... oops, they had lost several names for tracking who got infected.

Hi Shane I really love your videos. I have learned a lot thanks to you. I have a question about Excel Data Source. I have to use three Excel files with three tables (One of them has almost 900 items). They going to be read-only. I put all data in three different SharePoint lists but I’m pretty sure that this data will change at least 2 times a month. So what could I do? I was thinking let Excel files on Onedrive and overwriting every time but I dont know if this could be a problem.

Thanks Shane, This very true and limitation to use Excel as data source, What can be the solution? Currently I have app with excel as data source and having more than 30 Columns and rows are increasing day by day..

Ahhhhhh! I made a comment a couple of weeks ago on one of your videos about this exact thing. Should my ears be burning? Hilarious though: In 10 minutes you have recapitulated my entire PowerApps database experience of the past year. When I started with PowerApps I'd never heard of SharePoint (now "Lists"), so Excel it was. So guys: Everything Shane says here (and probably elsewhere) is true. Take the effort to start out with something other than Excel. You'll just have to switch over later.

Hey Shane, great video. However......I imported a 777k-sized Excel into a Sharepoint list. Very basic text strings, 5 columns, no formulas, nada. Made it a SP data source. For whatever reason, I ran into the delegation errors just like Excel. I'm taking the delegation errors on 'Filter(ExcelTab1, "JoeBlow1" in Userid)'. Are the Sharepoint servers I'm working with.......possibly not tuned correctly for PowerApps, etc?

Hi Shane, I agree with you that we should use SP lists as database. I have many occasions that I have new data records (1000+) in Excel and need to update my SP list according to the new data (update or/and add records). Currently I make a program with VBA to do that using ADO. However it takes time to complete it (some cases take one hour+). Can you tell me any suggestion for that?

Thanks for the advice Shane. In your experience wich sources (excluding SharePoint lists) are good options?

Dear Shane, thank you very much for your excellent advice (sorry for my writing, I'm bad at English). I would very much appreciate the answer to the following question: What would be the options to connect to a Power BI Dataset?

Funny my use case is the one case you said is okay :). I need a drop list of employees that I will update from SAP once a month. Employee ID and name that's it. If I were to make it into sharepoint list that would be an extra step. I already have an excel add in that cleans the sap data and creates a table. I'll just be overwriting the file each month.

Your #1 problem isn't a problem... if you made an app to make it easier for "you" to enter/change data in "your" spreadsheet, it wouldn't make any sense to ever have them both open anyway... SP Lists are even more dangerous, because it's easy to delete/damage your data, with zero option to recover. But again, in any DB type tool, the underlying data should never be accessed directly regardless - in dedicated environments like SQL Server, the end user can't access the tables without special tools either.

Thank you, Shane! I learn a lot from your videos.

Thank you very much for this video ! it explains well why you should never ever use Excel as a data source !! Is it a good solution to update an Excel spreadsheet (with Power Auomate ?) from a Sharepoint List to import data in Excel (and update some charts) ?

Excellent video! I'm having a problem with my Power Apps and I don't know how to fix it, if I can. My App is a Requirements Apps, and it works based on two tables. One with the data of the request, and another with the data of the comments. A request can have several comments, so what I would be needing is that when I access a request (which has a certain ID number) and I want it to go through a "View comments" button, for For example, I raised all the comments of that requirement (Obviously related to that requirement ID), and that I can also add new comments from the App. My database is an excel in One Drive that contains these two tables. I'm going crazy to be able to achieve this. Does Power Apps allow this or does everything have to be done in a single table, without relating it when the source is an excel spreadsheet? Thanks friend, and congratulations on your great channel!

Another great video Shane, thanks a lot! In our company we have recently migrated all data from local servers to Teams/Sharepoint. That works fine however for some finance excelsheets we see that in Sharepoint excel macros, scripts, BI cubes do not work properly so we have to keep them in a file server in Azure still. I really would like to hep finance team for automating their processing by building some apps but since their excelsheet with macros etc do not work in Sharepoint I cannot use powerapps at least in the most efficient way. Would you have any recommandations on this? I would appreciate if you can make a videos on how to use macros, automation scripts in powerapps if we put those excel files in SharePoint . Thanks in advance!

My main issue with using SP lists is that you also have to let everyone have access to them on the site and you can’t hide them from users being able to get to the backend?!

Hi Shane , There are multiple individual excel tables and inputs on each are used to calculate summary sheets which have formula in place, I am able to feed inputs using power apps but the summary sheet isn’t auto calculating the formula unless I am manually clicking refresh connection. Is there a force refresh option or some excel tweak that I am missing out ? I tried using individual sharepoint lists but share point doesn’t allow lookup and calculate using other SP lists .. I am really running out of ideas here 🤦🏻

Hello Shane, again, I love your videos!! I need some help to keep the functionality/relationship of several worksheets that are connected and have macros. I can transfer every worksheet but I also need to reconnect them to keep the functionality of the macros. I hope this makes sense. Thank you Espie

Thanks Shane, I finished my app based on Excel, but I'm facing the problem that I should share this file as well and I don't want users can seen sensitive information on the spreadsheet. Question: May I solve this issue if I migrate my database to Share Point or SQL? Thanks for the advice. Keep well.

Thank you for this video!! Answered a number of questions I had. I still have two questions, is it possible to save data in Excel and in the SharePoint list at the same time? (Add two commands to OnSelect Button) And you said that if the Excel is opened then we can't write a new record to it. What about the Patch command? Will it solve this problem?

Hi Shane, thank you so much for the content. In my case, I constantly have to update my datasource, and these updates usually come in a large number of records. In this case, updating a sharepoint list is not ideal, since I haven't found a way to input many records at a time there. Is that possible? Or is there another alternative besides a sharepoint list? That's the only reason why I use Excel today... As I can simply paste new lines there at once :( But indeed it's really bad for the app, since I connected it to Excel I have a series of new random errors rs. I'll so appreciate if you can help! Best wishes!

Hello Shane, love your videos. In my quest to not use Excel as data source, i want to import excel data into a dataverse table. I think i can manually do so, but in the spirit of automation, i need to import a file as it hits a folder. So something like an excel file is dropped in a folder --> a flow is looking for new files --> pickup the new file and imports the data into dataverse --> TimeStamps the excel file itself and moves to another folder. Now the data in excel will be in dataverse table and ready to be used :) ... Thanks for any tips.

My issue is that our source data is locked up in SAP and we can not use premium connectors. We have to extract large number of rows into excel (10k to 100k+) to do work. How do I get this data from the excel extract into the likes to SP. SQL is also out of the question for the same reason as SAP.

thanks for this Video @Shane i have been using Excel online for the past 1 year & now i'm getting trouble with it. my data type is mostly, the camera photos & data entry or description associated with the Pictures. data size: 4-5 GB of data per yearly now, i'm planning to migrate the data from Excel & Sharepoint Documents to MS SQL/Sharepoint list can you recommend me which data-source is the best in my scenario. MS SQL or Sharepoint List ?

Great video Shane! How many records can be stored in a SharePoint list?

1. Only on OneDrive, not SharePoint, and table. 2. Locked data source and apps break ( open in Excel online, Excel Desktop, Power Automate, etc.) 3. Delegation not working( delegation command like "search" doesn't work.) 4. Can't change Datatype ( decided on import )

It's super unstable when connecting to excel. Even less than 500 records, I am having issues the records are not removed when I use the remove function.

Thank you so much I needed this , I have a question not related to this video , is there a way I can export excel sheet from SharePoint list but only the items selected not the whole list ? Thank you in advance

Hi Shane great video. Thanks! Just a query, Is it possible to upload an excel file to modify the dataverse table datas, so that giving the customers an option for bulk uploading data to their app?

Hi Shane, Firstly this was very helpful thanks. My query is I have data that I want to update everyday from another system and use it in my app as read only. Seems possible to get that data in excel file and use it here but issue is with deligation limit. Is there any way to update SharePoint list data everyday without manual activity? Flow takes very long time to do this.

Hello Shane, Please help me with some advice about the following data security/privacy concern: How do you avoid that the PowerApps users modify the data directly on the SharePoint list if it is your back end data source? The above taking into account the following: -I have to use SharePoint as data source due to several restrictions, so there is no other option so far. -As you know, when you shares a PowerApp you must grant access to the users to the back end SharePoint list. -If a "curious" user achieves to know where the PowerApp data is, they can go there and edit it, which is dangerous whether your solution is an approval app or you app contains confidential information. Please, share your advice about that. Thank you.

Thanks for changing the color of your wall.. it was dark and ugly..now is cool and warm...

Good one. Can you try a video with SAP connector?

Hey Shane, I have been getting both the formula, (even though I don't have any), and the lock, (even though I own the file and its not locked), errors so I am going to covert over to SharePoint as you suggested. Do you have any videos that walk through using SP lists as the data source, how to use an SP list to populate a drop down in an app etc?

Hi Shane thanks a lot for sharing all these knowledge with us. You are the best11

I'm trying to create an Inbound inspection sheet for a distribution center. Part of this would be trailer seal verification. What is the easiest way to verify an input? Shipping clerk would enter seal number from paperwork in datasource, then unloader would enter seal number in powerapps. Is there a way for this to have a warning if they don't match?

As of now (2023) the current connector does allow connecting to SharePoint files and multiple users, yeah! Does delegation still apply with the new connector? Is there a limit of rows/file size that the app can edit? Too many records for SharePoint, and no Dataverse (except via Teams - but are there row limits there too? and how to get the 100K row Excel file data into DV?) Thanks!

Hi Shane, can PowerApps data be updated from within the app when Excel is the data source? I'm putting together a pro and con list for using SharePoint lists vs. Excel as a data source.

Thank you for this video. You talked about using Excel as a read-only source. I would like to use Excel for the input of a drop-down list (read-only) but write the selections to another source. Is that possible? Perhaps you have a video on how to use one source for input and another source for output. If not... wouldn't that be a cool idea for a new video?!? :) (hint, hint)

Thanks Shane. I uploaded an Excel file on my canvas app and planning to use it to store the label translations on the different languages I have to provide, means that I am only using Excell file readonly. Do you see any concern on performance? Can you suggest me an alternative for translation?

Great Video, eplains all the problems I've been having! need to change my data source now. Thank You! If you're looking to add Team members, please reach out!

Thanks Shane! I am struggling to connect excel data to my share point list. Can yoiu please make a video on that?? So the problem is : I want to add the excel table as a share point table/list and then use it as a source for the power app drop down options. But I cannot add the sharepoint table/list like I add the excel table in powerapp. Please let me know what to do.

I am using excel as a datasource ,can simultaneously multiple users update excel (create ,update) through powerapps ?what is the number of concurrent users ?

Hi Shane, thanks for the video. In stead of share point I want to save it in business central. I am having trouble to save data from a collection to business central using a custom connector. the custom connector works fine when I test it (at the custom connectors section) and the Get function also works fine in the power app (I had to do some little tricks to make it work). I tried to use the following formula: Collect (businesscentralconnector_1.Post("'CRONUS NL'") ; NewItem). NewItem is a collection where I save my article data (just one record). businesscentralconnector_1.Post("'CRONUS NL'") is the custom connector with the post action. I want to save this NewItem collection to Business Central, so a new Item will be created in Business central. thanks in advance for helping me

Great Video, Great Advise. However I find myself needing to use Power Query tool in Excel to do datamining from old database servers. Guess that's read only and presents less of an issue.

Hola Shane, necesito ayuda con una proceso entre excel y prower automate. Necesito automatizar el arrastre de filas hacia abajo.

I do have a problem with excel , when the co owners open the app and wanna see uploaded photos in the forms , they can not :( they just see the Data , i dont know the reason , but it´s really frustating , I hope you can help me, I have already shared my file with the users :(

I have connected my PowerApp to Excel because I use PowerAutomate to hit an API and recreate the table every night. (I have an OfficeScript to delete all of the content and then recreate from the ParseJSON action). Is there a way to either create a SharePoint list and then check for changes every night, or better yet to connect to the API directly? I tried to create a custom connector but was unsuccessful. Any guidance would be greatly appreciated.

I have a question If I used sharepoint list it won’t work for me even though I Dont want to use excel and that’s because sharepoint list has limitation number

Thank you Shane for something to refer forum posts to that will actually (hopefully) convince them of this fact. Your facial expression says it all . . .

@shane. Great content as always. Here is the one space where I have found excel to be great for my students, and I would love for you to show me, for real, how wrong I am. In excel, if I name a could something like monkes[image] and point the power app builder to it, it will automatically assume it is an image field, plus create the requisite image folder there. Easy and no sweat. For my students who are just beginning, this is super easy. I can’t seem to be able to do the same with SharePoint lists. Can I ?

Hi Shane, Is it possible to use sharepoint as a data source and still keep the ability to save multiple images to a record by a reference table like the site inspection template (uses onedrive)?

I've been having issues with Excel recently with Power Automate, getting the data sometimes returns old data or even nothing at times, I'm having headache handling this reliability issue. It's frustrating cause I can't exactly debug what or why is this happening?

I have large data in SP(More than 5000 rows).. I want to pull that data in combo Box in Power Apps. Can you please help me out in this or give me some alternative for that

Thank you Shane

can you do a video on a series of dropdowns that eliminate the options as you go (ex. dropdown1 has 10 options, dropdown2 has 9 bc of the selection in dropdown1)

I'm having a hard time trying to solve a problem. I would like to ask someone in this community for help. I have an Excel sheet connected to my Powerapps Canvas Form and we use it as our data entry. The form has ComboBoxes and TextInput Fields. When we enter our data and submit, most of the relevant columns in excel are populated. Except some of the ComboBoxes are not. The problem is when I try to edit an existing case details, I would like to have the ComboBoxes filled by default in my PowerApps with the row data for its respective column in Excel. All the TextInput fields are populated, but the ComboBoxes are empty and not interacting with Excel at all. Please advise and share any thoughts and solutions to this. Many thanks,

Today I wanted to use an excel with a lot of formula as data source, and I learned that I can't. It was very disappointing :( I will watch the video when I go back home, but do you have any recommendation for me solving this problem?

What about using Flow as a middleware, and interact with Excel?

Hi Shane. But when a user has edge. He cannot import an excel sheet. It needs active x controls

hello Shane, I want to understand if powerapps can read and update information of an excel file present in sharepoint library instead of onedrive? is it possible to do so

I 100% Agree. However, I have end-users who are addicted to Excel and refuse to use anything else. So I built into the app where they upload an excel spreadsheet from the app and read into my SQL database the results. Clunky, but at least I don't have a bunch of addicts walking around the office screaming at me because they cannot use Excel. Sigh.

I've granted users access to the Lists my App uses, which they need to be able to use the app (duh). But I notice that as a result, they can now also browse to the List and modify the data willy nilly. Is there a way to allow them to run the app, but restrict their ability to browse to the actual data screen?

I’ll be honest I didn’t even know it was possible to use excel as a data source like this. I’ve only ever used excel to import static data into my apps (this seems to be completely okay??)

Lesson learned the hard way Shane. Good tip though; Sharepoint wins over excel every time.

How to show distinct values in Powerapps Choice Field?

Hi Shane.. can we lock fields to be edited

After Patch data Into excel data source, Data source not refresh immediately Even using refresh() function

Another good video

First thing I tried to do is add Sharepoint Excel as a data source. Nope! Won't be using Excel thanks!

This error issue locked something is what I'm experiencing right now.

Yes Excel is NOT a good data source at all! Although the import Excel as static data works great, but I don't think it technically stays as an Excel file when you do that.

Same issues with using google sheets?

😭 im always using excel as data source as our company use it most of the time.

hahaha the title made me laugh. Because this is so true.