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.

Power Apps Create Collection From 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.

PowerApps Create Collection From Excel

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.

powerapps import data from excel

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

power apps import data from excel

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

power apps import data from excel sheet

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
power apps excel import

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

excel to power apps

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

powerapps collection to excel

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

power apps collection to excel

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

See also  Power Apps Modern Badge Control [Complete Guide]

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.

show specific columns in power apps collection from excel

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
show specific columns in powerapps collection from 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.

import data from excel in powerapps

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.

powerapps gallery to excel

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

Items = colShowFinancials

Where,

See also  Power Apps Modern Badge Control [Complete Guide]

colShowFinancials = Collection name

Display Power Apps Collection Records from Excel in Gallery

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

Text = "Product: " & ThisItem.Product

Where,

Product = Collection Header

power apps gallery to excel

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:

  • >