How to Turn Your Excel Spreadsheets into Power Apps

let's take a look at how you can build powerapps from your Excel spreadsheets do you have a mission critical spreadsheet in your organization chances are you do or you've been using Excel as a data source we've all been there I'm going to take you through the brand new feature that allows you to do this in a couple of clicks and I am genuinely very excited about this however there are also some very real limitations and so I'm going to take you through when this is a good idea and when you might need to do something a little bit more advanced and how to do that as well so what we do here is we start with data and then we have this option here that says upload an Excel file all I need to do is upload or drag my Excel file in here let's take a look at the Excel file that I'm working with first so what I've got here is a list of contract renewals so I've got customers that I'm working with with some kind of service agreement and a start date and an end date and I want to get that somewhere out of excel you'll notice here that I've got different types of columns so I've got some things that are working as ID numbers I've got names emails phone numbers all fake data by the way start date renewal date and some things that have types and categories that are similar things that you'd think of in a drop down list and something that is an amount of money as well now I'm going to close that before I start and then we're going to go ahead and select that file and what's going to happen now is it's going to take the shape of my Excel data and turn it into a table in Microsoft database now it will only work with one sheet it will only create one table so if you upload a spreadsheet that has multiple tabs multiple workbooks and things in there then you'll only get the first one you also don't need to have this formatted in a table format in Excel I've just clicked it and dragged it from what you saw there there was no fancy formatting or table formatting or anything on that and here we have it now what this has done is given us the table columns in the shape of what it's seen in my data in Excel so the contract ID and customer ID are just text strings so let's have a look at this inside here we can say edit column and this will show us the data type that it's chosen so this is a single line of text and you'll see there are some other options here for the kinds of things we can work with and you can actually go in and edit these if you're not familiar with dataverse at this point just some level setting on what's Happening Here so this is actually creating a table in Microsoft dataverse which is the database and business process flow engine very very sophisticated piece right at the heart of Power Platform this is creating a simple table but stick with me because I'm going to show you how you can use this in more depth as we go along as well there are lots of different data types that you can work with in dataverse more than what we're seeing here but this experience of creating from Excel is just giving you this relatively set of options so we have this ABC icon is showing us that we've got the contract ID customer and so on email you'll see is actually recognized that it's an email format and foam has also recognized that it's a phone format so with the text columns in dataverse there are special formats and if you start to bring that into your app it allows you to do things like click on it and send an email or click to call those kinds of things as well a little more interesting here start date and renewal date now I did a test you can possibly tell from my accent that I'm not in America um this is the the date format this is a US date format that goes month day year most of the rest of the world doesn't do it like that my spreadsheet is how I look at dates which is day month year I'm actually a little bit surprised and delighted actually it's handled that so I've gone from my start date there is the first to the 12th that's the first of December in my world and it's actually turned that into that us date format probably a bit of a cheat given that I am working in a US environment for the preview but pretty happy that it's handled that you can also with dates in here have something that is either a date and time or a date only so it's recognized that it is a date only for that kind of thing there's no time in there now this is where it gets interesting we've got a type and a category here a couple of different icons so if I go into type this is picked up that this is a choice in my spreadsheet I had standard or premium and it's given it a choice this will behave as a drop down inside the app I can if I wanted to have a default choice so let's say that standard for instance was the default Choice then we can choose that same here with this category we've got these things that it's picked up on and it's given it those values we've also got a payment and that's a number of days and it's picked up that that's a number and a value here now it hasn't picked up that that's a currency even though I had that in my spreadsheet so we might want to change that instead of that being a whole number I actually want to use currency there currency has some other special properties and we're going to say that that one is required so any of these columns in the table we can make mandatory or not and that will affect the ongoing user experience of what's going on in the app you'll notice here use first row as column headers that's what it's done so it's peaked out the first row of my spreadsheet hopefully typically you've got that we can also change the table name here so this is called a contract record let let's change this to contact renewal and you'll see what's happening in the plural name is that it's following that along with me it's giving it a description of what's going on in here and it's nominating a primary column which is sort of the the name that it has to have inside dataverse and also giving it a technical name behind the scenes you don't actually need to worry about any of that so I've done a lot of talking but I haven't actually done a lot of clicking so far have I have gone create from Excel uploaded my file come here and given the whole thing a little bit of a once over and now I'm going to go ahead and click create app so this is now creating that dataverse table for me with all of those columns and all of the data from my spreadsheet so I had I don't know how many rows in there it will show you the first 20 rows that are in there but it will actually upload all of that data in there for you limit of five Meg on the file to do that though and again if you're working with something bigger than that stay with me and I will show you the other options you've got available the other thing that it's doing here is creating a canvas app so this is a really lovely user interface over the top of this you can use this in your browser you could embed it in teams you could use it on a tablet device however you want it's fully responsive wherever it ends up takes about 15 seconds or so to come through which seems like a long time when you're talking through it but I am going to let this one run in real time here it is slightly slower when I'm running my video recording software over it so you might find that this is slightly quicker when you have this experience if you've got questions about any of this make sure you pop them in the comments below this is all brand new stuff and so there's plenty more still to come also if you're getting value out of this video please give it a like and consider subscribing to my channel I've got lots of things on how to build power apps and how to work with dataverse all right here it is so we have got beautiful app with some lovely components here I'm going to do nothing and just play it for you and show you what it does so we go up to the top here we click play and this is giving me a preview of the app experience so we can see down the side here I've got this is called a gallery this is a list of the rows in the table and I can actually search so let's say I was searching for Daniel there we'll get Daniel's record and I click on that and now that's going to give me the detail of all of that in the app so there's all of the information about what was going on there I can edit and change something let's say this has changed to twenty four thousand dollars and update that so that's my app experience I can also create a new record a row in the system here so I could go ahead and enter all of that information and that would create that in my underlying database table or cancel out of that now if you don't like some of the formatting on the app here you can change that we'll just close this here and what you can do is use all of the usual skills you've got with powerfx and formulas and things it looks like there's a lot of things going on here but let's say that we're looking at this whole section here which is a form so I'm actually going to go over to the side here and sort of click that whole thing and say there was something I didn't want to see here for instance maybe I don't want to see customer ID I can go in here and edit these fields so let's say we've got so we've got both value and value base which are when you create a currency column these are one is the actual value and one is a functional thing that you really don't need to be looking at all the time so we'll just come in here and say remove that from the form let's say I also didn't want to see the customer ID in there so we can remove that from the form and you can see that happening underneath in there we can also change the number of columns this is a two column layout I could change it to be a one column layout if I wanted to and muck around with that so plenty of things you can do here to change this app to be whatever you want it to be once you are happy with that we're going to save that and we're going to call this our contract renewals and save it now if you've got a relatively straightforward spreadsheet and straightforward use case like that this is a super cool thing to do however let me talk you through the thing that concerns me here when we look at this and hopefully this is something that you might have spotted we've actually gone in here two quite different things going on we've got our list of renewals but we've also got a list of customers so some of these things in here these columns here are actually related to our customers now if you don't have a customer database and you're not doing anything else with these customers and this genuinely in your mind is just a single flat table then everything I've shown you is great you can go now and enjoy that and have fun but if you want something a little richer and more complex if you've got a spreadsheet that really is Mission critical and you've got stacks of data and rows of things and multiple tables and so on then you're going to need to go a level up with this and you can do that we can still use a little bit of what we've done here as a shortcut so what I'm going to do firstly is say let's say my spreadsheet is a bit different from this let's say that the customer data is actually in a separate spreadsheet and my customer ID number there is something that I'm going to link the two Excel tables and people will often do this to try to create a database of excel Excel is not a database by the way but people will do this they'll have related spreadsheet worksheets linked up with those ID numbers now a huge benefit of going into Microsoft database here is that we can do this properly we can actually have a relational data table that starts to open up firstly secure and scalable storage so you're not at risk with living in your spreadsheets but these things are properly linked with tables and relationships that's going to allow you to manage and look after your data in such a better way so for instance you might have your database here of customers and you might be able to look at then what is their related purchase history what is the related interaction history three all this can be hooked up with all of your Microsoft 365 tools with Outlook and email and so on what is their related contract renewal what is the history of all of those things and so you can start to get much richer and deeper with this than just a single table so let's come back into the maker experience and have a look at what's possible to do here if I start with tables there are actually a lot of things in dataverse that are ready for you to use so as you start to think about building out an actual data model taking something from Excel and putting into this proper database World get familiar with what's in here so what we've got is some standard tables this is looking at the recommended tables if you click on all you'll see even more in here but one of the most common things that people want and what's available in here are tables for account and contact which are things just ready made to store that customer information you don't need to go building that yourself incidentally if you try to upload a table using what we just saw in Excel to say create a data table of contacts it will let you do that and it will create a whole second table of contacts in here which is not as good as the out of the box one so if you do want that table of contacts I highly recommend you start here and do it this way so what we've got in here is our contact table if we have a look at the columns we have got a heap of different things that allow us to work with all of the information multiple addresses and so on it goes on and on there's a couple of hundred and you can actually configure this to add any other information that you want that's unique to your organization now what we're going to do here is do it properly inside a solution if I go back into my list of tables here we'll see that contract renewal table is sitting here what that's done with the creating the Excel app the way we did it it's just kind of put it in What's called the default solution which will work and that's fine for personal productivity kinds of things but if you need to turn this into an app that's actually going to be used at scale across your organization and you want to be able to go through on application life cycle management process with that you're going to land yourself in some less than desirable situations there so that Excel single click create a table from Excel I think is amazing if you're doing a fairly straightforward just want to get up and running quickly personal productivity small use case please please go for it it's awesome we are going to use that again to shortcut some of the process here but we do want to be putting things inside what's called a solution for a bigger use case so the way we do that is that we start over here in Solutions a solution is just a bucket where we can contain all of the different things that we're building to make it easier to see them and manage them as we take our application on that journey into being an awesome use case so we're going to just call this one contract solution you can call this whatever you want it to be and then there's another piece in here called a publisher which is like your digital signature on the thing to say that I created it or my organization created it so I'm just going to make this one me as a public the name is the display name and then we've got a schema name here as well which needs to just be without any spaces in it and my prefix for the things that I create are going to have LC in front of them okay we will save that publisher and now select that from the list here so what I've done is set up a nice bucket to say here is where I'm building my application and my data model and here is the publisher that's me and I'm just going to click create there now from here what we're going to do is bring in that out of the box contact table to do a little bit of work with it so what we're going to do is to say I want to work with the existing table that is my contact table and add that into the solution now the contact table all of the tables in dataverse have what's called a primary name field so in the case of the contact table that's just the the full name of the person will be like the header if you like of the record they also have a primary key a unique identifier for every record in the system you don't see that here it's called it's a good it's like a 36 character great big gobbledygook string of text that allows the system to uniquely identify every contact record however in our data we also have a unique identifier for the contact that was just like a little three digit number that we use perhaps that's like my account number or customer number from the finance system or something so that's my unique customer identifier for my business which is different from the primary key that's available in the table and in order to knit our spreadsheets together here where we've got a spreadsheet of customers and another spreadsheet of those renewals with the number I need to be able to use that to make them up so what we're going to do here is go inside that contact table and do a single piece of configuration here to set that up so we have got a column in here and we're going to add a new column and this is going to be called customer ID because this is my particular customer ID and that is just going to be a text it's just a string of text it's not actually a number that behaves as a number so we want to do that and we will save that and then there's one more step which is to tell it that that is going to act as a key it's called an alternate key so not the out of the box behind the scenes thing that it's using but this is for my front-facing business the thing that I want to use is a unique key so we go back to the contact table I'm going to click on Keys here and we're going to create a new key this one is going to be called the customer ID and it's going to link to that field and we save that now we're going to create the table here to keep those contract renewals and I'm actually going to use that same experience we just did to shortcut that whole process because typically if I had a spreadsheet with all of those sorts of things in it I wanted to create that table and database I'd have to go through and create all the columns one by one and I do love how quick and easy that experience is but what I'm going to do here is start with a version of this spreadsheet assuming that I've got my contacts on another spreadsheet we're going to upload all of this data later and I'm just going to start with this with a customer ID in here I'm just leaving that as a placeholder so that when we do the data upload it's going to create the link back to the contact but I've taken out all of those columns that repeat what's in the contact table the first name last name email phone number and so on so we'll close that file let me go back into my home screen and do exactly the same thing we did before so we're going to say start with data upload an Excel file and I'm going to drag that one onto there and it's going to do the same thing it did before just with fewer columns in there now we're not actually this this process is going to do creating the table uploading the data creating the canvas app when I really want to upload the data here but it's going to do it anyway so if you've got a massive long spreadsheet just grab enough rows so that it understands the shape of your data at this point because we're going to remove it and upload it properly with the links to the contact table when we're done you can upload this with just the header row and no data but what happens is that it doesn't pick up these kind of drop down lists and so then there's a bit more work to do that so it's easier just to sort of give it enough data that it understands the shape of what's going on dozen rows or so depending on how complex your data is so that's all good what I'm going to do here is just make this correction again to the value because I wanted that to be a currency and I also want to call this one contract renewal and save that and then we'll go ahead and create the app and that will do that same thing that we did before of creating the canvas app okay there it is let's save that because we might still want to use that later so I'm going to call this one contract renewal yep save it and then I actually want to get rid of the data in here because we want to do an upload with properly linked data so we can go across here into the data icon there's my contract renewals table that's just been created click edit data here and I'm actually just going to go through and grab all this and delete these rows in here so you can see I've uploaded about 20 odd rows of data you may not even have needed that many and delete those rows so what we've done is made a big shortcut to just creating the table in there creating the app in there that's all good but it's just going to be completely blank for now so what I'm going to do now is go back will save changes there I'm going to go back into my solution and make her experience and bring all of these pieces together and get the data in the system so we want to leave that app we're going to go back another level and from here if you get this experience of the thing being collapsed just do that to pop it back out again back into my solution now good practice with my solution again this might seem tedious but it will matter the more sophisticated you get with this you want your pieces together in here we saw earlier that that quick creation experience doesn't build it in a solution but we can add the existing pieces in here so let's add a an existing table and I'm going to come in here and just search for that contract renewal table and bring it in there and I want to include all the objects so all of the different columns and things I want to bring in there and the other thing I want to do is bring in an existing app and it's a canvas app the one that was just created there it is there and we can add it so now we've got a solution package with all of the different pieces that we're going to be working on so now it's time to create a proper link between these two tables set ourselves up well for a real relationship management between these pieces so we're going to go into that contract renewal table and have a look at the columns here and I want to actually get rid of this customer ID column here we want the customer ID column left on the contact but from here what we want is a lookup we want to make a connection to there not just have it as another static number and they're not related so we're going to come in here and I'm going to remove and delete it completely and create a different type of column in here which is called a lookup a lookup column is exactly how it sounds it's going to say from the contract renewal lookup to that other customer record in there and when you're bringing that data in from Excel that isn't one of the options you can't actually create that as part of that quick X import from Excel so we're going to create a new column here this is going to be let's call this one the customer and the data type you'll see we've got more choices here than when we're working just in the Excel piece before and I'm actually going to choose a lookup not choosing customer here even though it looks like I might want to that's a different thing that looks up to either an organization or a person I just want to look up to that contact table that we are working with we're working with individual people and save so that's all I needed to do what that's done is created on this table a relationship between them so that a contact or a customer in the system can have many different renewals associated with them now I'm going to do a couple of other optional things here on the contract renewal form this is building out some pieces for a model driven app and let me explain why we're doing that and what that's about it looks very tempting here to say import data import data import data from Excel those look like awesome options and they are however when we're working with this alternate key you kind of get a bit stuck with both of those options and doing it through the front end of a model driven app is actually so much easier plus you get a model driven app so we've got two types of power apps here the one that was created earlier is called a canvas app that is a beautiful user interface you can drag and drop into anything you like with it this model driven app is more like the pieces that are sitting directly to allow you to view this dataverse data in a way that's really easy to work with you can have both and so I'm going to show you the best of both worlds even if you don't want to go ahead with the model driven app doing the data import this way is is going to be just so much easier than any other way of doing it so I'm going to create a form and a view if you don't want a model driven app and that app we created earlier is the output you want skip over this part but I'm going to do it here for visibility if you're interested in more details on model driven apps I'll link you up to my tutorial on that and you can go ahead and have all the fun in the world but for now I'm just going to keep it really super simple to give us the visibility we want first thing I'm going to do is create a view and a view is like the Excel like sort of column layout of what we're seeing so we've got our contract ID here first thing I want is that lookup to say who's the customer and we're going to say let's get rid of the created on and then I'm going to bring in the other columns here as well so we had a category we had the payment the start date the renewal date the type and the value I could shift the order of these things around I'm not too concerned about that I just want to kind of get the stuff on the screen to show you how this works save and publish I'm going to go back a level and back a level into my contract renewal table here and then there's one more piece that I'm going to create which is called the form so that if I clicked on one of those rows I could open it up and see the details of everything on the screen so again we'll just click here on forms and I'm going to use the type that's called a main form and this just gives me that sort of single view of that record you can have multiple columns and tabs and all sorts of things going on here again check out the longer tutorial if you're interested in those things but for now I just want to keep it so that we've got visibility of what's going on with this lookup so we've got the contract ID and the customer and let's bring across again the category the payment start date renewal date type and value I think that's everything and save and publish so it's going to all come together very quickly now so once that's done we are going to go back into my contract renewal table so we're going to go back up the breadcrumb trail again here what I'm going to do now is create my app directly from this table so we're going to call this the contract renewal management app just to sort of separate it out from the one we made earlier so that we know what's what this will create an app from that table because that's all I'm really interested in and we're going to be able to use this front-end data import experience from here so this is created give it a second a model driven app we've got down the side here the contract renewals table I'm actually going to bring in the contact table as well just so that we can see what's going on so I'm going to bring another dataverse table in here now I do appreciate that all of this is a lot more complex than just going create an app from Excel I hope you see the pieces coming together here and where we're going to go in the end but you will end up with something that has proper relational tables in a scalable way and that frankly just is a bit more work so we are going to now save three buttons in a row here publish pick up all of those changes ready to go and play come on play there we go that's going to launch that in a new tab and here is my application so we've got a model driven app here which has a bunch of capability built in including things like being able to visualize your data in power bi but it's actually the Excel piece that we're interested in here so I'm going to start by uploading my contacts first because I want the people in the system and then I want to upload those renewals and Link them to the right contacts in the right customers so on the top menu bar here depending on your Zoom Factor I'm just going to zoom down a bit you will see that there's an import from Excel and what we want to do here is choose the import from CSV option you want to have your files in CSV for this to work just zoom it back up again so that it's a little bit easier to see so I'm going to choose my file here is my contacts file in CSV format we click next and it's saying do you want to have an alternate key it's a where there's an alternate key in there so yes we're going a Mark that one is the customer ID we've got a comma delimited file CSV do I want to allow duplicates so this also has duplicate detection built in let's review the mapping and this is one of the reasons there's a couple of reasons here why I like doing it this way but this is just such a super simple interface compared to either of the other ways of doing it even though it's a couple more clicks to get to this point once you get here this just becomes easier than any of the other ways of doing it so we now have the map pretty much done for us we've got the last name customer ID email first name the phone is not mapped because there's multiple phone fields in here we've got mobile phone business phone and so on so you're just going to choose where you want that to go I'm going to choose business phone finish import and that's done let's go in here and track the progress and what this will do is process it as a background job so this is your moment to go and you know give yourself a bit of a pat on the back grab a beverage of choice a beer or a cup of tea depending on your preference or the time of day you can see that it's passing it will then go through processing and then it will be complete and it will show you any errors along the way the larger the file the longer this will take this is only 20 rows and there's not much in there so hopefully the amount of time it's taken me to explain this yes there we go so we have 20 successfully imported rows let's take a look we'll go back into my contacts screen here give this a refresh and ta-da there are all of my contacts in there now what I'm going to do is the same thing to upload my renewals so we're going to go into the renewal screen here I haven't zoomed the browser this time so you just use the three dots if you can't see it import from Excel import from CSV choose the file and there's my contract renewals file next and we've got one extra step here because we are making that link so we're going to say again we don't want to allow any duplicates and we've got all of these things already done but here's our customer ID that's not mapped and this is where we want to create that lookup so what we're doing here is we're saying link that to that customer lookup but now we need to tell it which piece is uniquely identifying the match and that's what we're doing with that customer ID that piece that we've created that's unique to our business that set of numbers is 100 101 102 and again experience of doing it this way this is the part that's much easier to get to than by any of the other methods so what we need to do is just find that so on that other table on the contact table we called that one customer ID so we just map it up and say okay and now we've got green ticks all the way finish the import I won't bother tracking the progress this time that'll be done again take a breath almost a minute to do this and what we're going to get here when I hit refresh is see all of the columns on the screen with links back to those customers so let's do that now ta-da now this is my record and can you see this is actually a hyperlink in here so if I go in to that record there's the detail so this is the view that I created earlier and now the form that I created earlier but this is actually a link through to that customer so I can click on that and I've got all of this information now because I use the out of the box contact table it comes with some stuff already in there but we can see we've got the details and so on in there timeline I could track all of those things and then we can also see for this person any related contract renewals you can bring this onto the form to see it as well and have that in there so now we've got completely related data you've set yourself up beautifully you may or may not want to flesh out this model driven app but just do it for the import process if nothing else so let's close all those pieces down the final thing we want to do here is go back and see what's happened to that lovely canvas app that we started with so back in my solution here we've now got 2 two apps the canvas app that we created at the start and that renewal app let's go in and have a look at this one and just edit it so we can see we've got an error in here because we've changed some things around so let's just come in here to the edit fields and this time what I want to do is remove this customer ID this was the one that we started with so let's remove that error's gone and we can add something in here now which is that customer lookup that we've created close that save and we are all good to go if you'd like to learn more about how to work with those model driven apps check out my full tutorial here any questions or scenarios you'd like me to explore please pop them in the comments anything about this Excel to app experience from the basic to the advanced let's get rid of your mission criticals spreadsheets together thank you for watching

Channel: Lisa Crosbie

Views: 121,052

Keywords: powerapps, powerapps tutorial, power apps, lisa crosbie, microsoft copilot, model driven powerapps tutorial, microsoft dataverse, excel, excel to app, powerapps excel, dataverse power apps, dataverse, microsoft power apps, start with data, microsoft ai

Id: mnHxS5XD8jc

Length: 32min 45sec (1965 seconds)

Published: Mon Jun 05 2023