Power Automate OData filter query with examples

In this Power Automate tutorial, we will learn how to use OData filter query in Power Automate and the different functions of filter query in detail. We will also be going through the below points:

1. Introduction Power Automate OData filter
2. Power Automate OData filter query startswith
3. Power Automate OData filter query substringof
4. Power Automate OData filter query not null/not equal
5. Power Automate OData filter query equal
6. Power Automate OData filter query greater than
7. Power automate 0Data filter query greater than or equal
8. Power Automate OData filter query date
9. Power Automate OData filter query AND
10. Power Automate OData filter query OR
11. Power Automate OData filter query multiple conditions
12. Power Automate OData filter query Yes/No

Introduction to Power Automate OData filter

Before we dig deeper into the Power Automate filter query, let’s first discuss about the OData filter query in Power Automate.

OData or Open Data Protocol is an HTTP service made by Microsoft that establishes best practices for designing REST APIs. One of many features of OData is the ability to filter data using a standardized practice. And Power Automate Odata filter is very powerful and comes in really handy when working with huge data like a SharePoint list or document library.

Using the OData query, we can limit the data that we want to bring into our Power Automate flow, and thus we are indirectly increasing the effectiveness of the flow by reducing the items looping time.

In Power Automate, while working on the SharePoint list we mostly use the Get Items action and that is where we find our filter query as shown in the below image.

Let’s explore some examples and learn how can we use the Odata filter query in Power Automate.

Power Automate OData filter query startswith

Let’s start by learning how to use Odata filter query startswith() function in Power Automate.

Suppose we have a SharePoint list with employee details such as Employee name, age, location, etc. shown in the below image. And we only need the details of the employee whose name starts with “John”

Let’s start by creating a Power Automate flow which will get manually triggered and then we will add the Get Items (SharePoint List) action from operators as shown.

Here we are using a filter query parameter to only pull the details for employees whose name starts with “John” and then we are using send an email action to send an email with the employee name. In the startswith() function, we are passing the column name as a parameter and then the comparison text/value based on which we want to filter the data.

Here is the expression:

startswith(EmployeeName,'John')

Once we save and run our flow, we will receive the above email as per the Sharepoint list and this is how we can use the Odata filter query with the startswith() function in Power Automate.

Read How to split a string into an array in Power Automate?

Power Automate OData filter query substringof

Let’s see another function of the OData filter query in Power Automate called substringof().

We will be using the same SharePoint list that we referred to in the first example, we will try to find the sub-string of the EmployeeName column to filter the items in the list.

With the substringof() function, we can verify if the column contains any specific string or surname/middle name. Under Get Items action in our flow, we will use the same function to filter the Employee name that contains “Wick” and then send an email with the filtered employee name.

Noticed the syntax of substringof() function here? Yes, here we pass the text/value as the first parameter and then we provide the column name as the second parameter.

Here is the expression:

substringof('wick', EmployeeName)

After manually running the flow we will get the above email with the employee name “John Wick” as per our SharePoint list and this is how we use substringof() function in the OData filter query in Power Automate.

Power Automate OData filter query not null/not equal

Another important thing that we are required to ensure while dealing with huge data is to find null values.

Suppose we have a flow, a particular column example EmployeeName is a required field and our flow will fail or break if there is any null value. To ensure that, we can use the OData filter query in Get Items action to fetch data from the list which are not Null.

Let’s refer to the below SharePoint list.

Here we don’t have any null value, but to ensure that our flow never fails in the future on Null item, we will provide a filter query to always get only the non-Null/Empty values of the EmployeeName column.

In this query, we are specifying to filter the data where EmployeeName is not equal to Null. We are using “ne” i.e Not Equal comparison operator to filter the data.

Here is the expression:

EmployeeName ne 'Null'

We can also use the same expression to check the EmployeeName column for any specific value like EmployeeName is not equal to “Sam Smith“

EmployeeName ne 'Sam Smith'

Here we have seen, without using any conditions operator in our flow, how easily we can find not Null values of any column using the OData filter query in Power Automate.

Read Power Automate formatdatetime

Power Automate OData filter query equal

Similarly, there can be the requirement to filter query using equal to comparison operator in Power Automate.

We will be using the same Sharepoint list from the above example (please refer to the Sharepoint list image in the above section) and we will provide the OData filter query to find items where the Age column is equal to 50 and then we will send the employee detail in an email.

We are using ‘eq‘ i.e equal to operator, we are querying the data where Age is equal to ’50’. and to fetch only those items in the flow.

Here is the expression:

Age eq '50'

Note: ‘ ‘ is because our Age column is a single line text datatype column.

After running the flow, we will receive an email with Employee Name “Walter White” as per our Sharepoint list. Only Walter White’s age is 50 and this is how we can use the equal to operator in the OData filter query in Power Automate.

Power Automate OData filter query greater than

Let’s see another commonly used operator, greater than query in OData filter in Power Automate.

We have a Sharepoint list with employee details as shown in the below image.

We will try to get all the employees whose Age is greater than 42 and send those employees’ details in an email.

To achieve the above requirement, we have created a flow that will manually trigger. We have also added the Get Items action of SharePoint in the flow.

In the query filter, we are querying the Age column and only getting the items from the SharePoint list where Age is greater than ’42’. In this case, there is only one employee whose age is greater than 42, Walter White.

Here is the expression:

Age gt '42'

After running our flow, we will receive the above email with the employee’s name and this is how we can greater than query in the OData filter in Power Automate.

Let’s also see similar operators that we can use in the OData filter query in Power Automate.

Less than operator in OData filter query ( lt ) : lt query evaluates to true, if the list item value is less than the specified/ compared value/text. Here is the expression:

Age lt '42'

This is how you can use greater than or less than operators in the OData filter query in Power Automate.

Read How to use Rest API in Power Automate

Power automate 0Data filter query greater than or equal

Let’s see another example using greater or equal to operator in OData filter query in Power Automate.

We will be using the above example with the Sharepoint list with employee details as shown below.

Our requirement here is to get the employee details whose age is greater or equal to ’42’ and send those employees’ details in an email body.

To achieve this, we have created a flow that will manually trigger. We have also added the Get Items action of SharePoint in the flow.

Greater than or equal to in OData filter query ( ge ): ge query evaluates to true, if the list item value is greater or equal to the specified/compared value/text. We are querying the Age column and only getting the items from the SharePoint list where Age is greater or equal to ’42’. In this case, we will be receiving 3 emails as per the Sharepoint list after running the flow.

Here is the expression:

Age ge '42'

Let’s also see similar operators that we can use in the OData filter query in Power Automate.

Less than or equal to in OData filter query ( le ) : le query evaluates to true, if the list item value is less or equal to the specified/compared value/text. Here is the expression:

Age le '42'

This is how we can use greater or equal OR less or equal to operators in the OData filter query in Power Automate.

Power Automate OData filter query date

Let’s learn how can we query with date in OData filer in Power Automate.

Suppose, we need to filter SharePoint list items based on the created date in Power Automate. We will use the Get Items action to fetch all the items from the Sharepoint list, however, we will pass the filter query to only fetch the required items from the list.

In this query, we are using the “Created” column of the SharePoint list to compare with today’s date by using the equal to comparison operator in the OData filter in Power Automate.

Here is the expression:

Created eq '2022-09-15'

We can use any comparison operator explained in the above section with the Date field of the SharePoint list in Power Automate.

Read Power Automate Trigger Conditions

Power Automate OData filter query AND

We have seen comparison operators and functions of the OData filter in Power Automate in the above sections. Now, let’s see the logical operators of the OData filter in Power Automate.

Logical operators help in combining multiple conditions together in the query using operators AND and OR.

Suppose, we need to filter employees from the below SharePoint list by their age and location. We need all the employees whose age is greater than or equal to 41 and whose location is Chicago.

In this case, we need to combine the multiple conditions using AND logical operator. So in our flow, in Get Items action we will use the OData query to filter the items of the SharePoint list.

We are simultaneously querying the list for Age greater or equal to ’41’ and Location columns equal to ‘Chicago’ using and operator.

Here is the expression:

Age ge '41' and Location eq 'Chicago'

We will receive the above email with the employees that satisfies both conditions and this is how we can use AND query in the OData filter in Power Automate.

Power Automate OData filter query OR

Above we have seen how to use AND operator in OData filter query. We will learn how can we use OR logical operator in the OData filter query in Power Automate.

Suppose the requirement is to get the employees from the below shown SharePoint list whose location is ‘Denver’ or either department is IT using a Power Automate flow.

It is hard to apply multiple conditions using a filter array condition operator of Power Automate, however, if we use OData filter query it gets much easier.

We will combine both the conditions using OR operator in the OData filter query in the Get Items action as shown below in the image.

Here is the expression:

Location eq 'Denver' or Department eq 'IT'

In this query, we are filtering the SharePoint list items based on the criteria mentioned. We will receive all the employees who satisfy either of the condition and this is how we can use OR operator in the OData filter query in Power Automate.

Read Power Automate IF Expression

Power Automate OData filter query multiple conditions

So we have seen AND and OR logical operators in OData filter query in the above examples, but what if there is a requirement with multiple conditions like 3 or more conditions?

Let’s see how can we write multiple conditions OData filter query in Power Automate using 2 or more AND and OR operators.

Here we will using the above SharePoint List as shown below.

Suppose we have a requirement to get the employee details whose Age is equal to ’41’ and employee must be from ‘Chicago’ location and also Department should be HR.

Here we will try to append multiple conditions together in the OData filter query using and logical operators.

As we can see, we are using multiple AND operators to combine all the required conditions and provide them in the query field to filter the list items based on these conditions.

Here is the expression:

Age eq '41' and Department eq 'HR' and Location eq 'Chicago'

Let’s see how can we join the above multiple conditions with OR operator in OData filter query. We will simply use the same SharePoint list and combine the conditions using OR operators.

Here is the expression:

Age eq '41' or Department eq 'HR' or Location eq 'Chicago'

and this is how you can combine more than 2 or multiple conditions to filter data using the OData filter query in Power Automate.

Power Automate OData filter query Yes/No

In this final section, let’s learn how can we query a check box (YES/NO) of the SharePoint list using the OData filter in Power Automate.

It is much easier to use the OData filter query to filter the check box value in Power Automate than to use a filter array. We can simply provide the column name and comparison value which in this case is either 1 or 0 for Yes and No respectively, in the query.

Suppose we have the above SharePoint list with the checkbox field “Flag” and we want to filter the list items based on the Flag column value is Yes.

We will create a flow that will trigger manually and we will add a Get Items action to fetch all the SharePoint list items, in the filter query we will pass our condition shown in the below image.

We are using equal to operator to check the condition in the query. We can only use eq and ne operator with the checkbox field in the filter query.

Here is the expression:

Flag eq 1

And this is how to use the checkbox column in OData filter query in Power Automate.

We have seen many functions, comparison operators, logical operators, date, checkbox, etc of OData filter query in Power Automation in this tutorial with examples.

Conclusion

In this article, we have learned how to use oData filter query in Power Automate with various examples:

• Power Automate OData filter query startswith
• Power Automate OData filter query substringof
• Power Automate OData filter query not null/not equal
• Power Automate OData filter query equal
• Power Automate OData filter query greater than
• Power automate 0Data filter query greater than or equal
• Power Automate OData filter query date
• Power Automate OData filter query AND
• Power Automate OData filter query OR
• Power Automate OData filter query multiple conditions
• Power Automate OData filter query Yes/No

You may like the following power automate tutorials:

• Power Automate Multiple Conditions
• Power Automate Parallel Branch with Examples
• Power Automate email body formatting
• Power Automate Do until
• Power Automate create a task in Microsoft Planner
• Remove unwanted properties from an object using Power Automate