Turn Your Excel Spreadsheets into Power Apps in Minutes with AI

hey there everyone long time no see I'm back at you with another video and today we're going to be covering the new Excel to table functionality this allows us to take any Excel file and turn it into a power app in a matter of minutes it will create the dataverse table for us import the Excel data and create a fully functioning powerapps canvas app for us to interact with our data I'll walk you through how it's done right after this foreign so if you haven't looked at powerapps recently the home page got a makeover a little bit ago and on this new powerapps homepage I'll show you how we get to this new Excel to table functionality if we click this start with data option you'll see the option here to upload an Excel file now to test this out I'm actually going to be testing in on two different Excel files that I have I have one file here that has my data formatted in a table and this table has information about registrations and it has nearly a thousand items in it so we'll see how it handles something with a lot of data formatted as a table and we'll also take a look at this other Excel file that I have which the data is not formatted in a table and it has over 500 records of some order information so we're going to look at how this Excel to table functionality handles each of these different types of Excel spreadsheets that we have let's start with the tabular data so we're going to click the select from device option and the tabular one is my registration so we're going to go to open and behind the scenes how this is working is it's using some of the new AI functionality that they started incorporating across the Power Platform to be able to recognize the columns that we have in the Excel sheets and create and recommend a dataverse table structure for that to store our data so the idea behind this would be if you have some kind of process or information that you're using Excel for to store that data Ai and work with it if you want to get off of Excel and into a real database and a system to manage that data this will help you do that really quickly so it's going to suggest a dataverse table structure create the dataverse table for you and create a power app for you to interact with the data so as you can see it only took a few seconds there for this to recognize all of the columns that we had in that spreadsheet and because I tagged my columns in the Excel with the right data types it was able to automatically recognize that and carry that over into the dataverse table structure so recognize these as single line of text but then it also recognize the email that it should be an email column type in dataverse so it's recognizing that we can see these are clickable links and it was able to surmise the shirt size column should be a drop down column multiple choice so we can select from a list of items where the shirt size should be it also automatically suggested a name for the table so this is one of the first things you'll want to double check when you're using this new feature is to see what it came up with and edit that to match whatever you're building here so it has employee record but this is actually a registration list so I'm going to change this display name by clicking on that pencil to registrations and we'll change this description as well so we'll say this table contains registration records and we could optionally change the primary column here I'm going to leave that as the ID and we can change the schema name if we want as well so we'll change the schema name as well to registrations and now if everything looks good with the recommended table structure I can simply say create app and it will go and create a powerapps canvas app for us that has this data source automatically added so right now it's creating that dataverse table for us and it's going to create a canvas app that uses that data so we can start interacting with it so it's loading right now and I'm trying to do minimal editing on this so you can get a feel for how long it takes from uploading your Excel spreadsheet to creating an actual app so now we see we really have a fully functioning app now one of the things I like about this if you've been around powerapps for a while and you've used some of the start from data templates you've noticed that it used to create a three screen application for you one screen for browsing the items one screen for adding a new item and another for editing an item what I like about this is it keeps it very simple by putting everything on one screen so on the left hand side it's where we can browse all of our items and on the right hand side is where we either add an item or C and edit an item that we have now what I really love about this is that it's responsive out of the box so this is going to work without me having to do anything extra on my desktop just fine as you're seeing here but it will also adjust and respond accordingly to be optimized for my mobile device and my tablet devices and one thing that you might notice that is new when we're working with this in the top right hand corner when we have it in preview mode here is we have this new Option this is going to allow us to preview what this application will look like on different device types so this is really helpful for you to test out the responsiveness of this without having to go through the pain of publishing it uploading it to your device and doing all that testing we can simply click on any of these icons like if I want to see what it will look like on a phone I can select this drop down it has all of these different types of phones that you can emulate and see what your app will look like on so I can select what I have here which is the iPhone 12. and get an idea of what this application will look like so it's going to show first initially the list of items that we have here so I can select one of these items and then that will take me to the details screen where I can see all the information about the items so as you can see works great on a phone if I want to see what it'll look like on a tablet I can go to an iPad Pro for example and this is what it would look like on a tablet in portrait mode so so many good Innovations going on in the powerapps space here night and day difference making it so much easier to be able to build responsive apps we can just simply upload an Excel spreadsheet it builds a responsive app for us and we can test that it's actually responsive and looks good right here in the maker experience which is amazing so that Excel spreadsheet that I uploaded had a thousand records so let's see and make sure that it actually uploaded all the records so we have the app here and if we go to the data tab we see that it did automatically add that dataverse table that it created for us from our Excel file so let's actually go to edit data and this is another thing I love we can edit the data right here from the application without having to go into another tab so we see this quick view to be able to see the data inside of my table I can scroll through and I'm scrolling and scrolling it looks like we have all nearly a thousand records so we had 999 records total and it added all of those in here and I can of course add in my additional columns so we can see all the information that we have so we had company name email first name last name and all that so I can save that and see the preview right here in my app of all the data that I have so it looks like all thousand records came across just fine and added here into my dataverse table and I have a fully functioning power app to be able to interact with that data so one more could you really ask for this functionality is really game changing so this is with an Excel file that the data was already formatted as a table and as we know if we've worked with Excel data inside of powerapps in the past that was generally a requirement if we wanted to pull in data from Excel and powerapps it had to be formatted as a table but what if you have something in Excel that has thousands of records and it's not formatted as a table and you can't or don't want to have to go through the pain of formatting that that way and you just want to upload that data so for that we have this order spreadsheet so this is not formatted in any way as a table so I haven't did anything special to this so we'll see how it's able to handle this file so go back to our start with data and select the upload and Excel file option again and select from device and this time we'll select our order spreadsheet and we'll see what it's able to come up with here now this spreadsheet has slightly less items in it we had about almost 500 items in the order spreadsheet so we'll see again how it's able to handle and how fast it can recognize the data in there and suggest a table structure for us all right so it's already done looks like all the data again this is not formatted as a table in this case in any way so it's just a plain data and Excel spreadsheet and it looks like it still came across okay so we have some ID columns here or quantity if we look at that I was able to recognize that that it should be a number field which is what we want order dates so this is one thing that didn't come across as expected in this case so it looks like order date came through as a string so this might be something we want to override so we can simply go and select the drop down go to edit column and for data type we can choose date and time and then we can specify the format more whether we want to do date and time or just date only so I might do date only here and we can update that and so you'll see now we're getting some errors so this is something that even if you don't have your data format as a table it will work but it's very important that you at least go through all of your columns in the Excel spreadsheet and make sure that the data types are defined otherwise you'll run into issues like this where It'll recognize this as a string and when you go try to even change it to say a date like it should be it's not going to recognize the data so it's not going to be carried over correctly so what we'll need to do to fix this is go back to our Excel spreadsheet and it's almost like I plan this error to happen here all right so we'll go to the order date field and we'll see how it did not come across as we want it so in Excel we'll right click on this we'll go to format and we'll choose this date option here and click ok so now that data is formatted okay which is good we might also just while we're in here make sure we're avoiding any other potential errors so go into our total amounts we'll format those cells and we'll do that as currency and then even though quantity came across okay in this example I'll go to format cells and we'll format that as a number with zero decimal places and then the rest should be okay so we'll give this a save so just making sure that we have good clean data now so we'll go back in this case we'll leave and discard this table and we'll upload that orders spreadsheet one more time and now that we formatted those cells with the correct data types it should come across fine and should recognize what we want those data types to be winner recommends our dataverse table structure here this is already looking much better so it looks like we have the quantity is a number order date is coming through as a date as we want and then all the rest of the fields are okay total amounts looking great so there we have all of our data and our dataverse table so we can click create app so now we have this example with unstructured data not formatted as a table inside of excel turning that into a responsive power app again in a matter of minutes just by uploading that with this new functionality there's our beautiful responsive power app and then of course we can go to our data tab we can edit the data in here just make sure all 499 I think it should be records came through so if we do some scrolling here and it looks like it did there's all of our data which is exactly as we were expecting so just to wrap up a few things that we need to keep in mind when using this new Excel to table functionality since the functionality here is taking your Excel file and moving that into a dataverse table you will need powerapps premium licensing to be able to utilize this feature and use the app that it builds for you since it's using dataverse also when you're uploading data in here you might have noticed as we were going through the process like say this registration spreadsheet that had the Thousand records in it when we were looking at the preview of the data that I gave us in this window here to see the table structure that it recommends you might have noticed that we only have 20 items so don't be afraid if you only see 20 here on this View and your Excel spreadsheet has thousands of records that doesn't mean the data isn't going to be carried over as we saw before it's only going to show the first 20 in this case because I would take too long to produce this view so when we click create an app in the background that's where it's taking all of our data and moving it over to dataverse for you so all of your data will get carried over so this functionality is in public preview right now so make sure to check it out give it a try and see what you think and try it out on your different Excel scenarios it will be a great way once it is generally available to convert those old Excel spreadsheets into Power Platform Solutions I'll put a link to some more resources to learn more about this feature in the video description and I'd love to hear what you think about this new functionality so drop a note in the comments and let me know what you think before you go do me a favor and if you like this video hit that subscribe button so you can be updated on future videos thanks for watching and we'll see you next time [Music] thank you

Channel: April Dunnam

Views: 29,629

Keywords: powerapps, power apps, microsoft copilot, excel to app, excel transformation, dataverse, microsoft ai, excel data, app development, power apps tutorial, excel spreadsheet, excel troubleshooting, copilot tutorial, excel to power apps, power apps from excel, copilot magic, microsoft power apps, start with data, upload an excel file, dataverse power apps, dataverse microsoft, dataverse canvas app, microsoft build 2023, Excel to app, excel to table, April Dunnam

Id: IyoClYxq2zw

Length: 13min 29sec (809 seconds)

Published: Wed Jul 12 2023