Power Apps Create Collection Using Excel [Complete Guide]

In this Power Apps Tutorial, we will go through different types of examples of how to create a collection using an Excel sheet within the Power Apps based on various scenarios.

While using the Power Apps canvas app for the most recent project, we received a request to create a Power Apps collection using the Excel sheet inside the Power Apps environment.

It will also be discussed how to work with the Power Apps collection using Excel, which contains the following items:

• Power Apps creates a collection from an Excel table
• Power Apps creates a collection of Excel Syntax
• Show Power Apps collection value from Excel
• Power Apps collection Excel items count
• Power Apps collection Excel rename column
• Power Apps collection Excel delete column
• Power Apps collection Excel sorts a column
• PowerApps collection Excel sort multiple columns

Before building a Power Apps collection using an Excel sheet, we need to connect the Excel sheet to the Power Apps canvas app. For this, the following steps are applicable:

• Open Excel and prepare an Excel sheet with some data. Suppose We have an Excel sheet named Country Details having columns such as Country, Captial, Population, Currency, and Continent.

• Format the above Excel sheet to the table.

• On the Power Apps, create a canvas app. Import the Excel table as a data source. For this, navigate to Add Data > Search Excel Online (Business) > Choose your connection > Select data set > document library > Select the Excel table > Connect. Once we connect the Excel table, we can see that within the Power Apps Data section.

This is how to import an Excel table to the Power Apps.

Also Read: Power Apps Collection Using SharePoint List

Power Apps creates a collection from an Excel table

Once the Excel table has been imported into the Power Apps, we can create galleries, data tables, and collections that make it possible to deal with the data. As per our requirement, we will see how to create a collection from the excel table within the Power Apps.

What is the syntax to create a Power Apps collection from an Excel

To create the collection from the excel table, the syntax is:

Collect(<Collection Name>, <ExcelSheetName>)

Where,

1. Collect: This Power Apps function allows the creation of a collection.
2. Collection Name: Provide a new or existing collection name
3. ExcelSheetName: Provide the excel sheet name.

Let’s implement this syntax in our app to create a collection from the Excel table. The required steps are:

• On the Power Apps screen, add a button control.
• Set the Text property as “Create Collection”.
• Insert the below expression on the button’s OnSelect property to create a collection within the Power Apps.

OnSelect = Collect(CollCountry, Table1)

Where CollCountry is the name of the new collection and Table1 is the name of the Excel table.

Once we click on the button, we can see a collection named CollCountry will be created in the Power Apps collection section shown below:

This is how to create a PowerApps collection from an Excel table.

Check out: Power Apps Timer Control Examples

How to show Power Apps collection value from Excel

In the above example, we have seen how to create a Power Apps collection from an Excel table. Now, we will see how to show this collected value within the Power Apps screen. For this, the following steps are:

• On the Power Apps screen, add a data table.
• Set the Items as the created collection name i.e., CollCountry.
• On the data table properties panel, go to Fields > Edit fields > Add fields > Select the desired fields to display on the data table.

Similarly, we can use a vertical gallery to display the collected data within the Power Apps screen. This is how to show Power Apps collection value from Excel.

Power Apps collection Excel items count

We occasionally may question how to count the number of items in the Power Apps collection, which was created from an Excel table.

So in this section, we will see how to count the Power Apps collection items created from Excel. To calculate the item count, we will use the above collection that we have created from an Excel table and the following steps are:

• Add a Text Label control to the above Power Apps screen and place it near the data table.
• Insert the below expression on the label’s Text property.

Text = "The total count of Items is: " & CountRows(CollCountry)

Where CollCountry indicates the name of the already-existing collection that we made in the example above.

Once the formula is applied to the label control, we can see it will display the total count of the collection’s Items shown below:

This is how to count the Power Apps collection using Excel.

Read: Power Apps Notify() function [How to use with examples]

Power Apps collection Excel rename column

Power Apps allows us to rename the column(s) within a collection that was created from an Excel data source.

Consider that we wish to rename the columns in a new collection that we will build using the Excel data mentioned above. Where we will rename the Country to Nation, Capital to Metropolis, Population to Birthrate, and keep the other columns the same i.e., Currency, Continent.

For this, the following steps are:

• On the Power Apps screen, add a button control.
• Give a name or set the button’s Text property as per your need (Ex: Rename Collection)
• Insert the below expression on the button’s OnSelect property.

OnSelect = ClearCollect(
    CollNewCountry,
    RenameColumns(
        Table1,
        "Country",
        "Nation",
        "Capital",
        "Metropolis",
        "Population",
        "Birthrate"
    )
) 

Where,

• CollNewCountry is the name of the new collection.
• Table1 is the name of the Excel table.
• “Country“, “Capital”, and “Population” is the name of the Excel column.
• “Nation”, “Metropolis”, and “Birthrate” is the new names of the columns.

• While clicking on the button, it will create a collection by renaming the columns within the Power Apps collection section.
• Add a data table, and set the Items property as “CollNewCountry “. Also, add the fields to the Power Apps screen in order to display the acquired data.

Now, we can see the collected data will visualize within the data table by renaming the columns shown below:

This is how to rename columns in the PowerApps collection from Excel.

Check: Power Apps Filter With Date Picker

Power Apps collection Excel delete column

In this section, we will see how to remove or delete the column(s) from a Power Apps collection that was created from Excel. That means we will create a collection by removing certain columns using the Excel table such as Population, and Continent.

• On the Power Apps screen, add a button control. Set the Text as Remove Collection.
• Insert the below expression on the button’s OnSelect property.

OnSelect = ClearCollect(CollColumn, DropColumns(Table1,"Population","Continent"))

Where,

• CollColumn is the name of the new collection to store data.
• Table1 is the name of the Excel table.
• “Population”, and “Continent” are the name of the excel columns.

Let’s click on the button while clicking on the Alt Key. We can see a collection named CollColumn will be created on the collection sections by removing the specified columns.

We can display these data via a Power Apps data table on the screen. This is how to create a Power Apps collection from an Excel table by deleting columns.

Have a look: How To Set Default Date in Power Apps Date Picker

Power Apps collection Excel sorts a column

In this section, we will see how to sort a Power Apps collection column that is retrieved from an Excel Table.

To work with this requirement, we are going to use the above-existed collection named CollCountry. By using this collection, we will create a collection where the Population is sorted by Ascending order. The following steps are:

• On the Power Apps screen, add a button control. Give a Text to the button. Ex- Sort Column.
• Insert the below expression on the button’s OnSelect property.

OnSelect = ClearCollect(CollSortPopulation, Sort(CollCountry,Population,Ascending));

Where,

1. CollSortPopulation is the name of the new collection
2. CollCountry is the name of the existing collection
3. Population is the name of the collection column
4. Ascending indicates sorting order

Now click on the button to create the collection. Next, insert a data table control to the Power Apps screen, set the Items property as CollSortPopulation, and add the fields to display the sorted data.

As we can see, the data table displays the collected data in ascending order by ascending the Population column.

This is how to sort a Power Apps collection column created from an Excel table.

Check out: How to disable Power Apps date picker

PowerApps collection Excel sort multiple columns

Similarly, in this section, we will see how to sort multiple columns of a Power Apps collection that build from Excel data.

Assume we’re going to use the CollCountry collection from above. Using this collection, we will create another collection also with Capital and Population columns sorted in ascending and descending order. To fulfill this requirement, the following steps are:

• On the Power Apps screen, add a button control.
• Give a text to the button (Ex- Sort Multiple columns).
• Insert the mentioned expression on the button’s OnSelect property.

OnSelect = ClearCollect(CollSortColumns, SortByColumns( CollCountry,"Capital",Ascending,"Population",Descending))

Where,

1. CollSortColumns is the name of the new collection name.
2. CollCountry is the name of the existing collection.
3. “Capital” and “Population” is the name of the collection columns.
4. Ascending, Descending is indicating to the sorting orders.

Let’s click on the button while clicking on the Alt key. Insert a data table into the screen and add the fields to display the collected data.

We can see the collection’s columns are sorted in ascending as well as descending order within the above data table. This is how to sort multiple columns within the Power Apps collection build from Excel data.

Conclusion

From this Power Apps tutorial, we learned how to build a Power Apps collection using an Excel table. Also, we have covered things such as:

• Power Apps creates a collection from an Excel table
• Power Apps creates a collection of Excel Syntax
• Show Power Apps collection value from Excel
• Power Apps collection Excel items count
• Power Apps collection Excel rename column
• Power Apps collection Excel delete column
• Power Apps collection Excel sorts a column
• PowerApps collection Excel sort multiple columns

Additionally, you may like some more Power Apps Tutorials:

• Power Apps Collection Removes Items [With Examples]
• How to Patch Power Apps Date Picker
• How to use Power Apps date picker
• Power Apps Checkbox control within Gallery
• How to use Power Apps Check Box Control
• How To Set Default Value in Power Apps List Box Control
• Power Apps Listbox items from SharePoint list
• How to use Power Apps List box control?