Power Apps Create Collection From Excel

Do you know how to create a Power Apps Collection from an Excel sheet? Ok, no problem!

This Power Apps tutorial uses a simple scenario to help you work with Power Apps Create Collection From Excel. We will also learn the syntax for creating a Power Apps collection from Excel.

Moreover, we will learn the following topics:

• How to show specific columns in the Power Apps collection from Excel
• Display Power Apps Collection Records from Excel in Gallery

Syntax to Create Power Apps Collection From Excel

The syntax below represents how to create a Power Apps Collection from an Excel sheet:

Collect(<Collection Name>, <ExcelSheetName>)

Where,

1. Collection Name = Provide the Collection name
2. ExcelSheetName = Provide the Excel Sheet name

This is the syntax to create a Power Apps Collection from an Excel sheet.

Power Apps Create Collection From Excel

Let’s implement it step by step to create a Power Apps collection from Excel.

1. I have an Excel sheet named Financial Sheet. This Excel has these many columns/headers below:

Columns/Headers
Segment
Country
Product
Units Sold
Manufacturing
Sale Price
Gross Sales

Also, the records below are presented in Excel.

2. Select the Excel records and format them as a table. Go to the Home tab -> Expand Format as Table -> Select the table design.

3. Now, we will create a Power Apps blank canvas app. Connect the Excel data source connector to the app. Go to the Data tab [from the left nav] -> Add data -> Search and select Import from Excel as shown below.

4. Select the Excel sheet from the system and click on Open. Choose the table [financials] and click on Connect.

Now, the Excel sheet has been connected to the app.

5. Insert a Button control [+ Insert -> Button] and set its OnSelect property to the code below: [Not only Button’s OnSelect property but also, you can add the code below to either Screen’s OnVisible or App’s OnStart properties]

OnSelect = ClearCollect(
    colFinancialDetails,
    financials
)

Where,

• colFinancialDetails = Provide the collection name
• financials = Specify the Excel table name

6. Save, publish, and preview the app. Tap the CREATE button. Now, the new collection has been created from the Excel.

7. To view the collection records, go to Variables (X) -> Expand Collections -> Select … of the specific collection -> Select View Table.

8. Here, you can view all the Excel records in the collection, as in the screenshot below.

This is how to work with Power Apps Create Collection from Excel.

Show Specific Columns in Power Apps Collection from Excel

Suppose in Power Apps Collection, you want to display some particular columns from Excel, then, in this case, follow the example below.

1. Here, there will be a Power Apps button control. Once the user clicks on it, the collection will be created from the Excel, and it will display only some necessary columns as per the user’s needs.

2. To work around this, select the Button [CREATE] and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colShowFinancials,
    ShowColumns(
        financials,
        "Product",
        "Gross Sales",
        "Sale Price"
    )
)

Where,

• colShowFinancials = Collection name
• financials = Provide the Excel table name
• “Product“, “Gross Sales“, “Sale Price” = Columns that I need from the Excel

3. Save, publish, and preview the app. Once you click on the button, the collection will be created from Excel. To view the result, go to Variables (X) -> Expand Collections -> Select … of the specific collection -> Select View Table.

All of the Excel records [with specific columns] in the collection are viewable as shown in the screenshot below.

This is how to show specific columns in the Power Apps Collection from Excel.

Display Power Apps Collection Records from Excel in Gallery

Next, we will discuss how to display all the collection records from Excel in a Power Apps Gallery control.

1. The image below represents a Button control and a Gallery control. When the user presses the button, the gallery will display all the collection records from the Excel sheet.

2. To do so, select the gallery and set its Items property as:

Items = colShowFinancials

Where,

colShowFinancials = Collection name

3. Then, select the Text label control from the gallery and set its Text property as:

Text = "Product: " & ThisItem.Product

Where,

Product = Collection Header

4. Save, publish, and preview the app. You can see all the collection records in the gallery.

This way, we can display Power Apps Collection records from Excel in a gallery.

Conclusion

We learned how to use Power Apps Create Collection From Excel with a basic example from this Power Apps tutorial. We also understood the syntax for importing an Excel file into a Power Apps collection and many more like:

• Show specific columns in the Power Apps collection from Excel
• How to display Power Apps Collection Records from Excel in Gallery

Additionally, you may like some more Power Apps tutorials:

• Power Apps Collection Distinct Filter
• Add Image to a Power Apps Collection
• Filter a Collection Based On Another Collection in Power Apps
• Add Gallery Data to a Collection in Power Apps
• Power Apps Save Collection to SharePoint List