Power Apps collection filter [With real examples]

This Power Apps tutorial will walk you through several examples of how to filter the Power Apps collection based on various scenarios.

Recently, we encountered a requirement while working with the Power Apps Canvas app to filter a collection within the Power Apps.

It will also cover how to filter the Power Apps collection, which contains the following items:

  • Power Apps collection filter SharePoint list
  • Power Apps collection filter contains
  • Power Apps collection filter with OR
  • Power Apps collection filter with AND
  • Power Apps collection filter by date
  • Power Apps collection filter and sort
  • Power Apps collection filter by user
  • Power Apps collection filter by current user
  • Power Apps collection filter by dropdown
  • Power Apps filter collection by another collection
  • Power Apps collection filter year
  • PowerApps filter not in collection
  • Power Apps collection filter multiple condition

How to filter a Power Apps collection using the SharePoint list

In this section, we will see how to filter a Power Apps collection that builds from the SharePoint list based on a certain condition.

Suppose, we have a SharePoint list having various types of columns including random data shown below:

Power Apps collection filter SharePoint list
Power Apps collection filter SharePoint list

Let’s use the above SharePoint list to create a Power Apps collection, and then filter the collection to display only data whose “Product type is Laptop.” To achieve this requirement, the following steps are necessary:

  • On the Power Apps screen, add a button control and set the Text property as Create Collection.
  • Insert the below expression on the OnSelect property to create a collection using the above Sharepoint list.
OnSelect = Collect(ProductColl, 'Product Model')

where ProductColl is the name of the new collection to store the retrieved data from the SharePoint list, and “Product Model” is the name of the SharePoint list.

Next, add a data table control to the Power Apps screen, connect the data table to the collection as a data source, and add the fields to display the collected data.

When we click the button, the collected data appears in the Power Apps data table shown below:

PowerApps collection filter SharePoint list
PowerApps collection filter SharePoint list
  • Now, we will filter this above collection that will collect only those data whose Product type is Laptop. For this, insert another button control to the Power Apps screen and set the Text as Collect Filter Data.
  • Add the mentioned expression on the button’s OnSelect property. As a result, when the user clicks on the button, a collection will create based on the condition.
OnSelect = ClearCollect(ProductColl, Filter('Product Model', Product = "Laptop"))

Where,

  • ProductColl is the name of the existing collection that we have created.
  • ‘Product Model’ is the name of the SharePoint data source list.
  • Product is the name of the
Power Apps collection filter SharePoint
Power Apps collection filter SharePoint

When we click the button, we can see that the filtered collected data will be created within the collection.

To display the collected data, add a data table control to the screen. Connect it with the collection, i.e., ProductColl, and add the fields as well.

Filter a PowerApps collection from SharePoint
Filter a PowerApps collection from SharePoint

This is how to filter a PowerApps collection from the SharePoint list.

Read Power Apps Create Collection Using Excel

Power Apps collection filter contains

In this section, we will see how to build a Power Apps collection that will filter the items if they contain some specific word or text.

The Power Apps covers a range of built-in functions and operators. Unfortunately, developers and consultants with relevant programming experience are familiar with some functions that may be missing from Canvas apps. One such function is Contains().

However, the in and exactin operators serve the same purpose as contains(). Suppose, using the above SharePoint list, we are going to create a filtered collection that will store only those data whose Title contains the specific text (Ex-Apple)

To achieve this need, the following steps are:

  • On the Power Apps screen, add a button control and set a Text property to the button.
  • Insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Filter('Product Model', "Apple" in Title))

where ProductColl is the name of the existing collection that we have created to store the data from the SharePoint list. “Product Model” is the name of the SharePoint list, and “Title” is the name of the SharePoint column.

PowerApps collection filter contains
PowerApps collection filter contains

Let’s click on the button while clicking on the Alt key. Then add a data table and connect it with the ProductColl collection. Also, add the fields to display the collected and filtered data as shown below:

Filter Power Apps collection with contains
Filter Power Apps collection contains

This is how to filter a Power Apps collection that contains a specific text.

See also  Code View hyperlink rewritten: Send an email (V2) action Dynamic Content in Power Automate

Power Apps collection filter with OR

In this section, we’ll look at how to use the OR operator to work with the Power Apps collection filter. That is, we will create a Power Apps collection that will display data by filtering the SharePoint list with the Product value of the Laptop or Mouse.

To work with this requirement we are going to use the above SharePoint list i.e., Product Model, and the following steps are:

  • On the Power Apps screen, add a button control and give the Text to the button as per your need. (Ex- Mouse OR Laptop)
  • Insert the below expression on the button’s OnSelect property. So that the collection will create, once the button is clicked.
OnSelect = ClearCollect(ProductColl, Filter('Product Model', Product = "Laptop" || Product = "Mouse"  )) 

Where ProductColl is the name of the existing collection, ‘Product Model’ is the name of the SharePoint list, Product is the column name, and “Laptop” and “Mouse” are the column values based on which the collection will filter.

Power Apps collection filter with OR
Power Apps collection filter with OR

Let’s press the button control to create the filtered collection. Add a data table control to the Power Apps screen to display the collected data.

Connect the data table to the collection, i.e., ProductColl, and add the fields to it.

PowerApps collection filter with OR
PowerApps collection filter with OR

This is how to filter a Power Apps collection with OR operator.

Read Power Apps Create Collection Using SharePoint List

Power Apps collection filter with AND

Similarly, in this section, we will see how to use the AND operator to filter a Power Apps collection. For this, we are going to use the above SharePoint list and the collection that we have created i.e., ProductColl.

Assume we are going to create a collection that will only display data that has the word “Apple” in the product title and the product value is equal to Mobile.

For this, the following steps are:

  • Add a button control to the Power Apps screen.
  • Insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Filter('Product Model', Product = "Mobile" && "Apple" in Title  ))
  • Next, add a data table control to display the collected data. Connect it with the ProductColl collection and add the respective fields to the data table.
  • Once the button is clicked, we can see the corresponding filtered data will display on the data table.
Power Apps collection filter with AND
Power Apps collection filter with AND

We can see the above data table will display will one data as it satisfied the filter condition. This is how to filter a Power Apps collection with AND operator.

Power Apps collection filter by date

Do you ever filter a Power Apps collection by date? If not, then follow this example where we will show how to filter a Power Apps collection based on date.

We are going to use the above-mentioned collection, where we will filter the data based on date value. That means the collection will display only those records whose Purchase date is smaller than a specific date. (Ex- 11/10/2022). The following steps must be taken to accomplish this:

  • Add a button control to the Power Apps screen.
  • Insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Filter('Product Model', 'Purchase Date' < Date(2022,11,10) ))

Where ‘Purchase Date’ is the name of the date column of the SharePoint data source.

Power Apps collection filter by date
Power Apps collection filter by date
  • Add a data table control to the screen and connect it with the collection i.e., ProductColl. Once the button is clicked, the filtered data will display on the data table as shown below:
PowerApps collection filter by date
PowerApps collection filter by date

On the above data table, we can see that it only displays those records whose purchase date falls before 10/11/2022 (mm/dd/yyyy). This is how to filter a Power Apps collection by date.

Read Power Apps Timer Control Examples

Power Apps collection filter and sort

Here, we will see how to sort filtered data within the Power Apps collection. Let’s take the above collection, where we will sort the purchase date in descending order.

To implement this need, the following steps are:

  • Add a button control to the screen.
  • Set the Text property (Ex- Filter and Sort By Date).
  • Insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Sort(Filter('Product Model', 'Purchase Date' < Date(2022,11,10) ),'Purchase Date',Descending))
Power Apps collection filter and sort
Power Apps collection filter and sort

To display the filtered data in sorting order, add a data table control to the Power Apps screen. Connect the data table with the collection, i.e., ProductColl, and add the fields to the data table.

See also  Power bi create a date table - Complete tutorial

Next, click on the button while clicking on the Alt key.

PowerApps collection filter and sort
PowerApps collection filter and sort

This is how to filter and sort within the Power Apps collection.

Power Apps collection filter by user or current user

Let’s see how to filter a Power Apps collection by the current user. That means the collection will be filtered and displayed based on the current user.

Within the Power Apps collection (i.e., ProductColl) that was created from the previously mentioned SharePoint list, there is a people column. We will use that collection to filter the data so that it only displays where the people column, i.e., ‘OrderBy,’ equals the current user.

For this, the following steps are:

  • On the Power Apps screen, add a button control.
  • Give a text to the button.
  • Insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Filter('Product Model','Ordered By'.Email = User().Email))
Power Apps collection filter by user
Power Apps collection filter by user
  • Add a data table and connect that table with the collection i.e., ProductColl. Add the fields to display the filtered data.
Power Apps collection filter by current user
Power Apps collection filter by the current user

Because the current user is “Sonam Subhadarsini,” it is only displaying data relevant to that user.

This is how to filter a Power Apps collection by the current user.

Read Power Apps Notify() function

Power Apps collection filter by dropdown

In this section, we will see how to filter a Power Apps collection by dropdown. That means the Power Apps collection will be filtered based on the dropdown selection.

Assume we’re going to use the above collection to create a dropdown control that displays all of the tenants’ users. The Power Apps collection will be filtered based on the dropdown selection. To work with the scenarios, the following steps are:

  • Connect the canvas app with the Office356Users.
  • Add a dropdown control, to the Power Apps screen.
  • Insert the below expression on the dropdown’s Items property to display all the users.
Items = Office365Users.SearchUser()
  • Next, add a button control to the Power Apps screen.
  • Insert the below expression into the button’s OnSelect property.
OnSelect = ClearCollect(ProductColl, Filter('Product Model','Ordered By'.DisplayName = Users_dropdown.Selected.DisplayName))

Where Users_dropdown is the dropdown control’s name.

Power Apps collection filter by dropdown
Power Apps collection filter by dropdown

Next, add a data table control and connect it with the collection i.e., ProductColl. Also, add the fields to the data table.

Let’s select a user’s name from the dropdown control and click on the button. We can see the filtered data will be visible on the Power Apps data table as shown below: (Ex- Sonam Subhadarsini)

PowerApps collection filter by dropdown
PowerApps collection filter by dropdown

This is how to filter a Power Apps collection via a drop-down control.

Power Apps filter collection by another collection

In this section, we’ll look at how to use Power Apps to filter a collection by another collection. Assume we have two collections and want to make another collection out of them. This new collection will only show items that are shared by both collections.

For this, the following steps are:

  • On the Power Apps screen, we have created two collections on the buttons’ OnSelect property such as CollProduct1 and CollProduct2.
OnSelect = ClearCollect(CollProduct1, {Product1:"Laptop"},{Product1:"Mouse"},{Product1:"Desktop"},{Product1:"Office Chair"},{Product1:"Keyboard"})   // Button1

OnSelect = ClearCollect(CollProduct2, {Product2:"Laptop"},{Product2:"Desktop"},{Product2:"Coffee Mug"},{Product2:"Mouse"})     //Button2
Power Apps filter collection by another collection
Power Apps filter collection by another collection
  • On the above screen, we have added 2 data tables and connected them to the collections respectively to display the collected data.
  • Add another button control to the Power Apps screen, and insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(CollFinal,Filter(CollProduct1,Product1 in CollProduct2.Product2).Product1)

Where CollFinal is the name of the new collection to store the filtered data.

PowerApps filter collection by another collection
PowerApps filter collection by another collection

Add a data table and connect that control with the CollFinal collection. Also, add the field to display the collected filter data.

Filter PowerApps collection by another collection
Filter PowerApps collection with another collection

This is how to use another collection to filter the PowerApps collection.

Read Power Apps Filter With Date Picker

Power Apps collection filter year

In this section, we will see how to filter a Power Apps collection by year. Suppose, we have a SharePoint list named Workers having a people column and a date column. We will create a collection using that SharePoint list and also, and we will build a dropdown that will display the list of years.

When the user selects any year from the dropdown control, the collection will filter based on the selected value. To work with the scenarios, the following steps are:

  • On the Power Apps screen, add a button control.
  • Give a name to the button control (Text: Create Collection)
  • Insert the below expression to create the collection.
OnSelect = Collect(Colworkers, Workers)

Where Colworkers is the name of the new collection and Workers is the name of the SharePoint data source.

Power Apps collection filter year
Power Apps collection filter year
  • Add a dropdown control to the Power Apps screen and insert the Items such as:
Items = [2018, 2019, 2020, 2021, 2022]
  • Add another button control to the screen and insert the below expression on the button’s OnSelect property.
OnSelect = ClearCollect(Colworkers, Filter(Workers, Year(JoinDate) = Year_Dropdown.Selected.Value))

where Colworkers is the name of the existing collection, JoinDate is the name of the date column, and Year_Dropdown is the name of the dropdown control name.

PowerApps collection filter year
PowerApps collection filter year

Next, add a data table control and connect it with the Colworkers collection, and add the fields to display the filter data.

See also  How to Display Dataverse Choices in Power Apps Gallery

First, click on the Create Collection button, then select a year from the dropdown control(ex: 2020), and finally click on the Filtered Data button to show the data on the Power Apps data table as shown below:

PowerApps collection filter by year
PowerApps collection filter by year

This is how to filter a Power Apps collection by year.

Power Apps filter not in collection

In this section, we’ll look at how to use the Power Apps filter, not in the collection. That is, there are two collections from which we will extract the items that are not in the other collection.

Suppose, we have created two collections (i.e., CollTimeSlot and CollBookedSlot.) by inserting the below expressions on the buttons’ OnSelect properties.

  • Insert the below expression in the first button’s OnSelect property:
OnSelect = ClearCollect(CollTimeSlot, {Time:"10:00 AM"}, {Time:"10:30 AM"}, {Time:"11:10 AM"}, {Time:"11:35 AM"}, {Time:"13:15 PM"},{Time:"13:40 PM"}, {Time:"15:20 PM"})  

Where CollTimeSlot is the name of the new collection and Time is the name of the collection header.

  • Similarly, insert the below expression on the second button’s OnSelect property.
OnSelect = ClearCollect(CollBookedSlot, {Slot:"15:20 PM"}, {Slot:"10:30 AM"},{Slot:"11:35 AM"})

Where CollBookedSlot is the name of the new collection and Slot is the name of the collection header.

  • Add two galleries to the Power Apps screen to display the respective collections data.
powerapps filter not in collection
PowerApps filter not in collection
  • Add a button control to the Power Apps screen and insert the below formula on the button’s OnSelect property to collect the filter data.
OnSelect = ClearCollect(CollRestSlot, Filter(CollTimeSlot, !(Time in CollBookedSlot.Slot)))

where CollRestSlot is the name of the new collection to store the filtered data.

  • Add a gallery control to the screen and connect that control with the collection i.e., CollRestSlot.
  • Once the button is clicked, we can see the rest time slots will be visible on the Power Apps gallery.
Power Apps filter not in collection
Power Apps filter not in collection

This is how to filter a Power Apps collection if that is not in another collection.

Read How To Set Default Date in Power Apps Date Picker

Power Apps collection filter multiple conditions

In this example, we will see how to filter a Power Apps collection using multiple conditions. That means we will filter a Power Apps collection based on multiple criteria.

Let’s take the above example of the SharePoint list(i.e., Product Model) and build a collection using that SharePoint data source. Then we will filter that collection based on multiple conditions such as the collection will display only those data whose product value is equal to Laptop or Mouse, and the purchase date should be less than 10th November 2022.

For this, the following steps are:

  • On the Power Apps screen, add a button control and insert the below expression on the OnSelect property to build the collection.
OnSelect = Collect(ProductModelCol, 'Product Model')

Where ProductModelCol is the name of the new collection to store the SharePoint data. Once the button is clicked the data will be stored in the collection.

  • Again add another button control and insert the below expression on the OnSelect property to store the filtered data with multiple conditions.
OnSelect = ClearCollect(ProductModelCol, Filter('Product Model', Product = "Mouse" Or Product = "Laptop"  And  'Purchase Date' < Date(2022,11,10)))
Power Apps collection filter multiple condition
Power Apps collection filter multiple conditions
  • Add a data table control to the Power Apps screen and connect it with the collection i.e., ProductModelCol.
PowerApps collection filter multiple condition
PowerApps collection filter multiple condition

This is how to filter a Power Apps collection using multiple conditions.

Conclusion

From this Power Apps Tutorial, we learned how to filter a Power Apps collection. Also, we have covered below topics such as:

  • Power Apps collection filter SharePoint list
  • Power Apps collection filter contains
  • Power Apps collection filter with OR
  • Power Apps collection filter with AND
  • Power Apps collection filter by date
  • Power Apps collection filter and sort
  • Power Apps collection filter by user
  • Power Apps collection filter by the current user
  • Power Apps collection filter by dropdown
  • Power Apps filter collection by another collection
  • Power Apps collection filter year
  • PowerApps filter not in the collection
  • Power Apps collection filters multiple conditions

You may like the following Power Apps tutorials:

>