Table of Contents
What are data operations in Power Automate?
Data operations in Power Automate allow you to manipulate (sort, filter, rearrange, and much more) data while the flow is running. The list of data operations available in Power Automate are:
- Parse JSON
- Create CSV table
- Filter array
- Select
- Join
- Create HTML table
- Compose
What is the filter array action in Power Automate?
The filter array action in Power Automate is used to reduce the number of objects in an array to a subset that meet one or more specific conditions.
How it works
- Input: An array or list of items, it usually comes from another action like Get items (SharePoint), List rows (Dataverse/Excel) or a result from an API call.
- Condition: A logical test you define in the “Choose value” / “is equal to” / “Choose value” style fields, or by writing an expression.
Output: A new array containing only items where the condition is true.
In the above example, we would take a list from another action, then apply a filter (First name = Eugenia) and the outputs of this action would be all items from the list that match this criteria.
When to use filter array vs OData filter query?
There are two main ways to filter data in Power Automate:
Use an OData filter query directly in your data-retrieval action, this filter is applied in the very same action that the data is retrieved. For example if we want to extract all projects from a SharePoint list that have “Rejected” status, we can do this directly in the SharePoint action:
There are different types of filters that can be applied, you can find a full guide on SharePoint OData filters in our blog.
Apply the Filter array action after the data has been fetched: first, we extract data from a data source (Excel, Dataverse or a third party API), and on top of the result returned by the system, we apply a filter inside the flow.
OData query filters are used when the data source supports this type of filtering and when the data type we want to filter by is allowed. For example:
- SharePoint allows OData filters, but calculated columns are not filterable columns
- Excel allows OData filters, but only one column can be filtered at a time and the column name can’t have special characters such as spaces.
When to use the Filter array action
The filter array action is used mainly when OData filters can’t be applied, but there are other scenarios that will require the need of this action in the workflow. Here is a quick guide with some general points to consider:
- The data source doesn’t support OData filtering
- You need more complex logic than OData can handle (substring matches, regex, nested array fields, case-insensitive comparisons, advanced date math).
- You already have an array from a previous step (like a variable or API response) and need to refine it further.
- You’re combining data from multiple sources before filtering.
Feature | OData Filter Query | Filter Array |
When filtering happens | Before data is retrieved (server-side) | After data is retrieved (flow-side) |
Performance | Faster, less data transferred | Slower if large dataset |
Complex logic | Depends on the data source | Very flexible |
Best for | Reducing data load from supported connectors | Filtering arrays already in your flow |
How to use filter array with a single condition?
The Filter array action is quite simple when working with just one condition. Just pick the array to filter, define the property to check, and set your comparison operator.
There are multiple operators available:
Example: filter for value equal to
Depending on the condition you want to apply, just select the column to filter by on the left side of the action, then select the operator from the dropdown, and finally write the criteria to be met on the right side.
Below shows a filter for projects “equals to” Rejected.
Example: not equal to vs contains vs starts with
Depending on the criteria, different operators can be used. Below you’ll find an example that will show all projects that “contain” the word France in the name.
We can also do something similar and bring everything that is “not equals to”:
Greater than, less than, greaterOrEquals, lessOrEquals
Filters can also be appled to numbers to return, for example, projects that started prior 2025:
Expressions can also be applied on the criteria field, for example, if we want to dynamically calculate the current year, something like below can be written:
Remove empty items (null)
It’s also very common to want to exclude items that are empty. To remove null results from an array, we must use the null expression as shown below:
The results should like this:
It’s important to not write “null” directly in the text input, otherwise Power Automate will interpret it as plain text.
Remove empty items (empty string)
Some empty data comes as empty strings instead of nulls. For this scenario, a filter like this can be applied:
How to apply multiple conditions (and / or)?
Sometimes you need to filter your array based on more than one rule, for example “Rejected projects in 2025” or “Approved and In progress projects with a budget over 100,000 USD”. The Filter array action allows you to combine conditions using the and() and or() functions.
Using and() for “all conditions must be true”
and() makes sure that every condition inside it is true before the item is kept.
Example: Status = Rejected AND Year = 2025
and(
equals(item()?['Status'], ‘Rejected’),
greater(item()?['Year'], 2025)
)
Using or() for “any condition can be true”
or() returns true if at least one condition is met.
Example: Status = In progress or Status = Approved
or(
equals(item()?['Status'], ‘Approved’),
equals(item()?['Status'],’In progress’)
)
Combining and() and or()
You can nest them for more complex filters.
Example: Status = In progress or Status = Approved AND budget > 100,000
and(
or(
equals(item()?['Status'], ‘Approved’),
equals(item()?['Status'],’In progress’)
),
greater(item()?['Budget'], 100000)
)
An important consideration for filters that combine two or more criteria, is that these need to be written directly in the Power Automate action, by using the “Edit in advanced mode” option.
Make sure to always include a “@” at the beginning of the expression.
Filter array coding convention
When working with the filter array action, to indicate the column or attribute by which the filter will be applied, item()?[‘PropertyName’] is used.
- The item() function represents the current row in the array.
- The ? is a navigator operator that helps us connect the current row with the attribute we want to filter by.
- The property name inside the brackets must match exactly the field name in your array (case-sensitive)
item()?['PropertyName']
item()?['Year']
If the column has some “child” values, like LookUp columns or Choice columns in SharePoint – that have “Value” and/or “Id”. This can represented as follows:
item()?['Status/Value']
item()?['Company/Id']
How to get the count from filter array?
The length() function is used to count how many results the filter returned.
This is really useful for example to know when to do something or not do anything.
- If there aren’t projects rejected for 2025, then: do nothing.
- If there are: send a summary email
length(body('Filter_array'))
How to use the results from filter array?
After applying Filter array, the output is a new array containing only the items that matched your conditions. We can use these results in different ways depending on your scenario. The “Body” output of the filter array action is what we would usually use.
Some examples of how the results are used are:
Loop through the results
The most common use case is processing each filtered item.
- Add an Apply to each action.
- In the Select an output from previous steps box, choose the body of Filter array.
Inside the loop, you can work with properties like:
item()?['Year']
item()?['Status/Value']
Get the first item from the filtered array
If you only need one result (the first match), the following expression can be used:
first(body('Filter_array'))?['Title']
Common scenarios with filter array in Power Automate
Filter SharePoint list items
If the column supports OData filters, then OData is recommended, but in the case that the column type is not supported for OData, the filter array action comes to the rescue! Some common cases of columns that can be used with Filter array are:
- When we want to filter by long text columns
- When we want to filter by calculated columns
Filter Excel table rows
The Excel connector has plenty of limitations when it comes to filtering, it pretty much covers only a single text column filter. Anything more complex than that, needs to be handled by a filter array. Some examples are:
- Multiple criteria combined (multiple columns)
- Filter by dates or numbers
- Filter by column names with spaces in the header name or special characters
Common mistakes to avoid when using filter array
- When working with combined filters (and / or), always use the advanced editor and include “@” at the beginning of the expression, otherwise it won’t work!
- When there is a possibility that data can contain nulls, use the “coalesce” function to handle the empty results.
- Keep in mind data types!
- Text should be compared with text
- Dates should be compared with dates
- Numbers should be compared with numbers
- Don’t forget that arrays start at index 0. The first result is index/row number 0. The second result is 1, and so on!
Partner with Power GI to build smarter Power Platform solutions
Building efficient flows often comes down to making the right choices — like knowing when to use Filter array inside Power Automate versus applying an OData filter query at the source. Contact us and start building great automations
