23 Power Apps Filter Function Examples For SharePoint

23 Power Apps Filter Function Examples For SharePoint

The Power Apps Filter function checks a table for any records matching a set of logical criteria. Then it extracts the results into a new table. Use these examples to help you filter a SharePoint list. All of the filter functions on this page support delegation so the full results set will be returned.

Table of Contents

• Filter A SharePoint Text Column In Power AppsText Column EqualsText Column Starts WithText Column Is BlankFilter A SharePoint Number Column In Power AppsNumber Column Is Greater Than Or Less ThanNumber Column Is Greater Than Or Equal To/Less Than Or Equal ToNumber Column Does Not EqualNumber Column Is Between Two ValuesFilter A SharePoint Date Column In Power AppsDate Column Equals Current DateDate Column Equals A Specific DateDate Column Is Between Two DatesDate Columns Date Range Includes DateFilter A SharePoint Yes/No Column In Power AppsYes/No Column Equals YesYes/No Column Equals NoFilter A SharePoint Choice Column In Power AppsChoice Column EqualsFilter A SharePoint LookUp Column In Power AppsLookUp Column ID EqualsLookUp Column Value EqualsFilter A SharePoint Person Column In Power AppsPerson Column Equals Current UserPerson Column Equals User NamePerson Column Equals User EmailAdditional ExamplesAND Logical OperatorOR Logical OperatorNOT Logical OperatorMultiple Logical Operators




Filter A SharePoint Text Column In Power Apps


Text Column Equals


Input:

Inventory SharePoint List

IDTitleManufacturer
1DishwasherLG
2FreezerSamsung
3Kitchen FaucetKohler
4Kitchen SinkAmerican Standard
5RefrigeratorSamsung
6Stove


Code:

Get records where the Manufacturer column equals Samsung

Filter(Inventory, Manufacturer ="Samsung")



Output:

IDTitleManufacturer
2FreezerSamsung
5RefrigeratorSamsung




Text Column Starts With


Input:

Inventory SharePoint List

IDTitleManufacturer
1DishwasherLG
2FreezerSamsung
3Kitchen FaucetKohler
4Kitchen SinkAmerican Standard
5RefrigeratorSamsung
6Stove


Code:

Get records where the Title column starts with “Kitchen”

Filter(Inventory, StartsWith(Title, "Kitchen"))



Output:

IDTitleManufacturer
3Kitchen Faucet Kohler
4Kitchen Sink American Standard




Text Column Is Blank


Input:

Inventory SharePoint List

IDTitleManufacturer
1DishwasherLG
2FreezerSamsung
3Kitchen FaucetKohler
4Kitchen SinkAmerican Standard
5RefrigeratorSamsung
6Stove


Code:

Get records where the Manufacturer column is blank

Filter(Inventory, Manufacturer=Blank())



Output:

IDTitleManufacturer
6Stove




Filter A SharePoint Number Column In Power Apps


Number Column Is Greater Than Or Less Than


Input:

Inventory SharePoint List

IDTitleInStock
1Dishwasher9
2Freezer14
3Kitchen Faucet7
4Kitchen Sink10
5Refrigerator15
6Stove13


Code:

Get records where the InStock column is greater than 10

Filter(Inventory, InStock > 10)



Output:

IDTitleInStock
2Freezer14
5Refrigerator15
6Stove13




Number Column Is Greater Than Or Equal To/Less Than Or Equal To


Input:

Inventory SharePoint List

IDTitleInStock
1Dishwasher9
2Freezer14
3Kitchen Faucet7
4Kitchen Sink10
5Refrigerator15
6Stove13


Code:

Get records where the InStock column is less than or equal to 10

Filter(Inventory, InStock <= 10)



Output:

IDTitleInStock
1Dishwasher9
3Kitchen Faucet7
4Kitchen Sink10




Number Column Does Not Equal


Input:

Inventory SharePoint List

IDTitleInStock
1Dishwasher9
2Freezer14
3Kitchen Faucet7
4Kitchen Sink10
5Refrigerator15
6Stove13


Code:

Get records where the InStock column does not equal 9

Filter(Inventory, InStock <> 9)



Output:

IDTitleInStock
2Freezer14
3Kitchen Faucet7
4Kitchen Sink10
5Refrigerator15
6Stove13




Number Column Is Between Two Values


Input:

Inventory SharePoint List

IDTitleInStock
1Dishwasher9
2Freezer14
3Kitchen Faucet7
4Kitchen Sink10
5Refrigerator15
6Stove13


Code:

Get records where the InStock column is between 5 and 10

Filter(Inventory, InStock >= 5 And InStock <= 10)



Output:

IDTitleInStock
1Dishwasher9
3Kitchen Faucet7
4Kitchen Sink10




Filter A SharePoint Date Column In Power Apps


Date Column Equals Current Date


Input:

Inventory SharePoint List

IDTitleLastSoldDate
1Dishwasher2/16/2023
2Freezer2/18/2023
3Kitchen Faucet3/1/2023
4Kitchen Sink3/4/2023
5Refrigerator2/23/2023
6Stove3/6/2023


Code:

Assuming the current date is March 6, 2023, get records where the LastSoldDate column equals today

Filter(Inventory, LastSoldDate = Today())



Output:

IDTitleLastSoldDate
6Stove3/6/2023




Date Column Equals A Specific Date


Input:

Inventory SharePoint List

IDTitleLastSoldDate
1Dishwasher2/16/2023
2Freezer2/18/2023
3Kitchen Faucet3/1/2023
4Kitchen Sink3/4/2023
5Refrigerator2/23/2023
6Stove3/6/2023


Code:

Get records where the LastSoldDate column equals March 1, 2023

Filter(
    Inventory,
    LastSoldDate = Date(2023, 3, 1)
)



Output:

IDTitleLastSoldDate
3Kitchen Faucet3/1/2023




Date Column Is Between Two Dates


Input:

Inventory SharePoint List

IDTitleLastSoldDate
1Dishwasher2/16/2023
2Freezer2/18/2023
3Kitchen Faucet3/1/2023
4Kitchen Sink3/4/2023
5Refrigerator2/23/2023
6Stove3/6/2023


Code:

Get records where the LastSoldDate column is between March 1, 2023 and March 31, 2023

Filter(
    Inventory,
    LastSoldDate >= Date(2023, 3, 1)
    And LastSoldDate <= Date(2023, 3, 31)
)



Output:

IDTitleLastSoldDate
6Stove3/6/2023




Date Columns Date Range Includes Date


Input:

Inventory SharePoint List

IDTitlePromotionStartDatePromotionEndDate
1Dishwasher2/14/20232/28/2023
2Freezer2/20/20233/5/2023
3Kitchen Faucet2/24/20233/17/2023
4Kitchen Sink3/11/20233/18/2023
5Refrigerator3/15/20233/31/2023
6Stove3/17/20234/2/2023


Code:

Get records where the PromotionStartDate column and the PromotionEndDate column includes March 1, 2023

Filter(
    Inventory,
    PromotionStartDate <= Date(2023, 3, 1)
    And PromotionEndDate >= Date(2023, 3, 1)
)



Output:

IDTitlePromotionStartDatePromotionEndDate
2Freezer2/20/20233/5/2023
3Kitchen Faucet2/24/20233/17/2023




Filter A SharePoint Yes/No Column In Power Apps


Yes/No Column Equals Yes


Input:

Inventory SharePoint List

IDTitleOnSale
1DishwasherYes
2FreezerNo
3Kitchen FaucetNo
4Kitchen SinkNo
5RefrigeratorYes
6StoveNo


Code:

Get records where the OnSale column equals Yes

Filter(Inventory, OnSale = true)



Output:

IDTitleOnSale
1DishwasherYes
5RefrigeratorYes




Yes/No Column Equals No


Input:

Inventory SharePoint List

IDTitleOnSale
1DishwasherYes
2FreezerNo
3Kitchen FaucetNo
4Kitchen SinkNo
5RefrigeratorYes
6StoveNo


Code:

Get records where the OnSale column equals No

Filter(Inventory, OnSale = false)



Output:

IDTitleOnSale
2FreezerNo
3Kitchen FaucetNo
4Kitchen SinkNo
6StoveNo




Filter A SharePoint Choice Column In Power Apps


Choice Column Equals


Input:

Inventory SharePoint List

IDTitleOrderStatus
1DishwasherOrdered
2FreezerOrdered
3Kitchen FaucetNot Ordered
4Kitchen SinkDiscontinued
5RefrigeratorOrdered
6StoveNot Ordered


Code:

Get records where the OrderStatus column equals “Ordered”

Filter(Inventory, OrderStatus.Value = "Ordered")



Output:

IDTitleOrderStatus
1DishwasherOrdered
2FreezerOrdered
5RefrigeratorOrdered




Filter A SharePoint LookUp Column In Power Apps


LookUp Column ID Equals


Input:

Inventory SharePoint List

IDTitleManufacturer
1DishwasherLG
2FreezerSamsung
3Kitchen FaucetKohler
4Kitchen SinkAmerican Standard
5RefrigeratorSamsung
6Stove


Manufacturers SharePoint List

IDTitle
1LG
2Samsung
3Kohler
4American Standard


Code:

Get records from the Inventory table where the Manufacturer column ID equals 2

Filter(Inventory, Manufacturer.ID = 2)



Output:

IDTitleManufacturer
2FreezerSamsung
5RefrigeratorSamsung




LookUp Column Value Equals


Input:

Inventory SharePoint List

IDTitleManufacturer
1DishwasherLG
2FreezerSamsung
3Kitchen FaucetKohler
4Kitchen SinkAmerican Standard
5RefrigeratorSamsung
6Stove


Manufacturers SharePoint List

IDTitle
1LG
2Samsung
3Kohler
4American Standard


Code:

Get records from the Inventory table where the Manufacturer column value equals “Kohler”

Filter(Inventory, Manufacturer.Value = "Kohler")



Output:

IDTitleManufacturer
3Kitchen FaucetKohler




Filter A SharePoint Person Column In Power Apps


Person Column Equals Current User


Input:

Inventory SharePoint List

IDTitleBuyer
1DishwasherMatthew Devaney
2FreezerSarah Green
3Kitchen FaucetMatthew Devaney
4Kitchen SinkDavid Johnson
5RefrigeratorDavid Johnson
6StoveAlice Lemon


Code:

Assuming the current user is Matthew Devaney, get records from the Inventory table where the Buyer column equals the current user

Filter(Inventory, Buyer.Email = User().Email)



Output:

IDTitleManufacturer
1DishwasherMatthew Devaney
3Kitchen FaucetMatthew Devaney




Person Column Equals User Name


Input:

Inventory SharePoint List

IDTitleBuyer
1DishwasherMatthew Devaney
2FreezerSarah Green
3Kitchen FaucetMatthew Devaney
4Kitchen SinkDavid Johnson
5RefrigeratorDavid Johnson
6StoveAlice Lemon


Code:

Get records from the Inventory table where the Buyer column user name equals “Sarah Green”

Filter(Inventory, Buyer.'Display Name' = User().Name)



Output:

IDTitleManufacturer
2FreezerSarah Green




Person Column Equals User Email


Input:

Inventory SharePoint List

IDTitleBuyer
1DishwasherMatthew Devaney
2FreezerSarah Green
3Kitchen FaucetMatthew Devaney
4Kitchen SinkDavid Johnson
5RefrigeratorDavid Johnson
6StoveAlice Lemon


Code:

Get records from the Inventory table where the Buyer column email [email protected]

Filter(Inventory, Buyer.Email = "[email protected]")



Output:

IDTitleManufacturer
4Kitchen SinkDavid Johnson
5RefrigeratorDavid Johnson




Additional Examples


AND Logical Operator


Input:

Inventory SharePoint List

IDTitleManufacturerOnSale
1DishwasherLGYes
2FreezerSamsungNo
3Kitchen FaucetKohlerNo
4Kitchen SinkAmerican StandardNo
5RefrigeratorSamsungYes
6StoveNo


Code:

Get records from the Inventory table where the manufacturer column “Samsung” and the OnSale column equals Yes.

Filter(Inventory, Manufacturer="Samsung" And OnSale = true)



Output:

IDTitleManufacturerOnSale
5RefrigeratorSamsungYes




OR Logical Operator


Input:

Inventory SharePoint List

IDTitleManufacturerOnSale
1DishwasherLGYes
2FreezerSamsungNo
3Kitchen FaucetKohlerNo
4Kitchen SinkAmerican StandardNo
5RefrigeratorSamsungYes
6StoveNo


Code:

Get records from the Inventory table where the manufacturer column “Samsung” or the OnSale column equals Yes.

Filter(Inventory, Manufacturer="Samsung" Or OnSale = true)



Output:

IDTitleManufacturerOnSale
1DishwasherLGYes
2FreezerSamsungNo
5RefrigeratorSamsungYes




NOT Logical Operator


Input:

Inventory SharePoint List

IDTitleManufacturerOnSale
1DishwasherLGYes
2FreezerSamsungNo
3Kitchen FaucetKohlerNo
4Kitchen SinkAmerican StandardNo
5RefrigeratorSamsungYes
6StoveNo


Code:

Get records from the Inventory table where the manufacturer column is not “LG.”

Filter(Inventory, Not Manufacturer="LG")
Important note: the NOT operator does not support delegation. Use the <> equals operator instead.



Output:

IDTitleManufacturerOnSale
2FreezerSamsungNo
3Kitchen FaucetKohlerNo
4Kitchen SinkAmerican StandardNo
5RefrigeratorSamsungYes
6StoveNo




Multiple Logical Operators


Input:

Inventory SharePoint List

IDTitleManufacturerOnSale
1DishwasherLGYes
2FreezerSamsungNo
3Kitchen FaucetKohlerNo
4Kitchen SinkAmerican StandardNo
5RefrigeratorSamsungYes
6StoveNo


Code:

Get records from the Inventory table where the manufacturer column equals “Samsung” and OnSale equals Yes or the manufacturer column equals LG

Filter(Inventory, (Manufacturer="LG" And OnSale="Yes") Or Manufacturer="LG")



Output:

IDTitleManufacturerOnSale
1Dishwasher LGYes
5RefrigeratorSamsungYes





Questions?

If you have any questions or feedback about 23 Power Apps Filter Function Examples For SharePoint please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

24 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Paulo Moekotte
Paulo Moekotte
1 year ago

Hello Matthew,

Looks like a small mistake up in the second example ‘Text Column Starts With ‘ where it says Samsung in stead of Kitchen.

Keep up to good work and thanks for the time and effort you are putting in to this.

Paulo

Wei
Wei
1 year ago

Great post, a small issue I see: Get records where the InStock column does not equal 9. Then is this formale correct?

Filter(Inventory, InStock <= 9)

Duncan Cunningham
Duncan Cunningham
1 year ago

Matthew thank you for being a creator that stays with the restrictions most of us operate under. There are so many content creators all going “dataverse can do this and that”. Our organizations are not paying for these premium options so I’m pleased that one of the best publishers of PowerApps guides still remembers those of us who are in the Sharepoint List ‘only’ world.

Tim
Tim
1 year ago

Hello,
Some of the links are not working on this page
I get a 404 error

Dawid Ziolkowski
1 year ago

Hi Matthew,

Is the Yes/No column filter function correct for NO? (think it should be false in second example)

Also, the Person.Email vs User().Email might not work in some org. The User().Email returns the User Principal Name and the Person.Email might have a different value.
UPN can be found thou in Person.Claims property.
Maybe it is worth to mention that as consideration.

As always good work and please keep it up 🙂

Ps. Funny thing is that Power Apps for a longer moment did not work well with the filtering of boolean column 🙁

Regards
Dawid

richard clowes
1 year ago

I wish there was a way to filter text saying text contains “xyz” which isn’t delegable.

Tyler
Tyler
1 year ago

Wonderful article. Your content is very helpful and actionable!

Avinash
Avinash
1 year ago

hello moekotte,

I have started learning power app and I come across a problem. Before explaining the problem I want to give you some context.

On my power app i have created a form which is linked with Microsoft list so that every new response can be saved in the list. This form have different types of data like drop down, true false, text, number.

Now on the other page of the same app, I have displayed the list using DataTable.

Problem:
I want to add a Text Input where users can search any type of data, example: If there is a drop down where users can choose locations and that drop down have 3 options (Delhi, Mumbai, Chennai), so the user can search for Delhi and get all the rows with the selected option Delhi.

I also have done some work with the help of filter option but it is not working,

This is the formula that I have used:

Filter(‘Interview Request’,

  Text(Title, “@”) in SearchInput.Text ||
  
  Text(‘Hiring Department’, “@”) = ‘Hiring Department_Column2’.Selected.Value ||

  Text(‘Manager ID’, “[$-en-US]0”) in SearchInput.Text ||

  Text(‘Experiance’, “@”) in SearchInput.Text ||

  Text(Location, “@”) in SearchInput.Text
)

Please help me with this problem.
Thankyou

Mike Gillis
Mike Gillis
11 months ago

I have a gallery with a SharePoint list data source. If a filter on the gallery’s items returns no records I’d like to set a variable to true. The following is in the gallery’s Items property.
If the current user’s email does not match the email in any records none should be returned. This works fine but if no records are returned I’d like to set a variable to true. Not sure how to do that. THANK YOU!

If(!varIsAppAdmin,
Sort(
  Filter(
    ‘Deployment Requests’,
    ‘E-Mail’ = varCurrentUser.Email
  ),
  Created,
  SortOrder.Descending
),
Sort(‘Deployment Requests’,Created,SortOrder.Descending)
)

fareedh
fareedh
7 months ago

Hi Matthew,

Filter with <> is not delegable right.
can you please me know the workaround for this?

Rodney Key
Rodney Key
4 months ago

Can you filter two columns with the same value except the matching
Id: ParentID:
0001. 0001. Exclude
0002. 0003 exclude
0003. 0001. Include

Nhan
Nhan
1 month ago

i cannot filter a table vs a text. Please show me

Alan McK
7 days ago

For the Yes/No section, you might want to add the syntax for when a value DOES NOT EQUAL true – this is useful if you add a Yes/No column to a list AFTER the List is already populated because on existing list items, the value of an added Yes/No column is neither Yes nor No … I’ve used “[&&] Archived true”, which seems to work, but may not be the best practice syntax. [“!=” doesn’t seem to work at all on booleans, and I’ve seen some chatter that suggests that “” doesn’t work with Or.]

Alan McK
4 days ago
Reply to  Alan McK

Correction — “suggests that [%lt;%gt;] doesn’t work with Or”.