Power Automate OData Filter: Explained

Have you tried searching for something specific in a mess of data while using Power Automate?

It’s like trying to find a particular shell on a crowded beach. Thankfully, the OData filter acts like a magical metal detector, making sure you find only what you want and nothing else.

Power Automate’s OData filters allow users to efficiently query and filter data from different sources, such as SharePoint, SQL Server, and Dynamics 365. By using OData filter queries, you can obtain specific subsets of data, reducing the amount of unnecessary information and enhancing the performance of your workflows.

This article will explore various Power Automate OData filter examples to help you understand how this feature can be effectively used.

By getting proficient in OData filters, you’ll unlock the full potential of Power Automate and significantly improve your automated workflows, ultimately streamlining your business processes and enhancing overall efficiency.

Let’s get straight to it!

What is OData Filter Syntax?

OData (Open Data protocol) filters are used to refine the data retrieved from connectors that support the OData protocol, ensuring that flows only process the most relevant records.

This helps in optimizing the performance and efficiency of flows by reducing unnecessary data processing.

When using filters, you can use comparison operators like eq for equals, ne for not equals, and so on.

You can also use logical operators such as and, or, and not to combine multiple conditions.

Additionally, there are functions to refine string-based searches. For example, startswith(fieldName,’string’) checks if a field starts with a specified string, endswith(fieldName,’string’) checks if it ends with one, and substringof(‘string’,fieldName) verifies if a field contains a particular substring.

To better understand the potential of OData filters in Power Automate, it’s important to learn about the different functions and operators it accepts.

Let’s look at how you can use them to create filter expressions using different conditions.

1. OData Functions and Operators

OData filter syntax supports various functions and operators to perform operations on the data. These include:

• Arithmetic Operators: add, sub, mul, div, mod
• Logical Operators: and, or, not
• Comparison Operators: eq (equal), ne (not equal), lt (less than), le (less than or equal), gt (greater than), ge (greater than or equal)

Here’s an example OData filter syntax with a function and an operator:

$filter=startswith(Title, 'Power') and Priority eq 'High'

The above example can be broken down into two main conditions combined with the logical and operator:

1. startswith(Title, ‘Power’): This condition checks if the Title field of a record starts with the word “Power”.
2. Priority eq ‘High’: This condition checks if the Priority field of the same record is equal to the value “High”.

When used in a query, this filter will retrieve only those records where the Title starts with “Power” and the Priority is set to “High”.

Both conditions must be met for a record to be included in the result set.

2. OData Conditions and Expressions

When working with OData filter syntax, conditions, and expressions allow you to filter the data based on certain criteria.

These conditions are formed using functions, operators, and the relevant properties of the data source.

Here are some common expressions using various operators:

• Equal (eq): Type eq ‘Bug’
• Not Equal (ne): Status ne ‘Closed’
• Greater than (gt): Price gt 100
• Greater than or equal (ge): Duration ge 30
• Less than (lt): Discount lt 10
• Less than or equal (le): Age le 18

You can also combine conditions using logical operators like and and or:

$filter=(Rating gt 3) and (Price lt 50) or (Discount gt 10)

In this example, the data will be filtered to show items with a Rating greater than 3 and Price less than 50 or items with a Discount greater than 10.

When using OData filter syntax in Power Automate, it’s important to understand the functions, operators, conditions, and expressions to create accurate and efficient filters for your specific needs.

In the next section, we’ll take a look at some basic Power Automate OData filters.

Basic Power Automate OData Filters

Now let’s discuss basic OData filters in Power Automate for SharePoint List, Dynamics 365, and Azure SQL Database.

1. SharePoint List Filters

In Power Automate, SharePoint lists can be filtered with Get Items action and Power Automate Filter Query parameter.

Here are some common filters:

• Filter by Title: To filter the list items by title, you can use Title eq ‘desiredTitle’.
• Filter by Date: To filter the list items by date, you can use a comparison operation like Created ge datetime’2023-06-14T00:00:00′ (greater than or equal to a specified date).
• Filter by Status: For a Choice type column name, to filter items by status, use Status eq ‘In Progress’.

2. Dynamics 365 Filters

In Power Automate, you can use the List records action and OData filter query parameter to filter Dynamics 365 data.

Some common filter examples are:

• Filter by Account Name: To filter the accounts by a specific name, use name eq ‘accountName’.
• Filter by Revenue: For filtering by revenue greater than a specified value, use revenue gt 1000000.
• Filter by Email: To get contacts with a specific email address, use emailaddress1 eq ’email@domain.com’.

3. Azure SQL Database Filters

Azure SQL Database can also be filtered with the Get rows action and OData Filter Query parameter in Power Automate.

Let’s see some common filter examples:

• Filter by Product Name: To filter products by name, use ProductName eq ‘desiredProduct’.
• Filter by Price Range: For filtering products within a certain price range, use Price ge 10 and Price le 100 (price between 10 and 100).
• Filter by Category: To get products based on a specific category, use CategoryID eq 1.

These are just some of the basic OData filters used in Power Automate for SharePoint List, Dynamics 365, and Azure SQL Database.

Remember to replace placeholder values with actual values that match your requirements.

If you are in need of more advanced filtering techniques, take a look at this next section.

Advanced Filtering Techniques Using OData

Let’s explore a variety of advanced filtering techniques using Power Automate OData filter queries.

We’ll cover topics such as date and time filtering, handling null, true, and yes/no values, text functions and filtering, and combining multiple conditions.

1. Date and Time Filtering

When working with date and time values, you can filter data by using specific expressions in the OData query.

For instance, to filter items with a date greater than a specific date or today’s datef:

odata=Field_Name gt ‘yyyy-mm-dd’

To retrieve items with a date greater than or equal to a given date, you can use the ge operator:

odata=Field_Name ge ‘yyyy-mm-dd’

2. Handling Null, True, and Yes/No Values

Handling null values or boolean values like true and false can be achieved using OData filter expressions.

For filtering based on null values, use eq null:

odata=Field_Name eq null

For filtering true/false or yes/no values, you can use:

odata=Field_Name eq true

odata=Field_Name eq false

3. Text Functions and Filtering

OData provides useful text functions to filter data based on text content.

Two of these functions are startswith and substringof.

• startswith: filter items where the specified field starts with a given string:odata=startswith(Field_Name, ‘start_string’)
• substringof: filter items where the specified field contains a given substring:odata=substringof(‘substring’, Field_Name)

4. Combining Multiple Conditions

OData filter queries also allow you to combine multiple conditions using the and and or operators. Enclose each condition in parentheses and use single quotes around string values.

For example, to filter items where the field name equals a specific string and the date is greater than or equal to a given date:

odata=(Field_Name eq ‘string_value’) and (Date_Field ge ‘yyyy-mm-dd’)

For cases where either of two conditions is met, you can use the or operator:

odata=(Field_Name eq ‘string_value’) or (Date_Field ge ‘yyyy-mm-dd’)

These advanced filtering techniques will help you fine-tune your Power Automate OData filter queries for more accurate and specific data retrieval.

Final Thoughts

OData filter queries offer a powerful way to filter and manipulate data when working with SharePoint lists and other data sources.

By utilizing various OData functions such as startswith, eq, and contains, you can effectively streamline your workflows and reduce the amount of unnecessary data processing.

As you develop your skills in Power Automate and OData filter queries, you’ll become more adept at creating efficient and powerful flows.

This will enhance your productivity, streamline data management and even impress your friends!

Happy automating!

If you’d like to learn more ways to use Power Automate to supercharge your workflow, check out this video from our YouTube channel.

In this video we dive into how to use Power Atuomate to integrate ChatGPT with Microsoft Outlook:

Frequently Asked Questions

How can I apply multiple OData filter conditions in Power Automate?

To apply multiple filter conditions, you can use the and or or operators. For example, if you have two conditions, such as Age ge 22 (age greater than or equal to 22) and Country eq ‘USA’ (country equals USA), you can apply both in your filter query like this:

Age ge 22 and Country eq 'USA'

How do I solve a “creating query failed” Odata filter result?

If you encounter a “creating query failed” error with an OData filter, check for any syntax mistakes in your filter expression. Make sure functions, operators, and conditions are written correctly.

Also, ensure that you’re using the right field names and values. If the problem persists, consider seeking help from resources or forums related to the system you’re using for troubleshooting.

What’s the difference between Odata filters and Filter Array?

OData is like a smart language that helps computers talk to each other and find specific information. It’s good for asking computers to give you certain things from a big pile of data.

Think of Filter Array like a sorting game for your computer. It’s a tool that helps your computer pick out things that match a rule you set.

So, OData helps computers talk and find things, while Filter Array is like a game that helps your computer sort and show things based on rules you give it.