QTT - Power Apps Excel is a terrible data source

in today's show we're going to talk about why excel is a terrible data source for powerapps that's right we're going to talk about negative stuff i know it sounds evil but excel is super popular in the world and a lot of people that use it as their first data source for power apps and they get frustrated because all these things happen so what i'm gonna do is i'm gonna show you some of those things we're gonna talk about why you shouldn't use it what you should use in its place and give you a couple attempts if you're really set on using it how to do it better but first here's our intro hi my name is shane young with powerapps911 those guys and in this quick thursday tip we're going to dive into why you shouldn't be using excel as a data source and i know that sounds almost backwards right like some of the early power apps documentation pointed you to excel as a data source as like your first app which made sense because we're all super familiar with excel but man it's got so many negatives that i just stick with a plain simple statement you should never ever use it as a data source okay so as part of this video i thought i should probably prove to you why show you some of the downsides and hit a couple other topics along the way so should be a fastball video but let's switch over to my desktop and just take a look the first thing i want to do over here is build us a really quick app right so if you go into powerapps go to create and you say excel online i know hundreds if not thousands i'm saying thousands if not millions of people have done exactly this they come in here and then as soon as this loads they're like all right cool it's going to use the onedrive for business connector right so to use excel there's your first challenge is that the file has to be on your onedrive for business no it can't be in sharepoint thousands of people have asked me no it has to be in your onedrive for business so i scroll down here and i'm like hey i want to use this charity one where is it right there charity contributions you select the file the second challenge is that your data has to be in a table and that table cannot have any formulas right there it just has to be straight data in all of those tables so i've built that simple sample and we're going to use gifs and so we're going to say connect right and these are showing me the tables i had defined in excel already so hit connect now as if magic after like 10 seconds powerapps has built us a app that lets us see the data right we'll hit preview up here there's all my excel rows and i can go in here like oh i want to add a new one so just add a new one how much was the amount you gave i don't know it was 32 the charity was chewie's kids i don't know just making up stuff here and the first name is um ferguson right the cat that doesn't want to give anything to it right so just fill in a couple rows there's no required data because excel doesn't have any concept to that and so if we say save it's like right the data goes out to our excel file everything is great we scroll down here somewhere we see the data right there and then if we were to go over to uh the onedrive right here and we were to find our file let me scroll down so then charity contributions right we want to see it in there so we open this up in our onedrive and we're like oh look ferguson's contribution right there yes why wouldn't you want to use this shane well i need the excel file open cool let's go back over here let's add another record so let's add one for 99 and we'll give this to a nobody because it's not going to go anywhere and then we're going to say casper the friendly ghost right we hit save this time we see the little ants marching that's what we call this little dots going across the top and the answer is going to keep marching and then we get an error ah what happened the requested source is unlocked this is the number one problem with excel is a data source is that if anyone has the excel file open whether it's in the browser or in the excel fit client it doesn't matter that file is locked now other excels can use that file because they have technology to share but powerapps can't get to the file so now my app is just broken right like it didn't save our data nothing happened because of that failure this is the number one reason you can't use excel as a data source right is that if anyone has the file open or anyone has a file locked for any reason and it might be in a power app that caused it to lock it might be the excel client that opened it or a lot of times we run into issues with power automate flows locking it if that excel file becomes locked for any reason your app doesn't work so that is the number one reason you can't use it um and there's just there's no getting around that now i say this i will tell you that some people like they're like hey and no one's ever going to use this app in that excel file great then i approve of you building your out there also if you're like well shane i'm just trying to learn i don't care awesome another great user reason to use excel but it i don't want production apps i don't want an app that you're going to share with your whole team being written in excel so where should you use well probably today the most common you know quote unquote free or included data source is sharepoint so go make yourself a sharepoint list with the same data as your excel table heck in sharepoint online you can import an excel file or excel table and it'll just make a sharepoint list for you you don't have to understand how it works it just happens and so then build your app because sharepoint doesn't have this locking issue all right so so the locking issue is the number one issue um another really common problem that you guys probably you know especially if you're watching this and learning but so everyone knows another thing is that powerapps does something we call delegation and so delegation is the ability to have the server process right so i i have a sharepoint list with 25 000 items in it and if i say filter out all the items where shane is awesome it's like two items in the whole list but those two items you know we send the query over to sharepoint sharepoint processes it on the server and then just sends back those two items and they show up in powerapps excel was a data source does not have any form of delegation so all your functions like search filter you know all those data type of queries they are beholden to the delegation limit which means by default if you go over here to file and settings it can only get the first 500 rows if your excel file has 600 rows in it only the first 500 get processed so those last 100 they just don't get queried just they're ignored they're not there now you know like with shane i can increase this you're right this can go to a maximum of two thousand well if your excel file has two thousand and one record you get the idea right so that's another problem delegation does not work and if you don't believe me because you don't get any warnings like saying you're dumb you're not it's not true then i challenge you set this to one right so say hey if something's not delayed we'll only get one record come back over here right hit refresh what are we going to see instead of this whole list we see one thing because this gallery is trying to delegate this search command and so it's not deliverable so it's getting the first record instead so always a good way to prove that delegation is your problem also remember if you are going to use um an excel file in your onedrive for business you have to share that file with everyone that you want to use the powerapp and edit that data so just keep that in mind now there is one other um good use of excel though so my friends that i kind of bounced these ideas off of told me to remind you guys that sometimes we do see customers use excel data as like read only so maybe you want to have like a you have an excel employee list you know and you want to just pull it in so you can have that populated drop down or something inside your app that's okay because you're just reading the data so you're not going to cause any locking problems but honestly if you were going to do that i would say just take that excel list turn it into a sharepoint list and just don't even sweat it i will not let my consultants build apps with excel as a data source if you are going to use it though a couple other things to think about don't have column spaces in your column names that's a real big problem for a lot of people technically they're loud they work but they just add an extra layer of confusion so if you're building one like cell because you're trying to learn avoid spaces in your column names all the data must be in a table like i already told you and also remember that your cells cannot have formulas all the excel uh fields here are literally just data right they could be numbers and be dates all that type of stuff which reminds me another challenge is that when powerapps talks to excel for the first time it decides what the format of this column is and then you're just stuck with whatever it is so hopefully in mind let's go see you know if i go in here so if i hover here right it tells me first name came over as text amount oh the amounts came over as text so i can't use them as numbers without wrapping them in a value function first because excel told powerapps that they were not numbers but they were text so that type of confusion cause is issues right the same thing let's see what the date column is i think this one's broken also oh so the date column did come over as the date time so that's good but that's another common issue we run into there so okay that's what i wanted to share with you guys don't use excel for production that's what i uh ask of you um but if you're thinking about shane i want to do you know whatever right i feel like 120 powerapps videos out here that'll help you guys right so there's plenty of things to learn over at training.powerapps911.com we have a free intro class over there that i'm actually adding new content to today um that has all types of additional things to start teaching you guys to go down the path of using real data sources like sharepoint or as you start to even mature further using things like dataverse um cds or cds sql server databricks for teams right there's a lot of other ones of those out there as well so if you have any questions comments other feedback other tips you want to pass to people leave comments below i respond to every single one of them sometimes it takes me a week or two um but i try to get all those comments and i try to like help people with that also remember hit me a subscribe button hit the like you know we have lots of this content i put out two videos a week of this type of stuff for you guys one quick one like this one deep dive there's a lot of information i am passing on to the world for free via youtube consume it so all right with that i'm gonna say thanks and have a great day before you go be sure to click on the subscribe button over here so that way you'll be notified when new videos come out if you need any help or you want to work together whether your problem is big or small check us out at powerapps911 we do it all i rhymed or if you're looking for more formal training offerings we have those linked up here somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 15,067

Rating: 4.9741936 out of 5

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps excel, powerapps excel data source, powerapps excel connector, powerapps excel online, powerapps excel error, powerapps excel onedrive, power apps excel data source, powerapps excel locking, powerapps excel resource is locked, powerapps tutorial, microsoft powerapps tutorial video

Id: RtmZM3tm2Zs

Length: 10min 47sec (647 seconds)

Published: Thu Jan 21 2021

Reddit Comments

[u/Rumblepuff]

And yet so many times in so many of his videos he uses Excel as the data source. Seriously though his videos are great.

[u/tj15241]

Thanks to Shane I built a app to track inventory in a warehouse using power apps and share point. Great videos.

[u/anonmonty024]

What’s the ideal data source?

[u/Venegoto94]

Really good video