In this Power Apps tutorial, We will discuss how to create a Canvas app from Excel in PowerApps.
We will also check, how to add a new screen in the PowerApps App, as well as how to Display form and Edit form in PowerApps.
To create a Canvas app using the PowerApps, We have to follow these two steps as:
- Create and Format an Excel file as Table and Upload it into OneDrive for Business
- Create a Canvas app from Excel in PowerApps
- Create PowerApps App for SharePoint List
- Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax
- How to share PowerApps with external users
Create and Format Excel as Table and Upload it into OneDrive for Business
First, we need to create an Excel file using some sample records as below.
Open the excel file, Go to the Home tab and click on Format as Table option. Select any table format as shown below.
Below are the table formatted Excel Data. Also, change your Table name.
Save your Excel file under the name (EmployeeWorkStatus.xlsx) and close it.
Upload your Excel file into any Cloud-Storage account like OneDrive, OneDrive for Business, etc.
Create a Canvas app from Excel in PowerApps
Follow the below steps to create a Canvas app from Excel Data using PowerApps.
Step-1: (Create Canvas app from blank)
Open the Power Apps page through Browser. Sign in your PowerApps using Microsoft account.
In the PowerApps page, Select the Canvas app from blank under Make your own app as shown below.
Step-2: (Specify Name for App)
Provide a name for your new app (EmployeeExcelApp), Select any format as Tablet or Phone and then click on Create.
If any dialogue box (Welcome to Power Apps Studio) will open then select Skip.
Step-3: (Connect to Data Source)
Select connect to data link option in the middle of the screen.
The Data sources will appear on the left side of the page once you clicking on the link. Expand the Connectors and select the connection for your cloud-storage account if it shows in that Data sources.
Otherwise if it not appears, then you can easily add a new connection using +Add a connection.
Select OneDrive for Business under the Connection section and add your connection.
Step-4: (Choose an Excel file)
In the Choose an Excel file box, type or paste the first letter of EmployeeWorkStatus to filter the list. Once you get your uploaded file, select it.
In the Choose a table section, select the checkbox for Employee_Work_Status and then click on to Connect. Now you can close your data pane by using a cross icon.
Create a New Screen in PowerApps
Click on New screen present under the Home/Insert tab. You can see a number of screens are available inside the new screen as Blank, Scrollable, List, etc. Here select a List screen as shown below.
The new List screen will appear as like below screenshot.
At the top of the new screen, the screen title will appear as App. You can rename the App Title by just double-clicking on it. As I have renamed it to Employee Records.
Similarly, you can see the Header Title properties at the right side of the page under the LABEL section. You can change the Title Properties as Font, Font size, Font weight, Font style, Text alignment etc.
In the same way, if you want to change the properties of the Body or GALLERY part, then just select the body and change the Gallery properties under the GALLERY section.
Here, I have changed the Layout from Image, title, and subtitle to Title, subtitle, and body by clicking on the down arrow option.
Now in the Formula bar, Replace TempateGalleryList2 with Employee_Work_Status. You can see the excel records with your formatted layout.
Edit Field Data in PowerApps Screen
To change the data display format, Select the body part of your screen. Go to Properties and click on Edit option from Fields section.
Now you can choose your Data fields at which position you want to view. As in the below screenshot, I selected the WorkingTime field in Body2, Weekend field in Subtitle4 and EmployeeName in Title6.
Now the excel records are appearing in the screen as like the below screenshot.
Now we will see how to create a Display form, Edit form of Employee using PowerApps.
A display form will help you to view all the details of an Employee whereas an edit form will help to edit or modify any field of an employee using PowerApps.
Also, you can save the edit form once your modification is done.
Before going to create the Display and Edit Form, Make sure to create three different screens. On the first screen (Employee Details Screen), You can see the Title of the employee only.
When you will click on the next arrow icon (>) of a specific employee, then it will navigate to the next screen.
On the second screen (Employee Details Screen), there will be a Display form that will contain all the details of an employee. If you want to change anything in that display form, then you can click on Edit icon otherwise click to Back or Trash icon.
On the third screen (Employee Edit Screen), It contains an Edit form, when an employee clicks on the edit icon in the second screen, then it will navigate to Edit form.
Below screenshot represents all three screens as:
- Employee Details Screen (List Screen)
- Employee Display Screen (Blank Screen)
- Employee Edit Screen (Blank Screen)
Create a List Screen using PowerApps
Here you will learn how to create a Display form using PowerApps. This below screenshot represents the first screen (Employee Details Screen).
Follow the below process to create the first screen (Employee Details Screen):
Go to Home/Insert tab -> New Screen -> List -> click on Connect to data -> Select OneDrive for Business -> Choose Excel file -> Select Table name -> Click on Connect.
Select the body part of the screen -> Go to Properties tab under the GALLERY section in left navigation -> Select Layout as Title as shown below.
To navigate the second screen, write the formula in the formula bar by selecting the next arrow icon (>).
OnSelect= Navigate([@'Employee Display Screen'], None)
Create a Display Form in PowerApps
Similarly, follow the below processes to create the Display form in the second screen (Employee Display Screen):
Go to Home/Insert tab -> New Screen -> Select Blank as shown in the below screenshot.
Now go to Insert tab -> Click on Forms -> Select Display.
Select Data source under the Properties section from the DISPLAY form.
Click on Edit fields and add all the fields. Select 1 in the Columns field and Vertical in the Layout field as shown below.
Click on FormViewer1 and write the display formula in the formula bar.
To add a Header on the top of the Display form, go to Insert tab -> Select Text -> Click Text input as shown below.
Provide a Header Title (Employee Details) to the Display form by just double-clicking on it.
Add the below icons in the Display form: (Go to Insert tab -> Icons)
- Edit: It helps to edit the form.
- Back: It helps to navigate to the back screen.
- Trash: It removes the item from the screen.
Select the Edit icon and apply the below edit formula in the formula bar.
OnSelect= Navigate('Employee Edit Screen', None)
Select the Back icon and apply the below back formula in the formula bar.
In the same way, for the Trash icon, select and apply the below formula in the formula bar.
OnSelect= Remove(Employee_Work_Status, TemplateGalleryList1,Selected); Back()
Now to Preview the Display screen, Press F5 or Run icon on the top of the page.
Select the next arrow icon (>) of any employee from the screen.
In the second screen, you can see that specific employee’s details. Also, you can edit, go to back and delete that specific employee details from the screen as shown below.
Create an Edit Form using PowerApps
Follow the below instructions to create an Edit form in the third screen (Employee Edit Screen):
Go to Home/Insert tab -> New Screen -> Select Blank as shown in the below screenshot.
go to Insert tab -> Click on Forms -> Select Edit as shown below.
Select Data source under the Properties section from the EDIT form.
Click on Edit fields and add all the fields. Then click on the Add button.
Select 2 in the Columns field and Vertical in the Layout field as shown below.
To add a Header on the top of the Edit form, go to Insert tab -> Select Text -> Click Text input as shown below.
Specify a Header Title (Employee Edit Form) to the Edit form by just double-clicking on it.
Add the below icons in the Edit form: (Go to Insert tab -> Icons)
- Save: This icon helps to save the edit form.
- Cancel: This icon helps to cancel the Edit form and go back to the Display form.
Select the Save icon and apply the below formula in the formula bar.
Select the Cancel icon and apply the below formula in the formula bar. This below formula will also help you to go back to display form.
Preview the PowerApps
To Preview the Power Apps, Press F5 or Run icon on the top of the page. This below screenshot represents the total performances of Display and Edit form.
Select the arrow icon of an employee -> click on Edit icon -> Edit any field in the Edit form -> Save the Form otherwise Cancel the form. If you will cancel, then it will go back to the previous details form.
Similarly, In the Display screen (Employee Details), If you want to delete the record, then simply click on Trash icon otherwise go back to the First screen (Employee Records) by clicking on back icon.
Save and Publish the PowerApps
Once the Power Apps is done, you need to Save and Publish the app. Go to the File tab from the top of the page as shown below.
Provide some Version note about the updates you have made. Click on Save.
Click on Publish button as like below.
If you want to modify the app details before publishing like App Name, Icon, Description, etc., then click on the Edit details.
Modify the app with Name, Icon or Description and Save it.
Again Publish the app by using Publish this version button as shown below.
Share the PowerApps
Also, you can share the new app with other team members in your Organization. So that other people can use this app easily. Click on Share.
Enter a name, email address of the user whom you want to share the Power apps. Check to Send an email invitation to new users. Click on Share.
When you will share the app with another user, then an App Web link will generate automatically. Also, you can open the app through this web link as like below screenshot.
Also, you may like the below Power Apps tutorials:
- PowerApps Functions Tutorial
- PowerApps gallery control filter example
- How to use PowerApps Table() Function
- PowerApps show hide fields based on Yes/No column
- PowerApps update data table columns
- Export PowerApps from one Tenant to another Tenant
- Getting your data PowerApps
- Working with PowerApps Collection
- How to create tabs in PowerApps in SharePoint Online List Form
In this Power Apps tutorial, We discussed how to create a Canvas app from Excel in PowerApps.
Also, we saw how to create a New Screen, a List Screen in PowerApps. We learned to create a Display Form, Edit Form from Excel data in Power Apps.
I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com