Power Automate Filter Query Operators [With Examples]

In this Power Automate tutorial, we will learn what Filter Query is and how to use the Filter Query operators in Power Automate flow with different examples.

What is Filter Query in Power Automate?

Power Automate filter query retrieves items from the SharePoint Online list and also from other data sources. And also it filters and displays that data based on the condition applied.

The Syntax of the Filter Query is:

1. Field or Column Name
2. Operator
3. Field value you want to check/filter

In the below table we can see the Operators are listed along with a description:

Operators	Description
eq	Equal to
ne	Not equal to
gt	Greater than
lt	Less than
ge	Greater than or equal to
le	Less than or equal to
contains	contains (Not Supported in Get items filter query)
not contains	Does not contains (Not Supported in Get items filter query)
and	And
or	Or
Startswith	Start with the specified value
Endswith	End with the specified value (Not Supported in Get items filter query)
substring Of	return string characters

In this Complete tutorial, we will be using the below SharePoint List called Products List Consisting of Product Code no, Product Name, Customer Name, Customer Location, Unit Price, Quantity, OrderDate, order priority, and Market Price.

In the following, we will see how we can use these operators and filter values using Power Automate flow:

Let us explore various Filter Query Operators in Power Automate.

Power Automate filter query equal to operator

Let us see how to apply a filter query using the equal-to operator in Power Automate.

In this example, we will filter data value where the Product Name is equal to using “eq” i.e. Equal to operator to filter the data.

Now we will create a flow to apply a filter query dynamically based on the Product Name. It filters and displays the data based on Product Name.

• Log in to the Power Automate flow -> Select instant cloud flow -> Enter Flow Name -> Choose Manually trigger a flow option -> Click on the create button like below.

• Expand the trigger action and Add a text input value for the Product Name.

• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Configure the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

"ProductName eq '@{triggerBody()['text']}'"

Where,

• ProductName = Column internal Name
• eq = Operator
• {triggerBody()[‘text’]} = Dynamic content value

To see the result, we will create an HTML table and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the Product Name to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see in the HTML table Output section we can view the filtered items based on the Product Name.

In the same way, we will filter different products we can enter the product name and choose the run flow option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the Product Name.

This is how to apply a filter query using the equal to operator in Power Automate flow.

Power Automate filter query Not equal to operator

Here we will see how to filter data using a filter query Not equal to operator in Power Automate flow.

In this example, we will filter data value where the Product Name is not equal using “ne” i.e. Not Equal comparison operator to filter the data.

• Create an instant cloud flow, Expand the trigger action, and Add a text input value for the Product Name.
• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Provide the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

"ProductName ne '@{triggerBody()['text']}'"

Where,

• ProductName = Column internal Name
• ne = Operator
• {triggerBody()[‘text’]} = Dynamic content value

To see the result, we will create an HTML table and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the Product Name to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see in the HTML table Output section we can view the filtered data except the Product name called Speaker.

This is how to filter data using a filter query Not equal to operator in Power Automate flow.

Power Automate filter query Greater than operator

Let us see how to apply a filter query using the Greater than operator in Power Automate flow.

In this example, we will filter data value where the Quantity is Greater than to using “gt” i.e. Greater than operator to filter the data.

• Create an instant cloud flow, Expand the trigger action, and Add a Number input value for the Product Quantity.

• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Configure the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and enter the number value to filter.

 "Quantity gt '@{triggerBody()['number']}'"

Where,

• Quantity = Column internal Name
• gt= Operator
• {triggerBody()[‘number’]} = Dynamic content value

We will create an HTML table and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair to see the Output.

Save and Test the flow. Now Enter the Quantity Number to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the quantity greater than 25.

This is how to apply a filter query using the Greater than operator in Power Automate flow.

Power Automate filter query Less than operator

Here we will see how to apply a filter query using the Less than operator in Power Automate.

In this example, we will filter data value where the Quantity is less than using “lt” i.e. Less than operator to filter the data.

• Create an instant cloud flow, Expand the trigger action, and Add a Number input value for the Product Quantity.
• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Provide the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

"Quantity lt '@{triggerBody()['number']}'"

Where,

• Quantity = Column internal Name
• lt = Operator
• {triggerBody()[‘number’]} = Dynamic content value

We will create an HTML table to see the output, and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the Quantity Number to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the quantity less than 10.

This is how to apply a filter query using the Less than operator in Power Automate.

Power Automate filter query Greater than or equal to operator

Let us see how to apply a filter query using the Greater than or equal to operator in Power Automate.

In this example, we will filter data value where the Unit Price is greater than or equal to using “ge” i.e. Greater than or equal to operator to filter the data.

• Create an instant cloud flow, Expand the trigger action, and Add a Number input value for the Unit Price of the Product.

• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Configure the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and enter the number value to filter.

"UnitPrice ge '@{triggerBody()['number']}'"

Where,

• UnitPrice = Column internal Name
• ge= Operator
• {triggerBody()[‘number’]} = Dynamic content value

We will create an HTML table to see the result, and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the Unit Price Number value to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the Unit Price of the Product being greater than or equal to 1500.

This is how to apply a filter query using the Greater than or equal to operator in Power Automate.

Power Automate filter query Less than or equal to operator

Here we will see how to apply a filter query using the Less than or equal to operator in Power Automate.

In this example, we will filter data value where the Unit Price is less than or equal to using “le” i.e. Less than or equal to operator to filter the data.

• Create an instant cloud flow, Expand the trigger action, and Add a Number input value for the Unit Price of the Product.
• Select the Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Provide the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

"UnitPrice le '@{triggerBody()['number']}'"

Where,

• UnitPrice = Column internal Name
• le = Operator
• {triggerBody()[‘number’]} = Dynamic content value

We will create an HTML table and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair to see the output.

Save and Test the flow. Now Enter the Unit Price number value to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the Unit Price of the Product being less than or equal to 280.

This is how to apply a filter query using the Less than or equal to operator in Power Automate.

Power Automate filter query startswith

Here we will see how to apply a filter query using the Startswith operator in Power Automate.

In this example, we will filter data value, where the Product Name starting with the name uses the “Start with” string operator to filter the data.

• Expand the trigger and add a Text input value for the Name of the Product in the instant cloud flow.
• Add Get Items action, to get list items from the SharePoint list and also to apply a filter in the filter query.
• Configure the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

startswith(ProductName,'@{triggerBody()['text']}')

Where,

• startswith= Operator
• ProductName= Column internal Name
• {triggerBody()[‘text’]} = Dynamic content value

We will create an HTML table and add key-value pair to see the output. By selecting the custom option and in the form field pass the dynamic content values and in the columns field.

Save and Test the flow. Now Enter the Product Name value to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the list item values that start with Monitor.

This is how to apply a filter query using the Startswith operator in Power Automate.

Power Automate filter query Endswith

Endswith is not supported in the get items filter query in Power Automate.

Power Automate filter query substringof

Here we will see how to apply a filter query using the substring of the string operator in Power Automate.

In this example, we will filter data value where the Product Name contains the value or not by using “substringof ” i.e. string operator to filter the data.

• Expand the trigger action, and Add a Text input value for the Product Name in the instant cloud flow.
• To get list items from the SharePoint list and also to apply a filter in the filter query based on value, select get items action from action triggers.
• Provide the SharePoint site Address and SharePoint list Name. In the Filter Query field, Pass the Field or Column internal Name, Operator, and values to filter.

"substringof  ('@{triggerBody()['text']}', ProductName)"

Where,

• ProductName= Column internal Name
• substringof = Operator
• {triggerBody()[‘text’]} = Dynamic content value

We will create an HTML table and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair to see the output.

Save and Test the flow. Now Enter the Unit Price number value to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the list item values that start with Monitor.

This is how to apply a filter query using the substring of the string operator in Power Automate.

Power automate filter query AND

Here we will be using the below SharePoint List called Products List Consisting of Product Code no, Product Name, Customer Name, Customer Location, Unit Price, Quantity, OrderDate, order priority, and Market Price.

Let us see how to use the AND operation in SharePoint to get item filter queries using Power Automate.

Here we will use the Products list to filter and get the data where the Unit Price is greater than 230 and the Quantity is less than 10.

Now we will create a flow to apply a filter query dynamically based on the condition applied.

Log in to the Power Automate flow -> Select instant cloud flow -> Enter Flow Name -> Choose Manually trigger a flow option -> Click on the create button like below.

Expand the trigger action, and Add two Number input values for the ProductUnitPrice and for the Quantity.

Select the Get items action, and then provide the SharePoint site address and list name. Then click on Show advanced options, In the Filter query field, write the below expression.

"UnitPrice gt '@{triggerBody()['number']}' and Quantity lt '@{triggerBody()['number_1']}' "

Where,

• UnitPrice, Quantity = Column internal Names
• gt and lt = Operators
• {triggerBody()[‘number’]} , {triggerBody()[‘number_1’]} = Dynamic content values

To see the output create an HTML table, and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the ProductUnitPrice and Quantity Number to filter the values and click on the Run flow Option.

Once the flow ran successfully like below, we can see the expected result in the HTML table Output section, where it filters and displays the items based on the Unit Price greater than 230 and the Quantity less than 10.

This is how to use the AND operation in SharePoint to get item filter queries using Power Automate.

Power automate filter query OR

Let us see how to use the OR operation in SharePoint to get item filter queries using Power Automate.

Here we will use the Products list to filter and get the data where the Customer Location is equal to Canada and the Quantity is less than 10 OR UnitPrice is greater than 230.

• Create an instant cloud flow, Expand the trigger action. Add two Number input values for the ProductUnitPrice and for the Quantity.
• Add a text input for the Customer Location.

Select the Get items action, and then configure the SharePoint site address and list name. Then click on Show advanced options, In the Filter query field, write the below expression.

"CustomerLocation eq '@{triggerBody()['text']}' and (Quantity lt '@{triggerBody()['number_1']}' or UnitPrice gt '@{triggerBody()['number']}')"

Where,

• CustomerLocation,UnitPrice, Quantity = Column internal Names
• eq,gt, and lt = Operators
• {triggerBody()[‘text’]},{triggerBody()[‘number’]} , {triggerBody()[‘number_1’]} = Dynamic content values

We will create an HTML table to see the output, and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the ProductUnitPrice and Quantity Number to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the condition applied.

This is how to use the OR operation in SharePoint to get item filter queries using Power Automate.

Power Automate Filter Query Multiple Conditions

Let us see how to get items using filter query with multiple conditions using Power Automate flow.

In this example, we will filter the value where the Product Name is equal to Monitor and Order Date is equal to today minus the last 7 days and Order Priority is Low.

• Create an instant cloud flow, Expand the trigger action. Add two text input values for the ProductName and for the Order Priority.

Select the Get items action, and then configure the SharePoint site address and list name. Then click on Show advanced options, In the Filter query field, write the below expression.

"ProductName eq '@{triggerBody()['text']}' and OrderPriority eq '@{triggerBody()['text_1']}'  and OrderDate eq '@{addDays(utcNow(),-7,'MM-dd-yyyy')}'"

Where,

• CustomerLocation,UnitPrice, Quantity = Column internal Names
• eq,gt, and lt = Operators
• {triggerBody()[‘text’]},{triggerBody()[‘number’]} , {triggerBody()[‘number_1’]} = Dynamic content values

We will create an HTML table to see the output, and in the form field pass the dynamic content values and in the columns field choose the custom option and add key-value pair.

Save and Test the flow. Now Enter the ProductUnitPrice and Quantity Number to filter the values and click on the Run flow Option.

Once the flow ran successfully, we can see the expected result in the HTML table Output section, where it filtered and displays the items based on the condition applied.

This is how to get items using filter query with multiple conditions using Power Automate.

Conclusion

In this Power Automate tutorial, we have learned how to use the Filter Query operators in Power Automate with different examples.

• Power Automate filter query equal to operator
• Power Automate filter query Not equal to operator
• Power Automate filter query Greater than operator
• Power Automate filter query Less than operator
• Power Automate filter query Greater than or equal to operator
• Power Automate filter query Less than or equal to operator
• Power Automate filter query startswith
• Power Automate filter query Endswith
• Power Automate filter query substringof
• Power automate filter query AND
• Power automate filter query OR
• Power Automate Filter Query Multiple Conditions

You may also like:

• Import Data from Excel to a SharePoint list using Power Automate
• Add Attachments to SharePoint List Items using Power Automate
• Check If a Date is Blank in Power Automate
• Power Automate Condition if a String is Empty