How to Filter Power Apps Collection?[Text/Number/Choice/Date/Person]

Power Apps filter collection is a most common requirement for all developers who are building various business applications. It’s very easy and tricky to use and filter the records based on your demands.

In this Power Apps tutorial, I will explain how to filter Power Apps collection with different criteria, such as:

  • Filter Power Apps collection with search text input
  • Filter Power Apps collection by choice column
  • Filter Power Apps collection by date
  • Filter Power Apps collection by person/group column
  • Filter Power Apps collection by yes no column
  • Filter Power Apps collection by logical operators

Filter Power Apps Collection With Search Text Input

Here, we will filter the Power Apps collection with multiple search text inputs.

I have a SharePoint list as Loan Approval with columns below:

Column NameData Type
NameTitle
Loan TypeChoice [“Car Loan“, “Gold Loan“, “Home Loan“]
Credit ScoreNumber
Salary Currency
Loan Applied DateDate and time
Approved or NotYes/no
ID ProofImage
Loan Handled byPerson/group
Filter Power Apps Collection With Search Text Input

There is a Search box and a Data table control. When a user searches for any name, loan type, Salary, etc., in the search box, the data table filters and displays the result based on the search box value.

Power Apps filter collection With Search Text Input
  • On the Power Apps Screen, insert the Text input control and Search icon as shown below:
Power Apps Collection filter with search Text Input
  • Set the Text input Padding-Left to 50 pixels.
How to filter Collection With Search Text Input in Power Apps
  • Write the code below on the Data table’s Items property as:
Filter(
    colApproval,
    StartsWith(
        Title,
        txt_Search.Text
    ) || StartsWith(
        'Loan Type'.Value,
        txt_Search
    ) || StartsWith(
        Salary,
        txt_Search.Text
    ) || StartsWith(
        'Loan Applied Date',
        txt_Search.Text
    ) || StartsWith(
        'Approved or Not',
        txt_Search.Text
    )
)

Where,

  1. colApproval – Collection name
  2. StartsWith – The function checks whether one text string begins with another
  3. Title – SharePoint list Column Name
  4. txt_Search – Text input control name
How to Filter the Power Apps Collection With Search Text Input

Power Apps Filter Collection By Single Search Text Input

The gallery filters and shows all the records according to user search input when a user searches or begins with any letter of any user.

Power Apps Filter Collection By Name with one text-input control
  • Apply the code below on Gallery’s Items property:
Filter(
    colApproval,
    StartsWith(
        Title,
        txt_UserName.Text
    )
)

Where,

  1. colApproval = Collection name
  2. Title = SharePoint list column name
  3. txt_UserName = Text-input control name
Power Apps Filter Collection Starts With

Power Apps Filter Collection By Number

When a user searches or starts with any first digit of Credit Score [Number column] and clicks the Search button, the gallery filters and displays all the records based on user search input.

Power Apps filter collection by number column using text-input starts with
  • Insert a Power Apps Text input as shown below:
Filter Power Apps collection by number column using text-input starts with
  • Add a Button control [Search] and set its OnSelect property to:
ClearCollect(
    colCreditScore,
    Filter(
        'Loan Approval',
        StartsWith(
            'Credit Score',
            txt_CreditScoree
        )
    )
)

Where,

  1. colCreditScore = Provide the collection name
  2. Loan Approval = SharePoint list name
  3. Credit Score = SharePoint list number column name
  4. txt_CreditScoree = Text-input control name
How to filter Power Apps collection by number column using text-input starts with
  • Set its Items property of the Gallery control as:
colCreditScore 

Where,

  1. colCreditScore = Collection name
Filter a Power Apps collection Starts With

This way, we can filter the Power Apps collection by number.

Filter Power Apps Collection By Choice Column

I will show you how to filter the Power Apps collection by choice column.

Example – 1:

When a user selects a Loan type [SharePoint Choice column] from the drop-down control and clicks the Search icon, the gallery will filter and display the relevant records based on the selected drop-down value.

How to Filter Power Apps Collection By Choice Column
  • Insert a Drop-down control and set its Items property to:
Choices('Loan Approval'.'Loan Type')

Where,

  1. Loan Approval: SharePoint list name
  2. Loan Type: SharePoint list choice column name
How to filter Power Apps collection by drop-down control from SharePoint list
  • Add a Search icon and set its OnSelect property as:
ClearCollect(
    coldrp,
    Filter(
        'Loan Approval',
        'Loan Type'.Value = drp_LoanType.Selected.Value
    )
)

Where,

  1. coldrp: Collection name
  2. drp_LoanType: Dropdown control name
Power Apps Filter collection by drop-down control SharePoint list
  • Insert a Gallery control and set its Items property:
coldrp
How to filter Power Apps collection by choice column SharePoint list

Example – 2:

Here, there is a Power Apps Data table control. This data table contains the SharePoint choice values [Loan Type]. It filters and displays the user choice value once the user opens the app.

Power Apps filter collection by choice column
  • Create a Power Apps collection on Screen’s OnVisible property:
ClearCollect(colFilter,'Loan Approval')

Where,

  1. colFilter = Collection name
  2. Loan Approval = SharePoint list name
Filter Power Apps collection by choice column value
  • Use the code below on the Power Apps Data table’s Items property:
Filter(
    colFilter,
    'Loan Type'.Value = "Gold Loan"
)

Where,

  1. Gold Loan = SharePoint list choice column value
How to filter Power Apps collection by choice column value

Power Apps Filter Collection Using Choice Column With AND Operator

Next, I will show you how to filter the Power Apps collection using the choice column value with the AND operator.

We will filter the data table from that collection and display the “Home Loan” records with credit scores over 700.

Filter Power Apps collection by choice column
  • Set the Data table’s Items property to the code below:
Filter(
    colFilter,
    'Loan Type'.Value = "Gold Loan" And 'Credit Score' > 700
)

Where,

  1. colFilter = Collection name
  2. Loan Type = SharePoint list choice column name
  3. Gold Loan = SharePoint list choice column value
  4. Credit Score = SharePoint list number column name
Power Apps filter collection using choice column value with AND feature

This way, we can filter the Power Apps collection by choice value.

Filter Power Apps Collection By Date

I will show you how to filter the collection by SharePoint date column in Power Apps.

In the SharePoint list [Loan Approval], there are two date columns:

Column NameData Type
Loan Start DateDate and time
Loan End DateDate and time
Power Apps filter collection by a date column

When a user selects a particular date from a date-picker control and clicks on the Search button, the gallery filters and displays the result based on user-selected date.

Power Apps Filter collection by date column SharePoint online list

Follow the below steps to achieve it:

  • On the Power Apps screen, Insert a Date picker control.
  • Insert a Button control and set its OnSelect property to:
ClearCollect(
    coldate,
    Filter(
        'Loan Approval',
        'Loan Applied Date' = Date(
            Year(dte_LoanStartDate.SelectedDate),
            Month(dte_LoanStartDate.SelectedDate),
            Day(dte_LoanStartDate.SelectedDate)
        )
    )
)

Where,

  1. coldate = Collection name
  2. dte_LoanAppliedDate = Date picker name
How to filter Power Apps collection by date column from SharePoint online list
  • Apply the code below on Gallery’s Items property:
coldate
Power Apps Filter collection by date column SharePoint list

Power Apps Filter Collection by Date Range

As in the image below, I have two Date picker controls:

  1. Loan Start Date
  2. Loan End Date

When the user selects a particular date from both the controls, the gallery will filter and display the specific records that fall between those selected dates of the date pickers.

Power Apps filter collection by date range

Let me show you how to achieve this:

  • On the Power Apps screen, insert two Date-Picker controls as shown below:
How to filter Power Apps collection by date range
  • Insert a Gallery control and set its Items property to:
Sort(
    If(
        IsBlank(dte_LoanApproval_EndDate.SelectedDate),
        If(
            IsBlank(dte_LoanApproval_StartDate),
            colLoanApproval,
            Filter(
                colLoanApproval,
                'Loan Start Date' >= dte_LoanApproval_StartDate.SelectedDate
            )
        ),
        If(
            IsBlank(dte_LoanApproval_StartDate),
            Filter(
                colLoanApproval,
                'Loan Start Date' <= dte_LoanApproval_EndDate.SelectedDate
            ),
            Filter(
                colLoanApproval,
                'Loan Start Date' <= dte_LoanApproval_EndDate.SelectedDate,
                'Loan Start Date' >= dte_LoanApproval_StartDate.SelectedDate
            )
        )
    ),
    'Loan Start Date',SortOrder.Descending
   )        

Where,

  1. dte_LoanApproval_EndDate = Power Apps 2nd date picker control name
  2. dte_LoanApproval_StartDate = Power Apps 1st date picker control name
  3. colLoanApproval = Collection name
  4. Loan Start Date = SharePoint list date column name
Filter Power Apps collection by date range

This is how we can filter the Power Apps collection by date range.

Power Apps Filter Collection By Today’s Date

I will show you how to filter the Power Apps collection by today or the current date.

Example:

Whenever the user clicks the arrow icon, the gallery filters and displays the records that started today using the SharePoint list date column [“Loan Start Date“] as shown below:

Power Apps Filter Collection Today By Date Column

Let me show you how to achieve this:

  • On the Power Apps screen, insert a Next arrow icon -> Set its OnSelect property to:
ClearCollect(
    colToday,
    Filter(
        'Loan Approval',
        'Loan Start Date' >= Today(),
        'Loan Start Date' < DateAdd(
            Today(),
            1,
            TimeUnit.Days
        )
    )
)

Where,

  1. colToday = Provide the Collection name
  2. Loan Approval = SharePoint list name
  3. Loan Start Date = SharePoint list date column name
How to filter Power Apps collection today by date column
  • Insert a Gallery control and set its Items property to:
ColToday

Where,

  1. colToday = Collection name
Filter Power Apps collection today by date column

Power Apps Filter Collection By Current Year

Next, we will discuss how to filter the Power Apps collection by the current year.

When the user clicks on the Check button, the Data table filters and displays the records that started this year and ended this year using the SharePoint list date columns [“Loan Start Date” and “Loan End Date“].

Power Apps Filter collection by a current year

Follow the below steps to achieve this:

  • On the Power Apps screen, insert a Button control -> Set its OnSelect property to:
ClearCollect(colYear,
With(
    {
        StartDate: Date(
            Year(Today())-4,
            Month(Today()),
            Day(Today())
        )+ 1,
        EndDate: Date(
            Year(Today()),
            Month(Today()),
            Day(Today())
        )
    },
    Filter(
        'Loan Approval',
        'Loan Start Date' >= StartDate,
        'Loan End Date' <= EndDate
    )
)
)

Where,

  1. colYear = Provide the Collection name
  2. Loan Approval = SharePoint list name
  3. Loan Start Date = SharePoint list date column name
  4. Loan End Date = SharePoint list date column name
Filter Power Apps collection by a current year
  • On the Power Apps screen, insert a Data Table -> Set its Items property to:
colYear

Where,

  1. colYear = Collection Name
How to filter the Power Apps collection by current year
  • Save, Publish, and Preview the app. Whenever the user clicks the button control, the gallery filters and displays the records that started in the current year and ended in the current year using the SharePoint list date columns.
Filter collection by a current year in Power Apps

This way, we can filter the Power Apps collection by date.

Filter Power Apps Collection By Person/Group Column

This section will explain how to filter the Power Apps collection by the Person/group column.

In the above SharePoint list [Loan Approval], there is a Person column called Loan Handled by.

The combobox is connected to the above SharePoint list person column. When a user selects a particular user from the combo box and clicks the Search button, the gallery displays all the details of the specific user.

Power Apps filter collection by person column
  • In the Power Apps app, connect the Office 365Users connector to the app.
Power Apps filter collection by person or group column using SharePoint list
  • Write the code below on Combobox’s Items property:
Office365Users.SearchUser({searchTerm:cmb_LoanHandledBy.SearchText,top:10})

cmb_LoanHandledBy = Combo box control name

Filter Power Apps collection by person column
  • On the Combo box control property pane, change the Fields layout to Person and enable the “Allow Searching.”
How to filter Power Apps collection by combo box control SharePoint list
  • Insert the Button control and set its OnSelect property:
ClearCollect(
    colUsers,
    Filter(
        'Loan Approval',
        'Loan Handled By'.DisplayName in cmb_LoanHandledBy.Selected.DisplayName
    )
)

Where.

  1. colUsers = Collection name
  2. cmb_LoanHandledBy = Combo box control name
How to filter Power Apps collection by person column
  • Insert a Data table -> Set its Items property to:
colUsers
Filter collection by person column in Power Apps

Power Apps Filter Collection By Current User

Here, I will explain how to filter the Power Apps collection by the current user.

Whenever the user loads the screen, the data table will filter and display the current user records as shown below:

Power Apps filter collection by a current user
  • Create a Power Apps Collection on screen’s OnVisible property:
ClearCollect(colCurrentUser,'Loan Approval')

Where,

  1. colCurrentUser = Provide the collection name
  2. Loan Approval = SharePoint list name
Filter collection by a current user in Power Apps
  • On the Power Apps screen, insert a Data table -> Set its Items property to:
Filter(
    colCurrentUser,
    'Loan Handled By'.Email = User().Email
)

Where,

  1. colCurrentUser = Collection name
  2. Loan Handled By = SharePoint list person column name
How to filter Power Apps collection by current user

This is how we can filter the Power Apps collection by the current user.

Power Apps Filter Collection Based On Sharepoint Multiselect Person

Here, let me show you how to filter the Power Apps collection on the multi-select person field in SharePoint.

I have a SharePoint list [IT Support Ticket] that has various columns like:

Column NameData Type
Issue IDTitle
Issue Single line of text
Issue TypeChoice [Laptop, Network, Outlook, Others]
Issue Handled ByPerson/group
Power Apps filter collection on multiselect person field in the SharePoint

In the SharePoint person field [Issue Handled By], you must select “Allows multiple selections.”

How to filter collection by current user in Power Apps

Whenever I select multiple users from the combo box control and click the search icon, the gallery will display the records based on the selected users.

Filter Power Apps collection on multiselect person field in SharePoint

Let me show you how to achieve this:

  • On the Power Apps screen, insert a Combo box control -> Connect the combo box to an Office365Users as shown above:
Office365Users.SearchUser({searchTerm: cmb_IssueHandledBy.SearchText}).DisplayName

Where,

  1. cmb_IssueHandledBy = Combo box control name
How to filter Power Apps collection on multiselect person field
  • Insert a Search icon -> Set its OnSelect property to:
ClearCollect(
    colSP,
    'IT Support Ticket'
);
Collect(
    colIssueHandledBy,
    Filter(
        colSP,
        User().Email in 'Issue Handled By'.Email
    )
)

Where,

  1. colSP = Provide the collection name for the SharePoint list
  2. IT Support Ticket = SharePoint list name
  3. colIssueHandledBy = Provide the collection name for the person column.
  4. Issue Handled By = SharePoint list person column name
Filter collection on multiselect person field in Power Apps
  • Insert a Gallery control -> Set its Items property to:
colIssueHandledBy

Where,

  1. colIssueHandledBy = Collection name
How to filter Power Apps collection on multiselect person field in SharePoint

This is how we can filter the Power Apps collection on a multi-select person field in SharePoint.

Filter Power Apps Collection By Yes No Column

Now, we will see how to filter the Power Apps collection by a SharePoint Yes No column.

In the same SharePoint list [Loan Approval], there is a Yes/no column called Approved or Not.

If a user toggles to Approved, the data table will filter and display all the approved record details, and if the user turns off the toggle, the data table will filter and display the records that are not approved.

How to Filter Power Apps Collection By Yes No Column

Let us see how to achieve it:

  • On the Power Apps screen, insert a Toggle control -> Set its OnChange property to:
ClearCollect(coltoggle,Filter('Loan Approval','Approved or Not'=true))

Where,

  1. coltoggle = New collection name
  2. Loan Approval = SharePoint list name
  3. Approved or Not = SharePoint list yes/no type column name
How to filter collection using Yes or No column in Power Apps
  • Now, insert a Data table -> Set its Items property to:
If(
    tgl_ApprovedOrNot.Value = true,
    coltoggle,
    Filter(
        'Loan Approval',
        'Approved or Not' = false
    )
)

Where,

  1. tgl_ApprovedOrNot = Toggle control name
  2. coltoggle = Collection name
How to Filter Power Apps Collection By Yes No Column
  • Save, Publish, and Preview the app. When the user provides a toggle control to yes, the data table will filter and provide particular data based on approved loans.
Power Apps filter collection using a SharePoint list yes or no column
  • Also, if the user provides a toggle control to NO, the data table will filter and display the records that are not approved.
Filter Collection By Yes No Column in Power Apps

Filter Power Apps Collection by Yes/No Column With AND Operator

Let me explain how to filter the Power Apps collection using the yes/no column with the AND operator.

The Power Apps gallery below will be filtered based on the collection and display the approved records using the yes/no column “Home Loan.”

Filter Power Apps collection using yes no column with the AND operator

Let me show you how to achieve this:

  • On the Power Apps Screen, OnVisible property -> Create a Power Apps collection using the above SharePoint list.
ClearCollect(colFilter,'Loan Approval')

Where,

  1. colFilter = Provide the collection name
  2. Loan Approval = SharePoint list name
How to filter Power Apps collection using yes no column with AND operator
  • Insert a Gallery control -and set its Items property to:
Filter(
    colFilter,
    'Approved or Not' = Boolean(1) And 'Loan Type'.Value = "Home Loan"
)

Where,

  1. colFilter = Collection name
  2. Approved or Not = SharePoint list yes/no column name
  3. Boolean(1) = This gives the true values from the SharePoint list yes/no column
  4. Loan Type = SharePoint list choice column name
  5. Home Loan = SharePoint list choice column value
Filter PowerApps Collection Using Yes No Column

This is how to filter the Power Apps collection using the yes/no column with the AND operator.

Filter Power Apps Collection By Logical Operators

Here, I will show you how to filter a Power Apps collection by using logical operators like:

Logical OperatorDescription
AND operatorIf both controls are true then it gives a result. [Search with multiple values]
OR operatorIf any one control is true then it gives a result. [Search with single value]
Power Apps filter collection by logical operator from SharePoint online list

Let me show you how to filter the Power Apps collection by using both logical operators:

Power Apps filter collection by AND operator

Let’s say I selected a value as “Car Loan” from the drop-down control and provided a text input as “460” of Credit score. Then click on a search button.

Later, it displays the data table based on all the records of the Home Loan, which has a credit score of “460”.

Power Apps filter collection by AND operator from SharePoint list

Let us see how to achieve it:

  • On the Power Apps screen, Insert a Drop-down control -> Set its Items property to:
Choices('Loan Approval'.'Loan Type')
How to filter Power Apps collection by AND operator from SharePoint list
  • Besides a drop-down control, Insert a Text-input control.
Power Apps filter collection by AND operator SharePoint list
  • Insert a Search icon -> Set its OnSelect property to:
ClearCollect(
    coloperator,
    Filter(
        'Loan Approval',
        'Loan Type'.Value = drp_LoanType.Selected.Value && 'Credit Score' = txt_CreditScore.Text
    )
)

Where,

  1. coloperator = Collection Name
  2. Loan Approval = SharePoint list name
  3. Loan Type = SharePoint list column name
  4. drp_LoanType = Drop-down control name
  5. && = AND Operator
  6. Credit Score = Another SharePoint list name.
  7. txt_CreditScore = Text-input control name.
How to filter Power Apps collection by logical operator SharePoint list
  • Insert a Data table -> Set its Items property to:
coloperator
How to filter Power Apps collection by AND operator SharePoint list

This is how we can achieve this by filtering the Power Apps collection by using an AND operator from the SharePoint list.

Power Apps filter collection by OR operator

Here, we will see how to filter collection by OR operator from a SharePoint list.

Let’s say I selected a value as “Home Loan” from the drop-down control and provided a text input as “567” of Credit score. Then click on a search button. Later, it displays the data table containing “Home Loan,” but the credit score of 567 was not contained.

Power Apps filter collection by OR operator SharePoint list

To achieve it, follow the same above procedures but in the Search icon -> Set its OnSelect property to:

ClearCollect(
    coloperator,
    Filter(
        'Loan Approval',
        'Loan Type'.Value = drp_LoanType.Selected.Value || 'Credit Score' = txt_CreditScore.Text
    )
)

Where,

  1. coloperator = Collection Name
  2. Loan Approval = SharePoint list name
  3. Loan Type = SharePoint list column name
  4. drp_LoanType = Drop-down control name
  5. || = OR Operator
  6. Credit Score = Another SharePoint list name
  7. txt_CreditScore = Text-input control name
How to filter Power Apps collection by OR operator SharePoint list

This is how we can filter the Power Apps collection using the OR operator.

Conclusion

I hope this article helped you a lot. In this article, we learned everything about how to filter Power Apps collection, like filtering Power Apps collection based on text input, Power Apps filter collection by number, how to filter Power Apps collection by date, and many more.

Some more Power Apps articles you may also like: