In this Power Apps Tutorial, We will discuss how to create Powerapps collection from an Excel Sheet.
You can also check my previous articles on how to Add, Update, Remove and Filter items from PowerApps collection and also how to create a collection in Power Apps from the SharePoint Online list.
Create PowerApps collection from Excel
To create PowerApps Collections from an Excel sheet, We need to follow these below processes as:
- Create an Excel file, Add some records and format that Excel file as Table
- Rename the Excel Table Name
- Create a Canvas app in Power Apps
- Connect a new or existing Data Source and Import from Excel to your App
- Add a Button and Apply the Collection formula for Excel Sheet
- Preview the Canvas app in the Power Apps
- Excel Records added into the Power Apps Collections
PowerApps create collection from excel – Syntax
Below is the syntax to Create Collection from Excel in PowerApps.
- Collect = Collection Function Name which is used to create Powerapps Collections
- MyExcelCollection = Collection Name
- ExcelSheetName= Provide the Excel Table Name which contains records that you wants to display in the Powerapps Collections
Create Excel, Add Records, and Format Excel as Table
Follow the below steps:
- Create an Excel file using some sample records as below.
- Open the Excel file, go to the Home tab. Click on Format as Table option and select any table format.
- Change your Excel Table name (Select Table -> Go to Design tab -> Rename Table Name) as Corona_Details and Save it as like the below screenshot.
Create a Canvas app in Power App
- Sign in the PowerApps app with your credentials.
- Go to Apps option from the left navigation.
- Select Canvas under the + New app tab from the top of the page.
- Select Tablet layout under the Blank app section as shown below.
Connect a Data Source and Import From Excel to your App
Click on to connect to data from the middle of the page and expand the Connectors (from left navigation). Select the new Data Source as Import from Excel as shown below.
Upload the Excel file from Desktop or your destination path. Choose the Excel table by name (Corona_Details) and click on the Connect button.
Add a Button and Apply the Excel Collection formula
Here, we will add a button into an Power Apps App and on click of that, we will populate the PowerApps collection.
In the Power apps Screen, add a Button (Click Insert -> Button).
Double click on the button and just rename the Button name to Add Excel Records to Collection.
Also, you can provide some button properties like Color, Border, Font, Font size etc.
Select the Button and apply the below formula in its OnSelect Property to this function.
OnSelect = Collect(ExcelItemColection,Corona_Details)
- OnSelect = Button Property Name
- Collect = Collection Function Name
- ExcelItemCollection = Collection Name
- Corona_Details = Excel Table Name
Preview the App in the PowerApps
Now preview the app to see how it works. To preview, click on F5 or Preview icon from right top of the page.
In the Preview screen, the Button will appear as the below screenshot. Just click on this button.
There will be no effect when you will click on the button, but all the Excel records will be added into the Powerapps Collections.
Excel Records added into the Power Apps Collections
To view all the Excel Records in the Powerapps Collections, Go to View tab -> Collections as shown below.
Click on your Collection Name (ExcelItemCollection) and then you can view the first 5 records in the Powerapps Collection.
This is how to create a collection in Power Apps from excel.
Also, you may like some below Powerapps Tutorials:
- How to display images from a SharePoint Online library in PowerApps
- Create a Data Table from Excel Data using Power Apps
- How to share PowerApps with external users
- How to create and use PowerApps Collection
- How to Create a Canvas app from SharePoint List in Power Apps
- How to use List Screen in PowerApps
- PowerApps charts (Column, Line and Pie Chart)
- How to format number as currency in PowerApps
In this PowerApps tutorial, We discussed how to create PowerApps collection from an Excel Sheet.
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