A common requirement in canvas app development is to use a gallery to show items in a SharePoint list. It’s unlikely we’ll want to overwhelm users with all the data, so will give the option to filter results. This can be in the form of a single filter option or combining multiple filters at once. Either way, we’ll need some Power Apps filter magic to help us.
In this article, I’ll cover the Power Fx needed to filter gallery items using any SharePoint column type. This will include how we can filter galleries using multiple filters.
- Scenario
- Single line of text
- Multiple lines of text
- Number
- Date
- Choice
- Person or Group
- Yes/No
- Lookup
- Managed Metadata
- Hyperlink
- Rating / Likes
- Location
- Created By / Modified By
- Combining filter criteria
Scenario
I have a SharePoint list with lots of records. I’d like users of a Power App to have a multitude of filters available, so they can dissect the data to their needs. Where possible, each filter option is provided as a dedicated Power Apps control (ie text input, combo box) to avoid hardcoding entries:
Bonus tip: If you need to replicate any of the list configuration to use in filterable controls, try this:
1- Add a form control to your app. Bind it to the SharePoint list.
2- Add in any columns you need configuration for.
3- Select any control and view its Items property.
4- Copy the config to the Items property of your own combo box/dropdown controls.
Each example of Power Fx in this article covers two things:
1- filtering the data by the given control value, OR
2- still showing data even if the control value is blank
This invariably provides a nicer user experience of showing all the data until filtered clauses are matched, rather than showing nothing until there’s a match.
Example of the Power Apps filter technique used, where I’m using 2 pipes to indicate the Or operator:
Filter(MySharePointList, SingleLineOfTextColumn = txtTextSearch.Text || IsBlank(txtTextSearch.Text))You can choose to use the Or function if you’d prefer:
Filter(
MySharePointList,
Or(
SingleLineOfTextColumn = txtTextSearch.Text,
IsBlank(txtTextSearch.Text)
)
)Finally, where possible I try to mention whether a technique is delegable. If you’re not familiar with Power Apps and delegation, this will be a good place to start.
Single line of text
We can use the output of a Text Input control to search for text in Single Line of Text columns in SharePoint. Using equals as the operator, only exact matches will return.
// Single line of text - exact text match in single line of text column
Filter(
MySharePointList,
SingleLineOfTextColumn = txtTextSearch.Text || IsBlank(txtTextSearch.Text)
)We can use the in operator to find if the text entered appears anywhere in a Single Line of Text column. This works, but isn’t delegable so won’t work for large data sets.
// Single line of text - text appears anywhere in single line of text column
Filter(
MySharePointList,
txtTextSearch.Text in SingleLineOfTextColumn || IsBlank(txtTextSearch.Text)
)A Power Apps filter technique that’s more delegable-friendly is to use the StartsWith function instead. This will yield results quicker than an exact match too.
Bonus tip: update the DelayInput property of the Text Input control to true for a better user experience.
// Single line of text - using StartsWith
Filter(
MySharePointList,
StartsWith(
SingleLineOfTextColumn,
txtTextSearch.Text
) || IsBlank(txtTextSearch.Text)
)Multiple lines of text
The same Power Apps filter logic applies as Single Line of Text; whilst you can use the output of a Text Input control and the in operator to find text, the approach isn’t delegable. Please keep that in mind when designing your filtering.
Number
In Power Apps, we can add a Text Input control and change the format to Number. However, the logic below will show as non-delegable, even though we are matching data types to number columns in SharePoint.
// Number - exact match
Filter(
MySharePointList,
NumberColumn = txtNumberSearch.Text || IsBlank(txtNumberSearch.Text)
)A better Power Apps filter approach is to wrap the control within the Value function instead. This removes any delegation worries:
// Number - exact match
Filter(
MySharePointList,
NumberColumn = Value(txtNumberSearch.Text) || IsBlank(txtNumberSearch.Text)
)We can also deploy other logic, such as whether a value is greater than, less than, equals to and so on:
// Number - greater or equals to
Filter(
MySharePointList,
NumberColumn >= Value(txtNumberSearch.Text) || IsBlank(txtNumberSearch.Text)
)Taking it up a level, we could use two values to determine a range of numbers to filter by. In the Power Apps filter example below, I’m using the output of two slider controls:
// Number - within range
Filter(
MySharePointList,
NumberColumn01 > sldStartNumber.Value && NumberColumn02 < sldEndNumber.Value || sldStartNumber.Value = 0 && sldEndNumber.Value = 0
)Date
We can apply Power Apps filter logic to filter by a SharePoint date column. Similarly to number columns, we can filter by exact match(es) or whether a date is earlier or later. We can also use two date picker controls in Power Apps to filter records in SharePoint within the selected date range.
Here’s the logic for exact match:
// Date - exact match
Filter(
MySharePointList,
DateColumn = dtpStartDate.SelectedDate || IsBlank(dtpStartDate.SelectedDate)
)Another common scenario would be filtering records that are greater than or equal to today’s date:
// Date - equals to or greater than today
Filter(
MySharePointList,
DateColumn >= Today()
)
)Using two date picker controls to filter records, where the date in our SharePoint list is equal to or within a specified range in Power Apps:
// Date - date is equal to or within a range of dates
Filter(
MySharePointList,
DateColumn01 >= dtpStartDate.SelectedDate && DateColumn02 <= dtpEndDate.SelectedDate || IsBlank(dtpStartDate.SelectedDate) && IsBlank(dtpEndDate.SelectedDate)
)Choice
A choice column in SharePoint can be set to allow single or multiple values. In Power Apps filter terms, these SharePoint column types can be great for dropdown controls. A single choice from a dropdown box will represent a 1:1 match with any Choice column allowing single values only.
In the example below, note the reference of ColumnName.Value when referencing the SharePoint choice column:
// Matching single choice to single choice
Filter(
MySharePointList,
SingleChoiceColumn.Value = dpdSingleOption.Selected.Value || IsBlank(dpdSingleOption.Selected.Value)
)We can also find a single choice from a dropdown control within a Choice column allowing multiple values. You’ll need to use the in operator to do so. This technique is not delegable.
// Finding single choice within a multiple choice
Filter(
MySharePointList,
dpdSingleOption.Selected.Value in MultiChoiceColumn.Value || IsBlank(dpdSingleOption.Selected.Value)
)Person or Group
Person or Group columns in SharePoint are a window into Entra. We can’t retrieve the full profile of a user, but we do get a limited set of options:
For filtering purposes, unique matches are likely guaranteed with the Claims or Email properties. In the first example, finding a person within a Person or Group column that only allows a single selection:
// Filter by single person
Filter(
MySharePointList,
SinglePersonColumn.Email = cmbSinglePerson.Selected.Email || IsBlank(cmbSinglePerson.Selected)
)To find a single person in a Person or Group column that allows multiple selections, we must use the in operator. This approach isn’t delegable:
// Finding single person in a multiple Person or Group column
Filter(
MySharePointList,
cmbSinglePerson.Selected.Email in MultiPersonColumn.Email || IsBlank(cmbSinglePerson.Selected)
)Yes/No
Under the hood, a SharePoint Yes/No column is simply a boolean true or false. From a Power Apps perspective, you can store these values in a dropdown, combo box or even use a toggle control. There are a couple of things to note with the Power Fx approaches though, to ensure you get around delegation issues.
Firstly, an example to filter a SharePoint list where a Yes/No column is true:
// Filter by all true's
Filter(MySharePointList, YesNoColumn)
// Filter by all false's, non-delegable approach
Filter(MySharePointList, !YesNoColumn)Hardcoding the required value of false is a delegable Power Apps filter approach for that scenario:
// Filter by all false's, delegable approach
Filter(MySharePointList, YesNoColumn = false)You may choose to configure a dropdown or combo box control for users to make selections instead. Here, I’ve set my Items property to have blank, as well as boolean values for true and false:
To ensure compatibility, let’s wrap the selected output in the boolean Power Fx function:
// Filter Yes/No
Filter(
MySharePointList,
YesNoColumn = Boolean(dpdYesNo.Selected.Value) || IsBlank(dpdYesNo.Selected.Value)
)Lookup
Power Apps filter techniques against SharePoint Lookup fields are relatively simple. In the first instance, hard-coded filtering with text entries can be done like so:
// Filter Lookup column by text
Filter(MySharePointList, LookupColumn.Value = "The Force")If you have your lookup options configured to a dropdown or combo box control, we can deploy similar Power Fx to other examples. A slight difference here is not needing to add the IsBlank logic:
// Filter Lookup column with selected dropdown value
Filter(
MySharePointList,
LookupColumn.Value = dpdLookup.Selected.Value
)Managed Metadata
Similarly to Person or Group columns, Power Apps allows us access to a handful of properties for Managed Metadata columns:
For the following Power Apps filter examples, I’ll use Label.
To find a single term in a single-option Managed Metadata column:
// Find single term in single-option Managed Metadata column
Filter(
MySharePointList,
SingleMetadataColumn.Label = cmbSingleMeta.Selected.Label || IsBlank(cmbSingleMeta.Selected.Label)
)To find a single term in a multi-option Managed Metadata column:
// Find single term in multi-option Managed Metadata column
Filter(
MySharePointList,
cmbMultiMeta.Selected.Label in MultiMetadataColumn.Label || IsBlank(cmbMultiMeta.Selected.Label)
)Please note that neither approach is delegable.
Hyperlink
It’s possible to filter by text appearing in a Hyperlink column. I believe this only applies to the URL part of the column, as the alternative text is optional:
We can use the following Power Apps filter technique to find hardcoded text within the URL:
// Filter by hardcoded text in hyperlink column
Filter(MySharePointList, "My Text" in HyperlinkColumn)Similarly, we can use the output of a Text Input control to perform the same search
// Filter by searchable text in hyperlink column
Filter(MySharePointList, txtTextSearch.Text in HyperlinkColumn)Please note that neither approach is delegable. In this instance, using the StartsWith function is your only delegable-friendly option for hyperlink column filtering.
Rating / Likes
This is an interesting one. The Power Apps filter technique needs to be massaged a bit if you want to filter by the SharePoint Ratings column type.
Natively in SharePoint lists, Rating columns seemingly store the value as a number. This is based on the number of stars selected directly against the list item:
To bring these values into a gallery in Power Apps, we use ThisItem.’User ratings’. However, the entries are shown with a comma – meaning that the value is text, not a number:
We need to convert the output into a number if we are to filter by it. For that, we must use the Left function to extract the number, then use the Value function to convert it.
What a faff. With that logic, we can now filter a gallery using normal number techniques such as less than, greater than, etc:
// Filter where rating is greater than or equal to 3
Filter(
MySharePointList,
Value(
Left(
'User ratings',
1
)
) >= 3
)You can avoid the conversion and filter using hardcoded text values instead:
// Filter where rating is specific hardcoded entry
Filter(MySharePointList, 'User ratings' = "4,")Please note that neither approach is delegable.
Location
Much like Managed Metadata and Person or Group columns, a Location column type comes with several properties. We can use any of these for Power Apps filtering purposes.
We can perform exact match filtering using hardcoded text, or text from a Text Input control. All the above properties appear to be delegable with the technique below:
// Filter by Location Name
Filter(MySharePointList, 'LocationColumn: Name' = "My location name")Please note, filtering by postcode requires a match with text, not number:
// Filter by Postal Code
Filter(MySharePointList, 'LocationColumn: Postal Code' = "94129")Created By / Modified By
Arguably one of the most common Power Apps filter scenario’s; filtering records in a SharePoint list that were created or modified by the logged-on user of the Power App. As far as SharePoint lists go, the Created By and Modified By columns are in-built Person or Group columns. We therefore have the same properties we can play with (Claims, Email, etc). I always opt for email as this is guaranteed to be a unique identifier.
For the examples below, I’m using the Office 365 Users connector to determine the logged-on user (see this article for why). This is a good candidate for a Named Formula too.
// Filter entries where creator is logged-on user
Filter(
MySharePointList,
'Created By'.Email = Office365Users.MyProfileV2().mail
)// Filter entries where creator is logged-on user
Filter(
MySharePointList,
'Modified By'.Email = Office365Users.MyProfileV2().mail
)Combining filter criteria
For the full gallery filtering experience, we can combine any number of Power Apps filter criteria at once. This allows for a truly dynamic user experience to dissect SharePoint list data as desired (subject to delegation).
Each filter criteria must be separated with a comma, like so:
// Filter with mutliple criteria
Filter(
MySharePointList,
// Filter by logged-on user
'Created By'.Email = Office365Users.MyProfileV2().mail,
// Filter by lookup column
LookupColumn.Value = dpdLookup.Selected.Value,
// Filter by single person or group column
SinglePersonColumn.Email = cmbSinglePerson.Selected.Email || IsBlank(cmbSinglePerson.Selected),
// Filter by date column
DateColumn = dtpStartDate.SelectedDate || IsBlank(dtpStartDate.SelectedDate)
)You can combine with other functions too, such as Sort or Sort By Columns. This means the filtered output will always be sorted in a dedicated order:
// Filter with mutliple criteria and sorted
SortByColumns(
Filter(
MySharePointList,
// Filter by logged-on user
'Created By'.Email = Office365Users.MyProfileV2().mail,
// Filter by lookup column
LookupColumn.Value = dpdLookup.Selected.Value,
// Filter by single person or group column
SinglePersonColumn.Email = cmbSinglePerson.Selected.Email || IsBlank(cmbSinglePerson.Selected),
// Filter by date column
DateColumn = dtpStartDate.SelectedDate || IsBlank(dtpStartDate.SelectedDate)
),
"CharacterName",
SortOrder.Ascending
)Hopefully some useful tips above that you can inject into your apps to make solid filtering experiences.
Thanks for reading. If you liked this article and want to receive more helpful tips about the Power Platform, don’t forget to subscribe or follow me on socials 😊
About the Author
Craig White
2x Microsoft MVP, Power Platform & M365 Architect
Reference:
White, C (2025). Filter all SharePoint Column Types in Power Apps – Platforms of Power. Available at: Filter all SharePoint Column Types in Power Apps – Platforms of Power [Accessed: 16th July 2025].
