Create Power Apps from Excel with data | Microsoft AI Copilot

hello everyone Raza here in this video I will show you a new feature through which we can bring data from an Excel file and directly create a canvas app bringing data from Excel to dataverse offers so much benefits in terms of scalability Rich data types Advanced security and so much more Excel data will be transformed into a table and dataverse and once we click create app it will build a fully responsive canvas power app I will also show you how to transform the data in our Excel file using power query and creating a similar experience so let's get started with this video [Music] we can now create a canvas app with data from an Excel file on the home screen experience we have start with data and one of the first options is to upload an Excel file when you upload an Excel file it generates a dataverse table and if AI is implemented for your organization the AI copilot feature can assist in table creation by suggesting table names descriptions column types headers and more I have a simple Excel file that has invoice data this Excel file has no table it simply has one sheet now let's bring in that Excel file directly here we can upload the file or drag and drop the file and the initial experience that we can see right here will only show the first 20 rows because this is more like a preview experience if copilot is enabled for your environment on the left hand side that says that copilot has gone ahead and created the stable experience copilot was smart enough to understand that the first row in my cell file was a header row so it enabled used first row as header it went ahead and defined the names of the columns and also the types of the columns so invoice ID it's mapped it to a single line of text I am free to change this to any other data type and I can also decide whether I would like to make this a required field or not invoice date it's mapped it to a date field customer as text customer email it's mapped to format email customer addresses text total mapped it to a currency data type and finally invoice status you can see how smart copilot is it's understood the data in that column and gone ahead and created a choice data type which has the following three choices which maps to my data I even have the table name right at the top I'll change this to invoice the primary column it's selected as invoice ID click save and I will go ahead and click create an app and just like that powerapps will begin the process where it will first create the table in dataverse plus import all the data from the Excel file into the table and go ahead and create a fully responsive canvas powered app and here is the PowerApp experience that it has created on the left hand side under data it's connected to a table called invoices if I click edit data this will take us to that table experience can go and create additional columns or even go ahead and change existing columns invoice ID you can see that from my Excel file there was a specific pattern to it so for this column if I go to edit the column notice the data type a single line of text I'll change this to Auto number I can even Define a prefix my prefix will be I and V and my Excel file had data all the way to id1016 so I will seed the value here to begin from one zero one seven and I'll click save once that's done I'll close notice powerapps refreshes the connection to that data source on the home screen of this power app we have a gallery that showcases data from the invoices table there is also logic to search data in this Gallery based on the text entered in the search box we can create a new record in this table edit existing records read records or delete records form control experience shows data based upon the columns or fields that are selected invoice ID is automatically generated and right at the top I can see that I do not want the user to define the invoice ID so I'll simply remove this let's go ahead and preview the app I can select an item in the gallery and it will show the data respective to the selected item I can edit I like to change the invoice status here to paid and submit and that will go ahead and update the status for invoice 1002. can also create a new invoice pick my date I'll give the total invoice amount set the status and submit and just like that if You observe the invoice number that gets generated for the newly created item is the next invoice in sequence make sure you save the app if you would like to test the experience of how this app looks in different devices or window sizes go to settings support and change the authoring version to be 3.23053 or Beyond and once you have the authoring version set this time notice we can actually test the experience of this app in a wide variety of devices that we can choose and it's a fully functional experience of the PowerApp and if we want to test the same experience in an environment where co-pilot is not enabled if I go to start with data and go to upload an Excel file I'll upload that same Excel file notice this time the copilot has not created this experience so it doesn't show that information on the left hand side for my total column supplied it as number but this is something what I would like to make as a currency invoice status ideally should be choices so I can go and Define what my choice values are from my Excel file and it's gone ahead and made that update and I can go ahead create the app which would create the table bring in all this data and give me a fully responsive PowerApp experience here I have an Excel file that has information about flooring estimates I have two sheets in this Excel file what happens if I try and import this file drag and drop the flooring estimates Excel file it will only read the data coming in from the first sheet of the Excel file so if you do have a scenario where your data is spread across multiple sheets you would need to make sure that they are all placed in a single sheet in order for the import from Excel experience to read that data upload the updated file if you have scenarios where you have blank rows or where you have certain data and cells that you do not want to be a part of the data that's imported it just purely brings in all data from that first sheet that it experiences so it is important that you clear or cleanse your Excel file up front you ensure that you have clean data prior to importing it into the upload and Excel file experience and once you're ready you click create app and once the app is ready we have a fully responsive power app which has the data imported from that Excel file into a database table and the app is connected to dataverse let's take a more complex use case the product table and I have a separate table where manufacturer information is maintained the data is spread across multiple sheets and there are other data cleansing activities that I would like to perform now even for such scenarios we have options in powerapps go to tables we have an option called import data from Excel this requires that your Excel file only has one sheet and it has tabular data however the more advanced option is import data you have two options if you import into a new table it has a limited set of data types that it supports but if you import into an existing table it offers a lot more flexibility in terms of data types so for that reason I will go ahead and create a new table in dataverse give it the name product info I already have a column where I can store the name of the product I'll add one for ID category this has four options so I'll create a choice column in dataverse data type choice I'll create a new choice I'll call this category Choice values and start providing the choices I'll click save sync this with category Choice values save I'll create a column for manufacturer name and last I'll create a column of type currency for storing the price I would like to load the data from that Excel file by cleansing it into this table so I'll go to tables go to import use import data this will load power query and you can literally bring in your data from any data source of your choice in my case it's an Excel file so I'll pick Excel and I will upload my file drag and drop it here make sure I'm signed in I'll click next now notice it starts reading data from that Excel file so if I pick product table it shows me all the data in that specific table if I pick manufacturer shows me all the data in that sheet I'll go ahead and click next with power query we can transform that data rows 5 6 and 7 two of them are blank rows one's just informational so what I can do here is for manufacturer there's an option for keep rows I'll say keep top rows 4. so it'll go ahead and apply that step to transform the data next I would like to transpose the data in manufacturer select manufacturer go to transform and click transpose it has turned rows into columns and columns into rows column one I do not need so select the column go to home remove the column now the first row actually has the Header information so I can simply click used first row as headers and as you can see I've gone ahead and cleansed that data in manufacturer sheet now I'll go to my product table now the name of the product also has some additional information you can see there is a delimiter in between split this column data and we have a split column option split by delimiter the delimiter I'll add custom my daily limiter is the pipe operator click ok power query will go and split that information now this column if I double click the header I'll call this product the second column that it generated I do not want this so select and remove category I have one value in my Excel file and after that there's a lot of empty rows then I have a value and empty rows all the data below mix should have the value mix all the data below rural should have the value rural so for this scenario I'll click the column go to transform there's an option for fill in my case I would like to fill down and you can see the Magic in action you can also change the data types of the columns by simply selecting the option to change the data type now price as a column once again I'll select this split this by a delimiter I'll pick space click ok that ways I get two columns the main column here is price I'll rename this to price I'll change the data type to currency the split created the other column as well this one I will go ahead and remove so I'll select manufacturer ID there's an option called merge queries so I'll pick this merge this column with manufacturer on the manufacturer ID column and I will go ahead and click ok so it goes ahead and gets all that connected data and from this table I would like to only get the name of the manufacturer so I'll select this option here and pick the column there called manufacturer which has the name and I can rename this column to manufacturer name now that I have transformed my data I'm only interested in loading this product table so manufacturer I will right click and remove enable load and I will go ahead and click next now notice manufacturer do not load product table I would like to load to an existing table and this table is my product info table that I will select right here now all I have to do is provide the mapping so what's my source column for my category column which was of type Choice I'll map category ID would be product ID manufacturer name the name column would be the product and price would be price I'll click next I'll click publish and this will now begin the process of importing the data from that Excel file into my table called Product info under the hoods it creates a data flow we can see right now it's started the process of importing the data and if I go to product info my table should start receiving the data coming in from my Excel file transformed using power query and now if I need a fully responsive power app based on this existing table in dataverse I'll go to home start with data I'll say start with an existing table here I will search for my table select my table and click create an app and here is the power app that it has created if you enjoyed this video then do like comment and subscribe to my YouTube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 32,361

Keywords: create power app from excel, powerapps excel, excel, powerapps, power apps, microsoft, ai, copilot, microsoft copilot, excel to app, excel transformation, dataverse, microsoft ai, excel data, power apps tutorial, excel spreadsheet, excel to power apps, power apps from excel, microsoft power apps, start with data, upload an excel file, dataverse power apps, dataverse microsoft, dataverse canvas app, microsoft build 2023, reza dorrani, first power app, app development, dataflows

Id: Fy-kdOZRFFE

Length: 18min 43sec (1123 seconds)

Published: Tue May 30 2023