How to Use Power Automate Filter Query [Guide With Examples]

Power Automate is a powerful tool that can be used to automate processes and tasks. It can leverage the filter query feature, allowing users to use OData Power Automate filter query syntax to retrieve items from SharePoint, SharePoint Online, and other sources.

This article will provide a brief overview of Power Automate, then introduce the concept of Power Automate filter query and explain how it can be used to power automate get items filter query from a SharePoint list or other sources.

We will also discuss how to use the Get Items action in Power Automate or how to use filter query in power automate to filter the items based on multiple conditions, use the Power Automate filter query field to filter the items and use operators such as EQ, GT, and Startswith in Power Automate filter queries. Finally, we will discuss the importance of using the Power Platform to get items filter queries.

What is Power Automate Filter Query?

Power Automate (formerly known as Microsoft Flow) is a cloud-based software tool allowing users to create and automate workflows and tasks across multiple applications and services without developer help. The Power Automate Filter Query is an OData system query option that allows users to filter the data in a specific operation, such as “Get items” or “Get rows.”

For example, users might use the “Get items” action to fetch data from a SharePoint list when working with SharePoint. However, fetching all items at a time might be more efficient and feastime when dealing with a large amount of data. That’s where the power automat filter query comes into play. With the filter query, users can specify certain conditions to limit the data fetched from the list based on their specific requirements.

To illustrate this, let’s consider an example. You have filtered the SharePoint list with a “Due Date” column, and you want to fetch only those items that are due today or later. You could use a filter query DueDate ge ‘@{utcNow()}’ that would fetch only the items whose “Due Date” is greater than or equal to (ge) the current date and time (@{utcNow()}). This is more efficient than fetching all items and then filtering them in your flow.

In Power Automate, there are several ways to filter data, we have listed them below.

Filter Query

As described above, this option allows you to filter data at the source. It is powerful, efficient, and supports complex filtering conditions. However, it requires knowledge of the power to automate OData filter query protocol and its query language. Syntax errors in the filter query can lead to “Invalid filter clause” errors.

Filter Array

This action is available after retrieving the data, allowing you to filter the data in your flow based on certain conditions. This is useful if you need to perform additional logic or transformations on the data before filtering. However, large datasets may be less efficient as all the data must be retrieved first.

Condition

The Condition action allows you to control the flow of your logic based on certain conditions. For example, you could use a Condition to process only those items that meet a certain condition. This powerful feature may be less efficient for complex filtering requirements.

Select

The Select action allows you to filter columns from your data. You can specify which columns you want to include or exclude. This is useful for reducing the amount of data you’re working with but doesn’t filter rows based on conditions.

When choosing a method to filter your data in Power Automate, it’s important to consider the specific requirements of your flow, the size of your dataset, and your familiarity with the power automate odata filter query language.

Suggested reading: Compose in Power Automate: Definition and Usage

How to Use Filter Query in Get Items Action in Power Automate

To use a filter query in the Get Items action in Power Automate, you must filter a SharePoint list from which you want to get items based on specific conditions.

Step-by-step guide on using Filter Query

Step 1

Sign in to Power Automate.

Step 2

After signing in, click “Create” in the left-hand menu.

Step 3

You must select “Instant Cloud Flow” from the given options.

Step 4

Name your flow and select a trigger. The trigger could be anything, depending on your use case. For example, “Manually trigger a flow.”

Step 5

After selecting the following trigger, you need to click on the Create button that can be given below.

Step 6

You create a manual trigger flow successfully; in the next step, you need to click on the “+ New step.” button to add a new trigger.

Recommended reading: Microsoft Power Automate Trigger Conditions: Example-Based Guide

Step 7

Search for “Get items” and select it.

Step 8

In the “Site Address” field, select the SharePoint site where your list is located.

Step 9

In the “List Name” field, select the list from which you want to fetch the items.

Step 10

Now, here in this final step, you can enter your odata filter query power automate in the “Filter Query” field. This is a simple and easy way to use Filter Query in Power Automate.

Read also: How to populate a Word Document with Power Automate

Examples of Using Filter Query Power Automate to Filter Data

Example 1: Filtering Based on a Single Field

If you have a list of items with a “Status” column, and you want to fetch only the items with status “Completed”, you can use a filter query like this:

 Status eq 'Completed'

This query uses the eq (equals) operator to filter the items.

Example 2: Filtering Based on Multiple Fields

Suppose you have a “DueDate” column and a “Priority” column, and you want to fetch items that are due today or later and have a high priority. You can use a filter query like this:

 DueDate ge '@{utcNow()}' and Priority eq 'High'

This query combines two conditions using the and operator.

Example 3: Filtering Based on a Lookup Field

If you have a lookup field named “Manager”, and you want to fetch items where the manager is “John Doe”, you can use a filter query like this:

Manager/Title eq 'John Doe'

Manager/Title gets the title (name) from the Manager lookup field in this query.

Example 4: Filtering Date Fields

If you have a “StartDate” and “EndDate” field, and you want to fetch items where the StartDate is less than or equal to today’s date and the EndDate is greater than or equal to today’s date, you can use a filter query like this:

StartDate le '@{utcNow()}' and EndDate ge '@{utcNow()}'

Remember, the filter query should use the field’s internal name. If the field’s internal name contains space, replace the space with ‘x0020’.

Power Automate Filter Query Operators and Functions

You can use various operators in your filter queries:

• eq for equals
• ne for not equals
• gt for greater than
• ge for greater than or equal to
• lt for less than
• le for less than or equal to

Functions like startswith(Fieldname, ‘string’), endswith(Fieldname, ‘string’), substringof(‘string’, Fieldname) can be used to filter text fields.

For date and time functions, day(), month(), year(), hour(), minute(), second() are available, and now() can be used to get the current date and time.

These operators and functions are part of the OData protocol used by Power Automate.

Please note that Power Automate’s SharePoint power automate Get items filter query action is case-insensitive for its queries, meaning it doesn’t differentiate between lowercase and uppercase letters when retrieving items based on a Filter Query.

Related reading: Power Automate Functions: Definition, Cheat Sheet, Best Practices

How to Write OData Filter Query in Power Automate?

Creating power automate odata filter query in Power Automate, formerly Microsoft Flow, is an effective way to access and filter data from a given dataset. power automate odata filter queries’ main purpose is to retrieve data precisely based on specific criteria. These criteria include column names, list item values, or any other data field.

Here is a step-by-step guide on how to write an odata filter query power automate query:

Step 1: Choose your Power Automate action

The first step is to choose the action to use the filter query. Often, this will be in a ‘Get items’ or ‘Get files’ action, which retrieves items from a SharePoint list or library, respectively.

Step 2: Navigate to the Filter Query option

Once you’ve selected your action and specified the SharePoint site and list/library, you will find an optional ‘Filter Query’ input field. This is where you’ll input your OData filter query.

Step 3: Write the Filter Query

An OData filter query is written in a specific format: <Field Internal Name> eq ‘value’. Replace <Field Internal Name> with the internal name of the field you want to filter and ‘value’ with the value you are filtering for. The eq operator is used for equality. If you were filtering for values greater or equal, you would use ge. For example, to find all items where the ‘Title’ field equals ‘Project’, you would write Title eq ‘Project’.

Step 4: Multiple Filter Criteria

If you want to use multiple filter criteria, you can use the and or or logical operators. For example, Title eq ‘Project’ and Status eq ‘Completed’ would filter for items where the Title is ‘Project’ and the Status is ‘Completed’.

Step 5: Using Functions

OData also supports functions like substringof(), endswith(), startswith(), etc. For example, substringof(‘Project’, Title) returns true if the string ‘Project’ is found anywhere within the Title field.

Final Step: Test the Flow

After creating your OData filter query, test your Power Automate flow to ensure the data is filtered correctly.

Read also: How to Send EMails with Power Automate: Step-by-Step Guide

Some additional things to note:

• Ensure that the field internal names you are using are correct. SharePoint often creates internal names that are different from the field display name, especially if spaces are involved.
• Power Automate is case sensitive when it comes to field names in OData queries.
• Use single quotes around string values and no quotes around numerical values.
• If you filter based on a date field, the date must be in the ISO 8601 format (YYYY-MM-DD).

Examples:

• To get items where the title is ‘Project A’: Title eq ‘Project A’
• To get items where the status is not completed: Status ne ‘Completed’
• To get items where the project start date is greater than or equal to 2023-01-01: StartDate ge 2023-01-01
• To get items where the title contains the word ‘Project’: substringof(‘Project’, Title)

Rememwith OData filter queriePower Automate s, Power Automate provides a powerful mechanism for working with data precisely and effectively, maximizing the power of automated workflows.

Best Practices for Using Filter Query in Power Automate

Using Filter Query in Power Automate, particularly with SharePoint ‘Get items’ or ‘Get files’ actions, can improve the performance and specificity of your workflows. Here are some best practices and common mistakes to avoid:

Best Practices

1. Understand Data Types: Filter query behaves differently with different data types. For example, text and choice fields require single quotes around the value, while number and boolean fields do not.
2. Understand Data Types: Filter query behaves differently with different data types. For example, text and choice fields require single quotes around the value, while number and boolean fields do not.
3. Leverage OData Functions: Utilize functions such as startswith(), endswith(), and substringof() for more complex queries. These can provide powerful ways to filter your data.
4. Employ Logical Operators: Use the logical operators and, or To combine multiple conditions in your filter queries. This can significantly improve the precision of your data filtering.
5. Use ISO 8601 for Dates: When dealing with Date and Time columns, ensure your dates are in ISO 8601 format (YYYY-MM-DD).
6. Test Your Queries: Always test your queries to ensure they return the expected results. A small syntax error can drastically alter the data returned by the filter query.

Common Mistakes to Avoid

1. Case Sensitivity: One common mistake is not considering that Power Automate is case-sensitive regarding field names in OData queries.
2. Ignoring SharePoint’s Internal Field Names: SharePoint creates internal names that differ from the field display name. Ignoring this can result in filter queries not working as expected.
3. Misusing Quotes: Misusing single quotes around the filter value is another common mistake. Text values need single quotes around them; numeric and boolean values do not.
4. Improper Date Formats: The filter query might not return the correct results if you don’t use the ISO 8601 date format for date fields.
5. Overlooking the Delegation Warning: Delegation becomes crucial if your flow is designed to process a large amount of data. Not every filter query operation can be delegated, so always check for delegation warnings.
6. Not Escaping Special Characters: If your filter query includes special characters, you must escape them properly to avoid unexpected errors.

By following these best practices and avoiding common mistakes, you can create efficient and effective workflows using filter queries in Power Automate.

Read also: What are Variables in Power Automate and How to Use Them?

Final Thoughts

Filter queries in Power Automate are an extremely powerful tool that enables precise data filtering in actions like ‘Get items’ or ‘Get files’, particularly when dealing with SharePoint. Understanding and utilizing the OData syntax can help us form conditions to effectively query multiple items, filter SharePoint lists, or set specific criteria based on our needs.

However, as with any powerful tool, it’s essential to understand best practices and common pitfalls when using filter queries in Power Automate. This includes using the correct internal field names, understanding data types, and appropriately formatting filter values. By mastering the use of filter queries, we can create efficient, targeted, and powerful automated workflows to enhance productivity and data management.

If you want to learn more about Power Automate, we recommend checking our detailed Power Automate training course.

FAQ

How do I give a filter query in Power Automate?

In Power Automate, you can give a filter query in actions such as 'Get items' or 'Get files' when working with SharePoint. In the action's 'Filter Query' field, you provide your query in the format operator 'value'. For example, Title eq 'Project A'.

How do you get data from filter array Power Automate?

The 'Filter array' action in Power Automate lets you filter the elements of an array based on a condition. In the 'Filter array' action, you specify the array you want to filter and provide a condition in the format item()['property'] operator 'value'. The filtered array can then be used in subsequent actions.

How do you filter data in a query?

To filter data in a query in Power Automate, you use the 'Filter Query' field in the relevant action (like 'Get items'). The filter query uses OData syntax and operators (like eq, ne, gt, ge, etc.) to specify the condition for filtering.

How do I create a dynamic filter in a power query?

You can use parameters and the M query language to create a dynamic filter in Power Query (which is a part of Power BI, not Power Automate). You can create a parameter for your filter condition, then reference this parameter in your query. This allows the filter to be dynamically updated based on the parameter's value.

How do you write a filter query in the power app?

In Power Apps, filter queries are written using the 'Filter' function. For example, Filter('MyList', Status = 'Active') filters the 'MyList' data source for records where the Status field is 'Active'.

How do you filter a power query function?

In Power Query, you can filter data using the 'Filter rows' option in the 'Home' tab. To filter a function's result, you typically incorporate the filter operation in the function definition or in the step where the function is invoked.