How to Create a Power Apps Collection From Excel?

Are you new to Power Apps? Then, it’s important to understand the significance of the Power Apps collection. In this Power Apps tutorial, I’ll walk you through the steps that how to create a Power Apps Collection from Excel.

Creating a Power Apps collection from an Excel file is a simple and useful way to manage and manipulate data within your app.

Moreover, you may sometimes be required to retrieve specific columns from an Excel table while creating a Power Apps collection; you can achieve this here by following the tutorial.

How to Create a Power Apps Collection From Excel

Here are the steps to create a Power Apps Collection from Excel.

  1. Format an Excel sheet as a table.
  2. Create a blank canvas app in Power Apps.
  3. Connect a data source to Import from Excel.
  4. Create a Power Apps app collection from Excel.

Step 1: Format an Excel sheet as a table

I have an Excel file regarding ‘Doctor Details,’ which has fields like:

Field NameData Type
DoctorNameSingle line of text
DoctorEmailHyperlink
SpecializedInChoice
YearsOfExperienceSingle line of text
ConsultDoneNumber
Power Apps Collection From Excel

We will create a Power Apps Collection using the above Excel sheet. But we can see that our Excel sheet is not formatted as a table if we try to connect an Excel sheet as a data source without formatting.

We will receive an error: “No tables found in the Excel file. Only tabbed data is currently supported by the importer”.

Create an Power Apps collection using an excel

So, 1st, format your Excel sheet into a table format using “Format as Table.”

Power Apps Collection From Excel table
  • Then, change the Excel table name, Select a table, and Click on the Table Design tab [upper right-hand side] -> Then, Rename the table name based on your requirement and save the Excel file.
How to create Power Apps collection from excel file

Step 2: Create a blank canvas app in Power Apps

Now, we will create a blank canvas app in Power Apps :

  • Go to the Power Apps Home page -> Select the Apps section [Right-hand side] -> Expand the +New app -> Then click on the Canvas app.
Create Power Apps collection from an Excel
  • Now a dialogue box will open as “Canvas app from blank” and provide the app name based on your requirement -> Then select the Format as Tablet -> Click on the Create button.
How to create Power Apps collection from Excel
  • Now, the Power Apps app will be created with a new screen, as shown below:
Create Power Apps collection from Excel file

Now, we have created a blank canvas app in Power Apps. The next step is to connect to the Excel data.

Step 3: Connect an Excel sheet as a Data Source in Power Apps

Now, we will connect a data source to an Excel to work on the Collection.

  • Click on the Connect to data from the middle of the Screen page -> A dialogue box will appear [left-hand side]; click on the Add data.
Create Power Apps collection from Excel table
  • Now Select a data source by expanding the Connectors -> Then click on the See all connectors -> Select the data source as Import from Excel.
How to create collection from Excel using Power Apps
  • Now, select an Excel file from your local device.
How to create Power Apps canvas app collection from Excel
  • A dialogue box will appear as “Choose a Table.” Select the data table -> Then click on the Connect button.
Create Power Apps canvas app collection from Excel file
  • The Excel table will now be connected to our Power Apps canvas app.
Power Apps create collection from Excel

Moving forward, our next step involves creating a Power Apps app collection from Excel.

Step 4: Create a Power Apps app collection from Excel

We will create a Power Apps collection from Excel using the App OnStart property by applying the Excel collection formula.

  • In the Tree view, Click on the App [left-hand side] -> Select the OnStart property -> Then apply the Excel collection formula.
Collect(colDoctorDetails, DoctorDetails)

Where,

  1. Collect: Collection function name.
  2. colDoctorDetails: Provide your Collection name.
  3. DoctorDetails: Excel sheet table name.
Power Apps create a collection from an Excel
  • Now, we will Run OnStart, to create a collection from an Excel file. On the App, click on the More options (…) -> Then select Run OnStart
Power Apps collection from Excel sheet

Now, let us check if the collection will be created from an Excel table or what?

  • On the left-hand side, click on the Variables (x) -> Expand the Collections -> Then select the Collection name, click on the More options (…) -> Then click on View Table
Power Apps create collection from the Excel file
  • Now, we can see that the Power Apps collection will be created from an Excel file, as shown below:
Power Apps create a collection from an Excel table

Per the above steps, I have shown you how to create a PowerApps collection using an Excel table. In my Excel table, we can see that there are only 6 fields.

However, you may required to retrieve only three specific columns from an Excel table to create a Power Apps collection; let me show you how to create it with each step.

Create a Power Apps collection by retrieving specific columns from an Excel table

You can retrieve only the specific columns from an Excel table while creating a Power Apps collection. Follow the below steps to achieve it:

  • After connecting a data source to Import from Excel to a Power Apps screen.
  • On the Tree view, Click on the App [left-hand side] -> Select the OnStart property -> Then apply the Excel collection formula.
ClearCollect(colDoctorDetails,ShowColumns(DoctorDetails,"DoctorName","DoctorEmail","SpecializedIn"));

Where,

  1. ClearCollect: This function helps to remove duplicate data and adds new data to the same Power Apps collection.
  2. colDoctorDetails: Provide your Collection name.
  3. ShowColumns: This function helps to show the columns of a table or a list.
  4. DoctorDetails: Excel sheet table name.

Then, provide the specific column names based on your requirements.

How to create a Power Apps collection by retrieving specific columns from an Excel table
  • Now, we will Run OnStart to create a collection from an Excel file. On the App, click on the More options (…) -> Then select Run OnStart.

Now, let us check that the collection will be created by retrieving specific columns from an Excel table.

  • On the left-hand side, click on the Variables (x) -> Expand the Collections -> Then select the Collection name, click on the More options (…) -> Then click on View Table
  • Now, we can see that the Power Apps collection will be created by retrieving three specific columns from an Excel table, as shown below:
Create a Power Apps collection by retrieving specific columns from an Excel table

This is how we can retrieve specific columns from an Excel table to create a Power Apps collection.

Conclusion

I trust this Power Apps tutorial has given you the knowledge and confidence to Create a Power Apps Collection from your Excel sheet.

By creating a collection from an Excel file, you can easily import and manage data from external sources without manually entering it into your app.

Lastly, you have learned to create a Power Apps collection by retrieving specific columns from an Excel table.

Furthermore, you may like some more Power Apps tutorials: