How To Import Excel to Dataverse using Dataflow

hey everyone this is daniel and in today's video i'm going to talk about how to import excel to dataverse using dataflow and i'll walk you through an example where i have a nice poc app that i had built whose tables were in an excel spreadsheet sitting in one drive but now i'm ready to move it to production so how do i move that excel spreadsheet and its tables over to the tables in dataverse so that's what i'm going to show you as an example and i'll share some tips and tricks along the way so that's the plan stick around but first here's my intro video [Music] [Applause] so let's get started and here's the scenario i talked about what i am here in my powerapps canvas app is this proof of concept app that i had built and its data which are these excel spreadsheets there's actually an excel spreadsheet that is sitting in my onedrive and there you go that's the excel spreadsheet now i'm ready to move this over to databurst i'm going to use dataflows for that so let's get started what i'm going to first do is go into my powerapps and i'll actually open up another tab i'll go to powerapps and in there i'm going to now go to data flows to do that i go to dataverse and here's the little trick because you go ahead and see data flows as well let me go to data flows and we go and start the process there but it's actually easier to do that from the table size specifically the get data so i'm going to go to tables and in tables i'm going to click on data and i'm going to click on get data now in get data you've got two options so you might think that daniel i'm getting excel right so i'm going to go and click on this get data from excel but don't actually click on the one on the top which is get data and you click on the get data and now you are presented with these amazing options and voila right there on the top left you see excel workbook that's the easy way to do it now once i'm over here i need to go ahead and browse to my onedrive alright so i'm going to click on that and once i browse to my onedrive i go ahead and get the option to now select the excel spreadsheet location because it's presenting my onedrive right over here so let me just try to remember out of memory i said okay it was in powerapps and powerapps yeah that's the one so i'm going to select that spreadsheet and then once you've selected it you go and click on the select button and you do nothing else you basically go and click on next and here you are presented with these excel workbook now here's where it gets a little confusing because you will be thinking is that daniel i'm pretty sure my excel spreadsheet just had two little sheets on it and just to prove a point i'll actually go to my spreadsheet i'm gonna go and click on it and once i click on it you will see that that's all i have is two like there's no left button or right option that's all i have right so i'm going to close this so i don't get any errors but the confusion sometimes happens is where did these two extra come from devices two manufacturers one well that's kind of the how excel works is at some point when i was working with that spreadsheet um i might have renamed something or i might have gone ahead and added or delete something something happened and excel went ahead and remembered all of that somehow in its magical history process but it's also displaying it over here what i know from my viewing purposes is that i just have these two it's called devices and manufacturers so i'm just going to select those two and a great way to do it you know verify is that once i've selected it it'll actually give me a quick preview of what it is and so right now i know that hey these this is my manufacturers those columns seem right and then just to verify i'll click on devices when i click on devices it shows me what it is and those columns look correct so that's just a little tidbit that i'm showing over here is that don't get too confused with these unknown worksheets that show up select the one that you are 100 sure of and then you can also go and verify so now i'm going to click on next when i click on next this is where i'm going to do a little bit of magic now as i go ahead and see my spreadsheets just to verify everything is good manufacturers look great i have an id column title everything looks great when i go to my devices in my devices as i'm just reviewing my data i see that i have a manufacturer's id column over here and i was just thinking like man it was it would be so convenient if i actually had the manufacturer names right here as well but thanks to my power query technique over here this this this little section right here in power query provides you so much more flexibility so i'm going to show you a trick on how you can go ahead and now add your manufacturer column where you can actually get the manufacturer names so to do that we're going to go on the top right where it says merge queries and i'm going to click on merge queries i'm going to say merge queries so i select that and now it is able to give me the option to build my relationships so what i've done on the top right is i've already got the manufacturer id next i'm going to go ahead and do a write merge from my manufacturers and also helps me you know gives me it just makes it very user intuitive but it says that hey don't basis that this the one that you've got on the top is the same thing as current so don't go and accidentally select that i know that the top one is devices i'm going to go and select manufacturers now i got a relationship over here manufacturer id is the same thing as the manufacturer id here because that's how my spreadsheet was built my excel spreadsheet i had the manufacturer id on the top for devices i have the manufacturing on the bottom for for manufacturers so i've got a really good relationships and you know kind of the icing on the top of the cake is that the selection matches of 101 101 for the first table it's giving me a nice green check which means everything is good then i'm going to go and scroll down a little and i'm just going to say join kind left outer leave it at that default for myself and i'm going to go and click on out okay and then now it's gone ahead and build that relationship and it drops a column all the way to the right but that's all it says it says table so to do get me the exactly manufacturer names i'm going to go and click on this little icon on the top right and once i click on it it tells me that hey in your relationships these are all the list type you can get the data and all i want to do is i'm going to select unlock i'm just going to get the title so i'm going to click on ok went ahead and created i mean it's kind of going and just showing me the data say hey that looks great the column name is called manufacturers.titles i'll go ahead and change that easy way double click on it go to the right and i'll just get the manufacturers and now just for my own personal convenience i'm going to click on it stay clicked and i'm going to drag it over to the left and as i drag over to the left i'm going to make sure i just drop it right after the manufacturer's id so it's moving it's moving i'm going ahead and moving it to the left and very soon i'll get there and i'm going to make sure i drop it after the manufacturer there it is and drop it on the right neat thing is that in the query settings it is actually keeping track of everything that i did so that's also neat in case i need to revert back and do all those jazz it all just gives me the functionality to even delete them and move up but what i'm going to do is i'm just going to scroll down and just do a quick test because i know that say inspiron is a model that is given by dell yep it showed up perfectly inspiring is over there you know and then i also know that the elite books are hp so this is just the way i can verify that my id ones are actually correct just did a little quick you know spot checking but that's it now i go ahead and click on next and once i click on next it's just going to give me another place to verify and it's going to give me manufacturers and when you're reviewing this don't forget to go ahead and check you know click on the one on the bottom as well because that's just the way that you want to verify now i am importing it as a fresh table right so i'm going to leave it as load to new table but here's something i'm going to change i know that i already have a manufacturer's id so instead of me selecting auto-generated i'm going to go ahead and actually say nope i'm going to select manufacturer id i'm going to do the exact same thing for devices as well devices on the top left i'll leave it to load as a new table i like the name so i'm going to keep the table name as is but on the top right for the column mapping auto generated i'm going to go and select that to device id and everything is good i just verify everything sticks all settings are good i don't need to change any of the column types because i'm kind of pretty sure this is what i need to do and now i'm just going to go and click on next and when i click on next it is going to just add you know give me another option that you want to refresh manually refresh automatically gives you all these things me is going to be a one-time sync so i'm just going to leave it as refresh manually and then what i personally do is on this public publish button on the top bottom right i just click on the drop down and i say publish publish now and i select that and the process starts now this is where sometimes it gets a little bit concerning is because something's happening and i don't know what it is but one way i can verify is i actually go to data flows and when i go to data flows this is where i can go and see all my data flow so it's actually showing a bunch of things that is going on but here's the good for me when you verify it's like hey this this is the one which actually started now and i'm the one who created it that's the one that's actually functioning so i'll actually now go ahead and let this finish um and again if you remember i had done a video you know not too long ago we will come back over here and we'll actually change the name because i want to make sure it makes sense to me so what we're going to do is while this is running i can quickly now go over my tables and i can just keep an eye on my tables that is getting generated so quick way for me to realize if i've created a table is to search in the type column and i look for custom so right now i have a custom column which i created i had created this for some reasons i'm going to go and ignore that um any other custom one yep i agreed a test one a while back but so far i don't think that my new tables has been created so i'll go back to data flows and in my data flows i guess i'll just have to wait over here and so this is the one that's in process right now it's publishing in process which means that data flow just the connection and all the query and everything it's getting published so once that gets published what we need to wait for is we'll let the publishing finish and then also let the refresh finish because the refresh is what's going to go ahead and get the data from that excel spreadsheet and move it over into our dataverse table that's what we need to wait for cool so it went ahead and now published it successfully as you can see it said it's successful there's a green checkbox over there and we've got a successful timestamp as well this in progress is where it's going ahead and doing a refresh the first refresh is where it actually now gets the data for us and that's what's working on right now so we'll just let that also finish okay awesome and that's also done now everything was successful we went there and created the tables we went and get the tables all of it looks successful what i'm going to do is i'm going to select syllabus ellipsis and i'm going to do a rename and in my rename i'm going to put a name that actually makes sense to me device app data flow let's rename it just so i can keep it over here cool it changed now at this point it is completely up to you you've gone ahead and done the data flow you've gone ahead and got the data whether you want to keep this data flow as is or you want to go and delete it it's completely at your discretion what i do is that i go and keep it over here for a few days because after i do the migration and the transition you know i just want to make sure that i still have this because there might be an option or there might be a situation where you know the excel spreadsheet which is sitting in my onedrive the one which is right over here somebody else might actually touch it and modify it and do something in that scenario i can actually go back over here to my data flows and i can go ahead and do a full run again a refresh and it'll get that delta data and in fact if i want to see how all of that is done i put a link to a video i did in the past of how we can go and get all that data as well watch that video if you want but again what i do is i'll just leave it over here for a few days just in case i want to move any delta data now we go back to our tables and our tables we should see all our data that's coming in and i usually just glance over here either on the new column the name column or on the type column and i'm seeing it i can see my manufacturers which is showing up this is new and on the devices i can also see this new table show up so we are in good shape so now let's work on the last piece of the puzzle which is going ahead and replacing the data connections in our app so i go back to my power apps and in my powerapps i'm going to you can do it either way you want you can go ahead and delete these two and you can go ahead and add the other ones or in this scenario i can actually go ahead and even add directly these ones now what i'm going to do again this is just my personal practice is that i had this app open while those new tables came through so i'll just go ahead and if i had to save something i'll save it but i'm going to intentionally go and close it this is just something that i do because um because the tables are already created and that connection is already there in the powerapps studio so just to make sure that i've got a fresh connection i'll come back in i'll go back and now i've closed the app i'm going to go back into the app and i'm going to click on edit and i'll go to my canvas studio and now it's going ahead and loading we're getting there and now i'm in that's when i'll go ahead and delete the excel spreadsheets and i'll put in our database tables so the app has loaded now i'm going to go and now replace the connections now one of the things i like is that i've got these specific names like manufacturers and devices but it's an excel connection so what i can do is now i can go and get the other connections from dataverse tables and even though they have the same names the two names remain it wouldn't be because i've got manufacturers in excel the other one automatically becomes manufacturers too it doesn't happen that way and i'll show you so i'll go ahead and just expand all the tables and i can actually search for there's my manufacturers already here in fact both are here so i'll just select devices and the moment i get that in you'll see the names remain as is it's just that because how dataverse behaves it automatically added a plural which is why there's two s's so i'll have to update my formulas but you get the idea so i'll get the manufacturer's one as well and that's right here and by the way the plural which is the additional s you could go ahead and replace that right when we were creating the connection it's just something i didn't think of so but but you do have that flexibility so i'll go ahead and remove this and obviously we have some errors so let's fix these errors the first one is the manufacturers so i'll actually put in this one and that will fix that piece we'll get on the other errors and we'll come back here and i want to go ahead and grab my gallery one gallery one i've got the ss so let me just add the ss and that takes care of that as well now it is giving me a little bit of delegation warning over here and that's because and what happened is the manufacturer's id one of them either on this side or that side we've got a text to number problems so let's go and see which column is that which is causing this problem so i'll go my tables and in my tables um let's go and review our devices columns that's the new table that's created so in my devices it's the specific one we're looking for is the device id so in my device id that is a custom number which is a text but it's the manufacturer's one manufacturers in my devices table the manufacturer's id column is a whole number which is perfect that's what we want so i'm guessing the the delegation problem is coming from the manufacturer's side so if i click on the manufacturer's site let's see the manufacturer's id ah the manufacturer's id has got a text not a problem we'll just go and tweak our formula so what i'll do is the manufacture id this manufacturer's id is coming from devices we knew that was a wholesale i mean that was a whole number so here i'll just go and update that as i'll put in a little value prefix the moment i do that delegation has gone away that little exclamation went away and we've got all the information let's go and see what this error is showing up this error ah because it's the manufacturers you know double ss so i took care of that and there you go everything is good as new which is double a couple check you know the filtering is working awesome if i just randomly select something i see the numbers are getting added up and i can go and click on that and it shows up over here so the app successfully migrated over because and all the formulas are now good because we went ahead and updated that thanks to us going ahead and replacing from excel to the database tables the app is now working wasn't that awesome how we successfully went ahead and did the migration from excel over to dataverse just keep one thing in mind is that you make sure that everything is happening in the same environment so when you're going you're wherever you're importing the tables keep that in the same environment as where your app is otherwise when you go to the app and you're trying to search for the data versus tables you won't find them over there because the app and the database tables are in two separate environments so that's a little important you know last minute uh information that i need to share but hopefully this was helpful to you and as always keep using powerapps and dataflows thank you so much for watching my youtube video remember this is all free with fresh content that is updated on a weekly basis so if you've already subscribed to my channel thank you and spread the word if you haven't already subscribe click on the bell notification and let the learning begin

Channel: Daniel Christian

Views: 21,111

Keywords: How To Import Excel to Dataverse using Dataflow, power apps, power platform, powerapps data flows, power query, dataverse power apps, dataverse tutorial, daniel christian powerapps, excel tutoring, Import Excel, powerapps tutorial, powerapps canvas tutorial, power apps ideas, power platform microsoft, power query editor, excel tables, onedrive tutorial, Dataverse table, dataverse powerapps, dataverse data flow, How to Import, power query clean up data

Id: 21ONiff4hxo

Length: 17min 3sec (1023 seconds)

Published: Mon Dec 13 2021