Excel-like filtering for text fields

As has happened multiple times in the past, this was another question that was asked on the Power Apps community. The question was if it was possible to implement excel-like filtering capabilities in Power Apps.

I opened excel and looked at all the filtering capabilities for text fields. They are:

• Equals
• Does Not Equal
• Begins With
• Ends With
• Contains
• Does Not Contain

I created a gallery of users, added a button to reflect the filtering type, and then added the following filtering logic to the Items property of the gallery:

• Equals:

  (filterType = "equals" && 'Full Name' = TextInput_FullNameFilter.Text)

• Does Not Equal:

  (filterType = "doesnotequal" && !('Full Name' = TextInput_FullNameFilter.Text)

• Begins With:

  filterType = "beginswith" && StartsWith('Full Name'', TextInput_FullNameFilter.Text)

• Ends With:

  filterType = "endswith" && EndsWith('Full Name', TextInput_FullNameFilter.Text)

• Contains:

  (filterType = "contains" && TextInput_FullNameFilter.Text in 'Full Name')

• Does Not Contain:

  (filterType = "doesnotcontain" && !(TextInput_FullNameFilter.Text in 'Full Name'))

Combining all of these, the Items property looked like this (I also added a condition to allow the user to not filter and show all the records):

If(
    ShowAll,
    Users,
    Filter(
        Users,
        (filterType = "beginswith" && StartsWith(
            'Full Name',
            TextInput_FullNameFilter.Text
        )) || 
        (filterType = "endswith" && EndsWith(
            'Full Name',
            TextInput_FullNameFilter.Text
        )) || 
        (filterType = "contains" && TextInput_FullNameFilter.Text in 'Full Name') || 
        (filterType = "doesnotcontain" && !(TextInput_FullNameFilter.Text in 'Full Name')) || 
        (filterType = "equals" && 'Full Name' = TextInput_FullNameFilter.Text) || 
        (filterType = "doesnotequal" && !('Full Name' = TextInput_FullNameFilter.Text))
    )
)

Here is how this behaves:

Hope this helps and inspires others to come up with excel-like filtering for other data types!

Have fun! Get addicted!