PowerApps Collection – How to create and use

In this PowerApps Tutorial, We will discuss What is PowerApps Collection and it’s Syntax.

Also, We will see these below topics that are related to PowerApps Collections as:

• How to create a collection in PowerApps
• Create PowerApps Collection on start
• PowerApps Collection on Button
• Display the collection in the PowerApps Gallery control
• How to delete each and all items from the PowerApps Collection
• What is the difference between Clear() and ClearCollect() method in PowerApps Collection
• What is Powerapps Collection Items Limit
• How to Overcome PowerApps Collection Items Limit
• Remove unwanted column from PowerApps Collections
• What are the PowerApps Collection Count and its types
• PowerApps Collection add column
• PowerApps Collection row number
• What are PowerApps Collection functions

What is a PowerApps Collection

• A Collection means a group of items or an Array. A Powerapps Collection means it is an array that helps to store the data in Powerapps memory. Later, You can use these stored data in many ways.
• Also, You can save the data in a PowerApps Collection to any data sources like SharePoint Online List, Excel spreadsheet, etc.
• You can create a PowerApps Collection on OnStart of App. So that the code will run each time when the app loads. Also, you can use a PowerApps Button control (on Button’s OnSelect property).

Read Create Collection from SharePoint List in PowerApps

PowerApps Collection Syntax

To create a PowerApps Collection, We can write the below syntax:

Collect(CollectionName, record(s))

Where,

• Collect = PowerApps Collect function helps add records to a data source
• CollectionName = You need to specify a collection name while creating the Powerapps Collection
• record(s) = The Record maybe we can take as a single value, an item, or a table.

To learn and get more details about to create the PowerApps Collection from SharePoint List, you can refer to this link: Create PowerApps Collection from SharePoint List

Want to know more details about PowerApps Collection, follow this link: PowerApps Collection: Add, Update, Remove and Filter items

Create PowerApps Collection on start

Let’s take a simple example that how you can create a PowerApps Collection on OnStart property.

• Suppose we have a selection of books that we want to add to a collection, then we would apply the below formula (you can apply this formula on App’s OnStart property):

Collect(BookCollection, {BookName: "The Testaments", Author: "Margaret Atwood"}, {BookName: "Cheque book", Author: "Vasdev Mohi"}, {BookName: "The Overstory", Author: "Richard Powers"})

Where,

1. BookCollection = Collection name
2. BookName, Author = These are the columns where the Book names and Authors will store

• Now Save, Publish, and then close the app. Again reopen or edit the app.
• Go to the View tab -> Collections -> Click on the BookCollection (as this name you provided in the above formula). When you will click on the collection name, you can see all the records or items as shown below.

Read Power Apps Calculate

PowerApps Collection on Button

Now, we will discuss how to create a PowerApps collection on Button. Lets explain this with a simple scenario.

• In this scenario, We will create a Powerapps collection (using Button click) that includes with below four columns as:

1. Employee Name
2. Employee ID
3. Employee Role
4. Employee Address

Follow these below steps that you need to do:

Step-1:

Open PowerApps.com and then Select Apps (from left navigation) -> + Create an app -> Select Canvas as like below.

Step-2:

Choose the Layout either Phone or Tablet under the Blank app template. Here, I have chosen the Tablet layout.

Step-3:

Once you will choose the Tablet layout, By default, PowerApps will add one blank new screen as shown below. In this screen, we are going to create a PowerApps Collection with a Button click.

Step-4:

Next, On the PowerApps blank new screen, Insert these below Powerapps input controls as:

1. Text input: Insert three text input controls for Employee Name, Employee Id, and Employee Address.
2. Dropdown: Add one Dropdown control for Employee Role.
3. Label: Add four Label controls for Employee Name, Employee Id, Employee Role, and Employee Address.
4. Button: Insert one Powerapps button where you will create the PowerApps Collections. You can set its Text property to “Create A Collection“.

Make sure to design all the controls and rename all the column name controls with a proper name like the below screenshot.

Step-5:

Next, we need to provide some Employee Roles (choice values) into the Dropdown control. Select the Items property of the dropdown control and apply this below formula:

Items = ["Developer", "Tester", "Lead", "Manager"]

1. “Developer”, “Tester”, “Lead”, “Manager” = These are the Dropdown choice values that I want to display in the Dropdown column.

Step-6:

Now it’s time to create the PowerApps collection on Button click. Select the Button (Create A Collection) and apply this below formula on its OnSelect property:

OnSelect = Collect(EmployeeDetails,{empName:empNametxt.Text, empId:empIdtxt.Text, empRole:empRoleset.Selected.Value, empAddress:empAddTxt.Text})

Where,

1. EmployeeDetails = Powerapps Collection name
2. empName, empId, empRole, empAddress = These are the columns that you want to store the values of Employee name, Employee Id, Employee Role, and Employee Address in the Powerapps collection
3. empNametxt, empIdtxt, empAddTxt = These are the text input control name of Employee name, Employee Id, and Employee Address.
4. empRoleset = This is the Employee Role Dropdown control name

Refer to this below screenshot.

Step-7:

That’s it for creating Powerapps Collection. Save and Preview (F5) the app. Enter the Employee Name, Employee Id, Select Employee Role, and Employee Address. Hit on the Create A Collection button as shown below.

Step-8:

Now the Powerapps Collection has been created once you clicked on the button control. To view the created collection, Go to View tab -> Collections -> Employee Details (Collection name). You can see all the employee details have been created like below.

Display PowerApps Collection using Gallery control

• Now we will see how we can display the created Powerapps Collection items in a Gallery control. By using the above controls (in the previous example), I have added some more employee details in the collection as you can see below.
• Next, I want to display all these created employee details in a Powerapps Gallery control from the Powerapps collection.

• At first, On the PowerApps screen, We need to add a Gallery control (Go to Insert tab -> Gallery -> Select Black vertical (you can choose any type of gallery control).

• As you can see in the below screenshot, the gallery control is not connected to any data source since it is not showing any data or information. To display the information, we need to provide the Data source to the gallery control.
• Select the Gallery control and go to the Properties pane. From the Items property, you can select your desired data source option.
• Similarly, if you want to change the gallery layout, then you can select it by using the Layout property as shown below.

• Select the Items property as “EmployeeDetails” (Collection name that you have created in the previous example) and it’s Layout as “Title and Subtitle“.
• Click on the Edit option from the Fields property that can help you to edit the Layout design, here we can set in the Title and Subtitle column that what we want to show in the gallery control.
• In the Subtitle, I have added “empName” and in the Title, I have added “empId“. So in the Gallery control, we can see only the “empName” and “empId” value as shown below.
• If you want to see the more details of the employee in the gallery control, then you can change the gallery layout and display the fields as per your choice.

Delete or Remove item from PowerApps Collection

• Suppose there are some unwanted records that you want to remove item from the PowerApps Collection. Then, in that case, you can remove those items by using the Powerapps gallery control itself. When you will remove the item from the gallery control, then that specific item will remove from the PowerApps collections automatically.
• To remove each item from the PowerApps collection, you need to add a Trash icon or a Button control. When you will click on the button, then the specific item will remove from the gallery control and as well as Powerapps Collections.
• There are two different ways that you can remove items from the PowerApps Collection. Such as:

1. Remove a Single item from PowerApps Collection
2. Remove all items from PowerApps Collection

1. Remove a Single item from PowerApps Collection

Suppose at a time, you want to remove or delete a single item individually, then follow this below process:

• Select the Powerapps Gallery control -> Click on the pencil icon near the upper-left corner -> Go to Insert tab -> Add a Button. Then the button control will be added to all the gallery control items. Rename the button to Delete.

• Next, Select the Button control and set its OnSelect property as:

OnSelect = Remove(EmployeeDetails,ThisItem)

Where,

1. Remove = This is the Powerapps function that helps to delete the item from the gallery control
2. EmployeeDetails = Powerapps Collection name
3. ThisItem = This method is used to delete that specific item from the gallery control

Refer to the below screenshot.

• Now save and preview the app. Click on the Delete button of those specific items that you want to delete or remove from the collections. you can see those specified item has been deleted from the Gallery control.
• Also, if you will go and check to the specific Powerapps collection, then you can see those items have been removed automatically from the Collection.

2. Remove all items from PowerApps Collection

Suppose you want to delete or remove all items at a time from the PowerApps Collection itself, then follow this below process:

• Insert a Button named Delete The Collection. Go to Button’s OnSelect property and apply this below formula as:

OnSelect = Clear(EmployeeDetails)

Where,

EmployeeDetails = PowerApps Collection name

• Save and Preview the app. Click on the Button (Delete The Collection). Go to the PowerApps Collection page. You can see, all the employee records have been deleted from the collection as the below screenshot.

ClearCollect method in PowerApps

• As we discussed what is the PowerApps Collection and how to create the PowerApps Collection using the Collect method. But you may think that, if we use the Collect method to create the Powerapps Collection, then again what is the need for the ClearCollect method in PowerApps Collection.
• Giving to the answer is, the PowerApps Collect method is used for creating the collections that will store our desired data. Whereas, the PowerApps ClearCollect method is used to delete the existing item from the collections and add the new item to the same PowerApps Collections. You can check this MSDN article for more information.
• Within a single method, we can do two operations at a time as:

1. We can clear the existing or previous records from the PowerApps Collections.
2. It can helps to add the new record to the same PowerApps Collections.

• To perform with this PowerApps ClearCollect method, Insert a Button control and set it’s OnSelect property as:

OnSelect = ClearCollect(EmployeeDetails,{empName:empNametxt.Text, empId:empIdtxt.Text,empRole:empRolesel.Selected.Value,empAddress:empAddTxt.Text})

• Now save and preview the app. Enter the employee details using the text input controls and click on the button (Create A Collection).
• Once you will click on the button, then the specific record will add in the gallery control and as well as in the PowerApps Collection as shown below.

• Similarly, again add the new or next record (should be different from the previous record) by using the controls and then click on the button (Create A Collection). Now, you can see the previous record has been deleted and the new record has been added to the Gallery control and as well as in the PowerApps Collections.

PowerApps Collection Items Limit

• In the Powerapps, there is a certain limitation of items in every data sources like SharePoint, OneDrive, Common Data Service, etc).
• You can only add 500 items from a data source but not more than that. If you will add more than 500, then the PowerApps won’t allow adding.
• Let’s take an example. Suppose you have 550-600 items in an Excel file on your OneDrive for Business. When you will add this data source into the app, then it will allow only up to 500 items but not much more than that.

How to Overcome PowerApps Collection Items Limit

To overcome this type of Power apps Items limit, you need to do some below things as:

1. Increase the total Item Limit in PowerApps App Settings:

• By using this below method, you can extend your Power apps item limits above 500.
• In the Power apps app, Click on file (top left corner) -> Settings -> Advanced settings -> set value for non-delegable queries as shown below.

• This method also is having one drawback. This method can allow only a maximum of 2000 number of items or values but not more than that. You can only add 1-2000 number in the Value field. Because Powerapps does not know about the 2001 item.
• So if there are more than 2000 items in your excel file, then this method will not work for you. For that, you can use this below the second method.

2. Use Static Data:

• The best solution for this item limit case is, use static data. In this method, there will be 10000 rows and you can have access to all rows.
• In this case, You need to import the static data instead of using SQL Database where you have to spend some extra money.
• These static data are attached to your app where there may be an effect in the app loading time due to the enlarging of the total size of the app.
• You can add the Static data by using the below steps:

1. View -> Data Sources -> Search the data source like Excel
2. Click on Import from Excel
3. Select excel file -> Choose a table that you want to import

If this method does not fulfill your needs, then remember one thing, Excel is a good method but you have to modify it from within the app.

3. OneDrive for Business Connectors:

• Suppose you may need more than 2000 rows stored in one data source, then you should switch from OneDrive for Business data source to any other data source like SQL, Common Data Service, SharePoint, etc.
• In this method, you can’t get more than 2000 rows.
• In the OneDrive for Business connector, there is a separate connection to each table in the Excel file. The item limit of 2000 value is related only to the connections but not to the source.

Remove column from PowerApps Collections

In this example, we will discuss how to remove some unwanted columns from the PowerApps Collections itself.

• The below screenshot represents a SharePoint list named Travel Details. This list has some columns with different data types like Single line of text, Choice, Date, etc. Among all these columns, I wanted to display some selected columns in the PowerApps Collections.

Here, I want to explain what is the exact problem occurring while you are creating a PowerApps Collections. I have a Button (Click to collect Travel Details) and its OnSelect property as appearing with the below formula as:

OnSelect = ClearCollect(TravelCollection, 'Travel Details')

Where,

1. TravelCollection = Powerapps Collection name
2. ‘Travel Details’ = SharePoint List name

• When you will click on the button (Click to collect Travel Details), then the collection will create. Go to the Powerapps Collection page and open your collection (TravelCollection).
• Here, you will see there are so many unwanted or unnecessary columns are appearing within the collection that we do not want. Some of the unnecessary columns are like Author, ComplianceAsId, Created, Editor, ID, etc.
• Instead of all these unwanted columns, I want only Title (Traveller), Source, Destination, and Travel Date column from the SharePoint list (Travel Details).

• To do this, select the Button (Click to collect Travel Details) and apply this below formula on its OnSelect property as:

OnSelect = ClearCollect(
    TravelCollection,
    ShowColumns(
        'Travel Details',
        "Title",
        "Source",
        "Destination",
        "TravelDate"
    )
)

Where,

1. ShowColumns = It is a Powerapps function that helps to include columns
2. ‘Travel Details’ = SharePoint list name
3. “Title“, “Source“, “Destination“, “TravelDate” = These are the SharePoint list columns that I want to display in the PowerApps Collection

• When you will click on the Button (Click to collect Travel Details) and open the PowerApps Collections (TravelCollection), then you can see it will appear with all desired columns (Title, Source, Destination, and Travel Date) as shown in the below screenshot.
• As the Source and Destination, both are the choice columns, so those choice values are appearing with a Box like structure.

PowerApps Collection Count

• PowerApps Count function helps to count all records in a table. Also, it counts all items that satisfy a condition.
• Below represents some Powerapps count functions and also it’s descriptions as:

1. Count: It counts the number of items in a single-column table.

Syntax:

Count( SingleColumnTable )

Where,

SingleColumnTable = Specify the column of records to count

2. CountA: It counts the number of items that are not blank in a table. This Powerapps CountA function includes an empty text (” “) in the count.

Syntax:

CountA( SingleColumnTable )

Where,

SingleColumnTable = Specify the column of records to count

3. CountIf: This is the Powerapps function that helps to counts the number of items in a table that are true for a logical formula.

Syntax:

CountIf( Table, LogicalFormula )

Where,

• Table = Specify the table of records to count
• LogicalFormula = Specify the formula to evaluate for each record of the table. Records that return true for this formula are counted.

4. CountRows: This CountRows function helps to count the number of items or records in a table.

Syntax:

CountRows( Table )

Where,

Table = Specify the table of records to count

NOTE:

All the above Powerapps Count functions (Count, CountA, CountIf, CountRows) always returns a number value.

Example:

• Suppose there is a Powerapps collection that contains some records. I want to calculate the number of records or items that are present inside the Powerapps collections.
• The below screenshot represents the PowerApps Collection named BookCollection. It has three different types of items. The number of records I want to show in a Label control.

• To do so, I have applied this below formula on Label’s Text property as:

Text = CountRows(BookCollection)

• Once you will apply this above formula, then the number of collection items will display in the label control as shown on the below screenshot.

PowerApps Collection add column

PowerApps AddColumns is a type of function that helps to add a column to a table or a collections and a formula defines the values in that column.

Syntax:

AddColumns( Table, ColumnName1, Formula1 [, ColumnName2, Formula2, ... ] )

Where,

1. Table = Specify the Table to operate on.
2. ColumnName(s) = It is Required. Provide the Name(s) of the column(s) to add. You must specify a string for this argument.
3. Formula(s) = Specify the Formula(s) to evaluate for each record. The result is added as the value of the corresponding new column.

Example:

I have two PowerApps Collections named Product Description Collection and Product Price Collection.

1. The Product Description Collection is having two columns as:

• ProductID
• ProductDescription

Below screenshot represents for the formula reference:

2. Similarly, the Product Price Collection is having two columns as:

• Productid
• ProductPrice

Refer to the below screenshot for applied Powerapps Collection formula:

• Now what I want to do is, I want to create an additional Column in Collection1 (Product Description Collection) i.e. “ProductPrice” and bring in the Value from ProductPrice in Collection2 (Product Price Collection) based on the lookup of Collection2.Id=Collection1.ID
• For that, Insert a Button and set its Text property to “Product Description and Price“.
• Select the Button (Product Description and Price) and apply this below formula on its OnSelect property as:

OnSelect = ClearCollect(
    ProductDescriptionAndPrice,
    AddColumns(
        ProductDescriptionCollection,
        "ProductPrice",
        LookUp(
            ProductPriceCollection,
            Productid = ProductDescriptionCollection[@ProductID],
            ProductPrice
        )
    )
)

Where,

1. ProductDescriptionAndPrice = This is the new collection name where you want to store the Product price value
2. ProductDescriptionCollection = Name of the Collection 1
3. “ProductPrice” = This is the new column that you added in the new collection (where the product price will store)
4. ProductPriceCollection = Name of the Collection 2
5. Productid = This is the id column from the Collection 2 (ProductPriceCollection)
6. @ProductID = This is the ID column from the Collection 1 (ProductDescriptionCollection)
7. ProductPrice = This is the column that present inside the Collection 2 (ProductPriceCollection)

• Now Save and Preview the app. Click on these Buttons serially: Product Description Collection -> Product Price Collection -> Product Description and Price.
• Go to the PowerApps Collections page -> Select the new Collection name (Product Description and Price). Once you will select the collection, you can see the Product Price column has been added to the collection as shown below.

PowerApps Collection row number

• Suppose you have a PowerApps Collection that you created from a SharePoint list data source. Now, you would like to add row numbers to each item, So that you can reference each item in the collection individually.
• One point I can tell you here is, By default, Every SharePoint list includes an ID field. This ID field is a unique row number that you can reference individually.
• If you want to make a new column with a unique row number, then you can check out this Powerapps Collection blog post: PowerApps Collection row number

PowerApps Collection functions

There are some Powerapps functions that you can use in the Powerapps Collection. Those functions are:

1. Collect:

• This Powerapps Collect function helps to add the records to a data source. The items can be a single value, a record, or a table.
• In case, if you need any additional columns, then you can create in the PowerApps Collection.
• To create the records in a data source, you can also use the PowerApps Patch function.
• Powerapps Collect function returns the modified data source as a table.

Syntax:

Collect(DataSource, Item(s))

2. Clear:

• This Powerapps Clear function helps to delete or remove all the items from a collection. It will delete only the items but the columns of the collection will remain the same as it is.
• One thing you must remember is, the Clear function only works on PowerApps Collections but not in other data sources. You can use the Remove function to remove the selected records.
• Also, you can use the RemoveIf function by specifying the conditions. e.g. RemoveIf(DataSource, true)
• There is no return value in the Clear function.

Syntax:

Clear (Collection)

Collection = You need to specify the collection name that you wants to clear.

3. ClearCollect:

• PowerApps ClearCollect function helps to delete all the items from the collection. After clearing all the items, again it adds a different new set of records to the same collection.
• As this function first Clears the items and then it Collects the items in the PowerApps Collection, that’s why it is known as PowerApps ClearCollect function.

Syntax:

ClearCollect( Collection, Item(s))

• Collection = This is the required option that you need to specify to clear and then add data into.
• Item(s) = Specify one or more records or tables to add to the data source.

NOTE:

All these above functions can not be delegated when used with a data source. The first portion of the data source will retrieve and then the function will apply.
Sometimes, a warning message will appear to remind you to increase the limitations and to suggest possible switching delegable alternatives.
To learn more about the PowerApps Delegation, follow this link: PowerApps Delegation

You may like following PowerApps tutorials:

• PowerApps Sum function (Get sum of a column PowerApps)
• PowerApps upload file to SharePoint Online document library using Microsoft Flow
• PowerApps Get Current User (ID, EMail, Department, Location, Photo, etc)
• PowerApps Email Attachment Control – How to Use
• Customize SharePoint online list forms like new, edit and display form using Microsoft PowerApps
• PowerApps Employee Engagement Survey Example
• How to Create Login Screen in PowerApps
• Microsoft PowerApps Radio Button Example
• PowerApps submit the form to SharePoint Online list
• How to Customize SharePoint Online List form using PowerApps
• Create your First form using Microsoft PowerApps in SharePoint Online
• How to add PowerApps app to Microsoft Teams
• PowerApps CheckBox – How to use

In this PowerApps Tutorial, We discussed What is PowerApps Collection and it’s Syntax.

Also, We learned with these below topics that are related to PowerApps Collections as:

• How to create a collection in PowerApps
• Create PowerApps Collection on start
• PowerApps Collection on Button
• Display the collection in the PowerApps Gallery control
• How to delete each and all items from the PowerApps Collection
• Difference between Clear() and ClearCollect() function in PowerApps Collection
• What is Powerapps Collection Items Limit
• How to Overcome PowerApps Collection Items Limit
• How to remove an unwanted column from PowerApps Collections
• What are the PowerApps Collection Count and its types
• PowerApps Collection add column
• PowerApps Collection row number
• What are PowerApps Collection functions