Microsoft Power Platform allows us to get data from various resources when creating Power Apps.
Furthermore, it is possible to automatically create the application with a few simple steps.
You can use Excel to create Power Apps applications, but this is not the most recommended approach. Excel is great for many things but is not as great at managing data as a database would be.
Dataverse would be a preferable option. And why is that?
Because Dataverse provides features that Excel don’t. For example, offers centralized and structured data storage, scalability, enhanced security and access control, seamless integration with other Microsoft services, version control and auditing.
There is a new preview feature in Power Apps that enable us to take data directly from Excel spreadsheets and convert it into a Dataverse database.
In this article, we will show you a practical example of this functionality, and how you can automatically create a simple application transforming your Excel spreadsheet into a Dataverse database.
If you have Copilot enabled, you will get extra help creating the tables: It will suggest the best names, descriptions, and data types based on your Excel spreadsheet.
Before starting, at Creativity Spark, we recommend visiting our Services webpage to learn how we can help you create that app or automation that will take your business to the next level. If you need more information, do not hesitate to contact us.
You will need:
• An Excel workbook saved on your PC. It is not necessary to have it in OneDrive.
• A Power Apps license. You can get a free trial or developer license.
Excel file transformed to Dataverse table
Steps to convert Excel into Dataverse:
We will use this simple Excel file:
There are 3 products with their ID, the amount of each, the price, and the total as the result of multiplying Quantity by Price.
1. Create an Excel file with the above, and save it on your PC.
2. Open Power Apps and go to the Home Screen. You should see something like this:
Power Apps Home
3. Select Start with data.
4. Upload the Excel file created before by clicking on Select from device.
Upload the Excel file
5. After a few seconds a preview of your table is shown.
Preview Dataverse Table
Make sure to carefully read the message on the left:
“The table name, description, and data types were generated by copilot Al. If column headers weren’t detected, they were generated too. Al-generated content might have mistakes. Make sure it’s accurate and appropriate before using it. Read preview terms”
Copilot has the capability to identify repeated values within a column. As a result, it automatically generates choice options in the column.
It is highly recommended to review all these generated actions and edit your table schema before selecting Create app.
If you don’t see the above message it is because it was created without copilot assistance. But still, is important to review the generated table.
Review Dataverse created table
Follow the steps below to review the properties of the generated table, and make adjustments if needed.
1. Edit table properties. You can see and make changes to the Display name, Plural name Description, … and the prefix name by clicking on Advanced options.
Edit table properties options
2. Row ownership. To determine the permissions on the data.
Row ownership options
3. You can click on the column headers, and then on the Edit column button.
See columns atributes
On the dialogue open, you can review and if necessary, change the properties of each column.
Review and change columns atributes
Create an App
After uploading and reviewing, you are ready to create the app. When you finish this process, a Dataverse table based on your input Excel will be created, along with a responsive Canvas App that is ready for you to use.
The top 20 rows of data are initially displayed in the canvas app to expedite app creation. The remaining data is uploaded in the background. You can review the progress in the table hub or table designer.
Go ahead and click on the Create app button.
Wait until creating the app
You should see a screen like the below as a result:
Power Apps Main screen App created
The app has been created and the data is successfully stored in Dataverse.
Detail Database Table
We hope you enjoyed this post. Try to follow the steps described and create your own Dataverse table and app directly from Excel.
If you need help with Power Apps or any other product in the Power Platform family, do not hesitate and contact us. We will be delighted to help you get the most out of your Power Apps implementation.
Any questions of comments, feel free to leave them in the comments section of this post.