How To Use OData Filters In Power Automate With SharePoint

Power Automate OData filters are powerful. But I always struggle to remember the correct syntax for my OData filters.

This article provides Power Automate OData filter query examples for the most common use cases with SharePoint lists. It is my cheat sheet for formulating a Power Automate Get items filter query with OData.

What are OData Filter Queries

The by Microsoft initiated Open Data Protocol (OData) is an open protocol that allows the creation and consumption of queryable and interoperable REST APIs in a simple and standard way. Within this article we’ll focus on the use of filtering data from a SharePoint list with Power Automate Get items filter queries with OData.

When you are using SharePoint lists with Power Automate you will probably know the ‘Get items‘ operation.

Per default the operation returns all entries of the SharePoint list. This might be more than you want. By providing a Power Automate Filter Query you can limit the data you want to receive to your needs.

For instance you have a SharePoint list of employees. In your SharePoint list there is an Age column. You could filter the employees by their age. Just set the filter Filter Query like this (Returns only employees that are older than 34):

Age gt 34

Comparison Operators

To compare a value of a SharePoint list there are a couple of operators.

eq (Equals)

The ODATA eq operator evaluates to true, when the SharePoint list value is equal to the compared value.

Name eq 'John'
Age eq 21

ne (Not equals)

The ODATA ne operator evaluates to true, when the SharePoint list value is NOT EQUAL to the compared value.

Name ne 'John'
Age ne 21

gt (Greater than)

The ODATA gt operator evaluates to true, when the SharePoint list value is greater than the compared value.

Age gt 12

lt (less than)

The ODATA lt operator evaluates to true, when the SharePoint list value is less than the compared value.

Age lt 33

ge (Greater than or equal)

The ODATA ge operator evaluates to true, when the SharePoint list value is greater or equal to the compared value.

Age ge 22

le (Less than or equal)

The ODATA le operator evaluates to true, when the SharePoint list value is less or equal to the compared value.

Age le 11

Combining operators

The combining operators make multiple conditions possible. You can combine your conditions with an Or condition or an And condition. Last but least, you can use brackets to group conditions.

and (Logical and)

Combine multiple conditions with and. Here is an example to check an age from 10 to 19.

Age gt 9 and Age lt 20

or (Logical or)

Combine multiple conditions with or.

Status eq 'on vacation' or Status eq 'sick'

() (Brackets)

Use the brackets to control which condition gets when evaluated.

Name eq 'John' or (Age gt 9 and Age lt 20)

Functions

Although there are more functions that might be used with OData queries, the following two are the ones that are supported for the filtering of SharePoint list:

startswith

You want to know if a string column starts with a certain string, use startswith.

// Name = John Moe Smith
startswith(Name,'John')

substringof

With substringsof of you can verify if a string contains another string:

// Name = John Moe Smith
substringof('Moe',Name)

Common use cases

Filter query column with space

A common problem is: How do I formulate OData queries when there is a space in the column name?

The answer is to check the technical field name. You can find the name by going to your SharePoint list’s settings page and move the mouse cursor over the column. Within the URL you see the technical name. Use this name in your query.

Null

To check a field for an unset empty value use null like shown below:

Name eq null
Name ne null

Example queries by SharePoint field type

Choice

To filter a SharePoint field of type Choice with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)
• startswith (begins with)
• substringof (is part of the string)

Examples – OData filter for Choice SharePoint field

Status eq 'New'
Status ne 'New'
startswith(Product, 'Discounted by')
substringof('organic', Cotton)

Currency

To filter a SharePoint field of type Currency with an OData query, use these OData operators and functions in the OData Power Automate Filter Query field:

• eq (equal to)
• ne (not equal to)
• lt (less than)
• le (less or equal than)
• gt (greater than)
• ge (greater or equal than)
• startswith (begins with)
• substringof (is part of the string)

Examples – Power Automate OData filter for Currency SharePoint field

Price eq 9
Price ne 10
Price lt 20
Price le 50
Price gt 100
Price ge 33
startswith(Price, '8')
substringof('71', Price)

Date and Time

To filter a SharePoint field of type Date and Time with an Power Automate OData filter query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)
• lt (less than)
• le (less or equal than)
• gt (greater than)
• ge (greater or equal than)

Examples – OData filter for Date and Time SharePoint field

Created eq '2022-12-24'
Created ne '2022-12-24'
Created lt '2022-12-24'
Created le '2022-12-24'
Created gt '2022-12-24'
Created ge '2022-12-24'

Power Automate Filter query date range

Created ge '2022-12-24' and Created le ''2022-12-31''

Power Automate Filter query date today

Use the ‘utcNow()’ expression like this:

ScheduledAt gt 'utcNow()'

Note: Do not just copy the text, you have to add the expression.

Filter out empty dates

ScheduledAt ne null

Lookup

To filter a SharePoint field of type Lookup with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)

Examples – OData filter query for Lookup SharePoint field

Department eq 'HR'
Department ne 'HR'

Number

To filter a SharePoint field of type Number with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)
• lt (less than)
• le (less or equal than)
• gt (greater than)
• ge (greater or equal than)

Examples – Power Automate OData filter query for Number SharePoint field

Age eq 21
Age ne 44
Age lt 21
Age le 16
Age gt 40
Age ge 66

Person or Group

To filter a SharePoint field of type Person or Group with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)

Examples – OData Power Automate filter query for Person or Group SharePoint field

Employee/Title eq 'John Doe'
Employee/EMail eq '[email protected]'

Single line of text

To filter a SharePoint field of type Single line of text with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)
• startswith (begins with)
• substringof (is part of the string)

Examples – OData Power Automate filter query for single line of text SharePoint field

Name eq 'John'
Name ne 'John'
startswith(Name, 'Jo')
substringof('oh', Name)

Yes/No

To filter a SharePoint field of type Yes/No with an OData query, use these OData operators and functions:

• eq (equal to)
• ne (not equal to)

Examples – OData Power Automate filter query for Yes/No SharePoint field

Active eq 1
Active ne 1