Using PowerApps Filter Function for All SharePoint List Columns

Introduction

The Filter function in PowerApps allows you to filter data based on specific criteria. When working with SharePoint data sources, this function becomes invaluable for retrieving, sorting, and displaying the data you need. To use the Filter function effectively, it's important to understand its syntax:

Filter(Source, Formula)

Prerequisites

Before diving into the Filter function, ensure you have the following prerequisites:

A PowerApps account.
Access to a SharePoint site with a list containing various column types (text, number, date, choice, lookup, person, etc.).

Now, let's walk through the process step by step.

Connect to SharePoint

Launch PowerApps and create a new app or edit an existing one.
If not already connected, add a data source by selecting "Data" from the left-hand menu and clicking "Add a data source." Choose SharePoint and provide your site's URL.

Insert a Gallery

On your app's screen, insert a Gallery control by selecting it from the "Insert" menu. This Gallery will display the filtered results.

Configure the Gallery

Select the Gallery control, navigate to the formula bar, and set the "Items" property using the Filter function. Let's explore the filter function for different types of columns.

Use filter function for different types of columns

Text column

Let's apply filters to the Text column for values equal to 'Item 1'.

Filter('PowerApps Filter', Title = "Item 1")

Filter Text

Choice column

Let's apply filters to the Choice column for values equal to 'Choice #1'.

Filter('PowerApps Filter', ChoiceField.Value = "Choice #1")

Choice Column

Multi-select choice column

Let's apply filters to the Multichoice column for values containing 'SharePoint'.

Filter('PowerApps Filter', "SharePoint" in 'MultiChoice - Skills'.Value)

Multi Select choice column

Boolean column

Let's apply filters to the Boolean column for values equal to true.

Filter('PowerApps Filter', BooleanField = true)

Boolean

Currency column

Let's apply filters to the Currency column for values equal to 100.

Filter('PowerApps Filter', CurrencyField = 100)

Currency

Number column

Let's apply filters to the Number column for values equal to 100, greater than 100, and so on.

Filter('PowerApps Filter', NumberField = 100);
Filter('PowerApps Filter', NumberField > 100);
Filter('PowerApps Filter', NumberField >= 100);
Filter('PowerApps Filter', NumberField < 300);
Filter('PowerApps Filter', NumberField <= 300);

Number column

Lookup column

By ID
Let's apply a filter where the ID in the Lookup column equals 8.
```
Filter('PowerApps Filter', LookupField.Id = 8)
```
By Value
Let's apply a filter where the value in the Lookup column equals 'Test Item'.
```
Filter('PowerApps Filter', LookupField.Value = "Test Item")
```

Date column

Filter by current date
Let's apply a filter to the date column to include only records where the date is equal to today's date.
```
Filter('PowerApps Filter', DateField =  Today())
```
Filter by a specific date
Let's apply a filter to the date column to include only records where the date is equal to September 6, 2023.
```
Filter('PowerApps Filter', DateField =  Date(2023, 9, 6))
```
Filter by specific date range
Let's apply a date filter to include only dates between September 1, 2023, and September 6, 2023, in the date column.
```
Filter('PowerApps Filter', DateField >= Date(2023, 9, 1) And DateField <= Date(2023, 9, 6))
```

Person column

Filter by the current user email
Let's apply a filter where the email ID is equal to the current user's email.
```
Filter('PowerApps Filter', UserField.Email = User().Email)
```
Filter by the current user display name
Let's apply a filter where the user name is equal to the current user's name.
```
Filter('PowerApps Filter', UserField.DisplayName = User().FullName)
```
Filter by specific email
Let's apply a filter where the email ID is equal to the specific email.
```
Filter('PowerApps Filter', UserField.Email =  "[email protected]")
```

Summary

In this article, we've delved into the utilization of the Filter function in PowerApps to handle different types of SharePoint list columns such as people, lookup, date, and more.

I hope this helps.

Sharing is caring!