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")

Choice column

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

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

Multi-select choice column

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

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

Boolean column

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

Filter('PowerApps Filter', BooleanField = true)

Currency column

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

Filter('PowerApps Filter', CurrencyField = 100)

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);

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!