In this Power Automate tutorial, we will learn how to use the Power Automate Filter Array operator to filter data as per conditions more effectively and easily in Power Automate. We will also be going through the below points with examples for better understanding.
- Introduction of Power Automate filter array
- Power Automate filter array equal to
- Power Automate filter array not equal to
- Power Automate get the length of the filter array
- Power Automate filter array starts with
- Power Automate filter array contains
- Power Automate filter array remove the empty item
- Power Automate filter array greater than
- Power Automate filter array less than
- Power Automate filter array AND
- Power Automate filter array OR
- Power Automate filter array Date
- Power Automate filter array multiple conditions
- Power Automate filter array of objects
- Power Automate filter array SharePoint list
- Power Automate filter array Lookup column
- Power Automate filter array Choice column
Introduction of Power Automate filter array
Whenever we need to filter data in power automate, we get only two options. First, filter the data in the Get items action using the OData filter query. Second, filter the data with the ‘Filter array‘ action in Power Automate.
We know that the OData filter query is the better option to filter the data, however, in certain situations we don’t get the option to work with the OData filter query.
Please follow this link to know more about the OData filter query in Power Automate.
In such situations, we must use the ‘Filter array‘ action field to filter the data at a later stage in the Power Automate Flow.
The Power Automate ‘Filter array‘ action filter or limit the items from an array based on the condition or criteria we provide.
In the Power Automate flow, we will add the filter array data operator from all the listed operations and if we expand the ‘edit in advance more’ we can write our conditions or query. And we can also write multiple conditions simultaneously. Please refer to the below image.
Let’s explore more about this filter array action and learn a few of the commonly used operations.
Power Automate filter array equal to
Let’s start this tutorial with one of the most commonly used compare operator ‘equal to‘ in the filter array action of Power Automate.
Suppose we have a SharePoint list with employee details as shown in the below image and we need to only get the employees from the ‘Chicago’ location. And we also need to send an email with the employee details.
We will begin by creating a Power Automate flow that will trigger manually, and then we will add the ‘Get Items‘ action to fetch all the items from the given SharePoint list on the Sharepoint site. We want to filter the array to get the object whose location is ‘Chicago’. Next, we will select the ‘Filter array‘ data operator of the Power Automate flow.
In the ‘From’ field, we will provide the array of items that we will get from the previous step the ‘Get Items’ action. Then we will click on ‘advance mode’, and we will write our condition to filter the array of filtered items.
Here is the condition:
@equals(item()?['Location'], 'Chicago')Now, we have the array of filtered items. To send an email, Power automate automatically add the ‘Apply to each’ action, and then we will ‘Send an email‘ outlook action as shown below.
After saving and manually running the flow, we will receive 2 emails with different employee names as per our Sharepoint list.
And this is how we can use ‘equal to’ operator in the Filter array action in Power Automate.
Read Power Automate SharePoint Get Items Filter Query
Power Automate filter array not equal to
In this section, we will learn how to use the ‘not equal to‘ comparison operator in Filter array action in Power Automate.
Similar to the above example, we have a Sharepoint list with employee details and we need to get the employee whose department is not equal to ‘HR’ and we need to send the details of the filtered employees’ detail. Please refer to the below image for the Sharepoint list.
We will create a flow that will trigger manually. To fetch all the items for the SharePoint list, we will be using the ‘Get Items’ SharePoint action. Next, to apply the required condition, we will add the ‘Filter array’ action.
In this action, we will provide the array of items to be filtered in the ‘from’ field. We will use the ‘not equal to’ operator to filter the employees based on the Department column and we will click on ‘advance mode’ to write the below condition.
Next, we will ‘Send an email‘ outlook action to send an email with employee details in the email body. Power Automate will automatically add the ‘apply to each’ action to loop the array objects.
Here is the condition:
@not(equals(item()?['Department'], 'HR'))
After successfully manually running the flow, we will receive the above email with the employee name and this is how we can use the ‘not equal to‘ operator in the ‘Filter array‘ action of Power Automate.
Let’s now move forward and learn how to get the length of the array from the ‘Filer array’ action in Power Automate.
Read How to export SharePoint List items to excel using Power Automate and send email
Power Automate get the length of filter array
In this section, we will see how to get the length or count of the items of the ‘Filter array‘ action in Power Automate.
Suppose, we have a SharePoint list as shown in the below image with employee details and we need the count of all the employees whose age is greater than 41.
We will create a flow that will trigger manually. To fetch all the items from the SharePoint list, we will be using the ‘Get Items‘ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list.
Here in this action, we are filtering the array of list items where the ‘Age‘ column is greater than ’41’ by using the below condition in the advanced mode of ‘Filter array’.
Here is the condition:
@greater(item()?['Age'], '41')Next, to get the length of the array of the ‘Filter array’ action output, we will be using the compose action as shown in the below image.
We are using the length() function to get the count of the array and pass the body or the output of the ‘Filter array’ action.
Here is the expression:
length(body('Filter_array'))
After manually running the flow, we will receive the above length of the ‘Filter array” action items array as 3 in the output of the compose action in Power Automate.
In this section, we learned how to get the length of the array or count of the items of the ‘Filter array’ using Power Automate Flow. We can use the above condition to get the ‘greater than’ operator of the ‘Filter array’ in Power Automate.
Read How to remove characters from string in Power Automate?
Power Automate filter array starts with
Let’s learn how can we use the ‘starts with‘ operator in the ‘Filter array‘ action to filter a SharePoint list using Power Automate.
Suppose we have a SharePoint list with employee detail and the requirement is to get all the employees whose name starts with ‘John’ and we also need to send these employees names in an email.
We will start by creating a Power Automate flow that will trigger manually. To fetch all the items from the SharePoint list, we will use the ‘Get Items’ action, and then to filter the array of items, we will add the ‘Filter array’ action in the flow as shown in the below image.
In the ‘Filter array’ action, we will provide the array of the SharePoint list item in the ‘from’ field and then we will provide the condition to filter the items array where the employee name starts with the string value ‘John’.
Here is the condition:
@startsWith(item()?['EmployeeName'], 'John')Power automate flow, we automatically add the ‘Apply to each’ action for the array of filtered items and then we will add ‘Send an email’ to shoot an email with the employee name in the body.
After successfully running the flow, we will receive the above email with the employee name as per the Sharepoint list item. And this is how we can use the ‘starts with‘ operator to filter the array of the ‘Filter array‘ action in Power Automate.
Read How to split a string into an array in Power Automate?
Power Automate filter array contains
After learning how to use the ‘starts with’ operator in the ‘Filter array’ action, we will now see how can we use the ‘contains‘ operator in the ‘Filter array‘ action using the Power Automate flow.
Similar to the above example, the requirement is to get all the employees whose name contains the string value ‘John’ from the SharePoint list with the employee details. We will then send an email to verify the result. Please refer to the below image for the SP list.
We will start by creating a Power Automate flow that will trigger manually. To fetch all the items from the SharePoint list, we will use the ‘Get Items’ action, and then to filter the array of objects, we will add the ‘Filter array’ action in the flow.
In the ‘Filter array’ action, we will provide the array of the SharePoint list item in the ‘from’ field and then we will provide the condition to filter the items array where the employee name contains the string value ‘John’.
Here is the condition:
@contains(item()?['EmployeeName'], 'John')Power automate flow will automatically add the ‘Apply to each’ action for the array of filtered items and then we will add ‘Send an email’ to shoot an email with the employee name in the body.
After successfully running the flow, we will receive the above email with the employee name to verify the result as per the Sharepoint list item. And this is how we can use the ‘contains‘ operator to filter the array of the ‘Filter array‘ action in Power Automate.
Read Power Automate formatdatetime
Power Automate filter array remove empty item
In this section, we will learn how to remove an empty item or string using the ‘Filter array‘ action in Power Automate.
Let’s suppose, we have an array variable with cities name with one empty city name string. By using Power automate flow, we will remove the empty city name string.
To achieve this, we will create a Power Automate flow that will trigger manually. We will first initialize a variable ‘myArrayItems‘ and will set datatype as ‘array’ and value as ‘[“Chicago”, “New York”, ” “]’. Please refer to the below image.
To remove the empty string from the variable ‘myArrayItems’, we will add the ‘Filter array’ action to the flow. In this action, we will provide the array of cities’ names in the ‘From’ field. Next, we will click on the ‘advance mode’ to provide the condition to remove the empty items from the variable ‘myArrayItems’.
Here is the condition:
@equals(empty(item()),false)
After saving and manually running the flow, we will see that the empty item or object of the variable ‘myArrayItems‘ has been removed in the output of the ‘Filter array’ action body as shown in the above image.
And this is how you can check and remove an empty item or string using the ‘Filter array’ action in the Power Automate Flow.
Power Automate filter array greater than
Let’s learn how can we use the ‘greater than‘ comparison operator in the ‘Filter array’ action to filter a Sharepoint list using Power Automate.
Suppose we have a Sharepoint list with employee detail as shown in the below image and the requirement is to get all the employees whose age is greater than ’42’ and then we also need to send these employees’ names in an email.
We will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list.
In the ‘Filter array’ action, we will provide the array of the SharePoint list item in the ‘from’ field and then click on the ‘edit in advance mode’ and we will provide the ‘greater than‘ condition to filter the items array where the ‘Age‘ column’s value is greater than ’42’.
Here is the condition:
@greater(item()?['Age'], '42')
After successfully running the flow, we will receive the above email with the employee name to verify the result as per the Sharepoint list item.
In this section, we learned how can we use the condition to get the ‘greater than‘ operator using the ‘Filter array’ in Power Automate.
Read How to use Rest API in Power Automate
Power Automate filter array less than
In the above section, we have seen how to use the ‘greater than’ operator of the ‘Filter array’ action. Now, it is time to learn how to use the ‘less than‘ operator of the ‘Filter array‘ action in Power Automate.
Similar to the above example, suppose we have a Sharepoint list with employee detail as shown in the below image and the requirement is to get all the employees whose age is less than ’42’, and then we also need to send these employees’ names in an email.
We will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list. Please refer to the below image.
In the ‘Filter array’ action, we will provide the array of the SharePoint list item in the ‘from’ field and then click on the ‘edit in advance mode’ and we will provide the ‘less than’ condition to filter the items array where the ‘Age‘ column’s value is less than ’42’.
Power automate flow will automatically add the ‘Apply to each’ action for the array of filtered items.
Here is the condition:
@less(item()?['Age'], '42')
After saving and manually testing the Power Automate flow, we will receive 2 emails with different employee names and details. We can verify the result by looking at the output of the ‘Filter array’ action and as shown in the above image, the count of filtered items is 2.
This is how we can use the ‘less than‘ comparison operator of the ‘Filter array‘ acton in Power Automate Flow.
Power Automate filter array AND
In this section, we will be looking at more complex operators like AND logical operators of the ‘Filter array‘ action in Power Automate.
Suppose we have a Sharepoint list with employee detail as shown in the below image and the requirement is to get all the employees whose age is equal to ’50’ and whose department is equal to ‘IT’.
To achieve the above requirement, we will create a flow that will trigger manually. To fetch all the items from the SharePoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of objects from the SharePoint list. Please refer to the below image.
In the ‘Filter array’ action’s From field, we will provide the array of the SharePoint list item that we got from the previous step and then expand the ‘edit in advance mode’. Here we will provide our condition using @and logical operator. We will check the condition where the ‘Age‘ column is equal to ’50’ and also if the ‘Department‘ column is equal to ‘IT’.
Power automate flow will automatically add the ‘Apply to each’ action to loop the array of filtered items and then we will add the ‘Send an email’ outlook action to send out the emails.
Here is the condition:
@and(equals(item().Department, 'IT'),equals(item().Age, '50'))
After successfully manually running the flow, we will receive the above email with the employee’s name that has justified both conditions as per the SharePoint list.
This is how we can join multiple conditions together using AND logical operator of the ‘Filter array‘ action in the Power Automate Flow.
Let’s move forward in this tutorial.
Read Power Automate Trigger Conditions
Power Automate filter array OR
In the above se action, we have seen how to use AND operator of the ‘Filter array’ action, now let’s learn how to join multiple conditions together using OR operator of the ‘Filter array‘ action in the Power Automate.
We will be using the same requirement as the above section, we have a Sharepoint list with employee detail as shown in the below image and the requirement is to get all the employees whose age is equal to ’42’ or their department is equal to ‘IT’.
We will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list. Please refer to the below image.
In the ‘Filter array‘ action’s From field, we will provide the array of the SharePoint list item that we got from the previous step and then expand the ‘edit in advance mode‘. Here we will provide our condition using @or logical operator. We will check the condition to get all the items where the ‘Age‘ column is equal to ’42’ or if the ‘Department‘ column is equal to ‘IT’.
Power automate flow, we automatically add the ‘Apply to each’ action to loop the array of filtered items and then we will add the ‘Send an email’ outlook action to send out the emails.
Here is the condition:
@or(equals(item().Department, 'IT'),equals(item().Age, '42'))
Once we save our flow and manually run it, we will get an array of items with employees’ detail. We need to loop this array of items to get the employee’s name in an email body. And this is how we can join multiple conditions using OR logical operator of the ‘Filter array‘ action in Power Automate.
Power Automate filter array Date
In this section, we will learn another important scenario, how to use date column in the ‘Filter array’ action using Power Automate.
In this example, we have a SharePoint list with employee detail as shown in the below image and the requirement is to get all the items that got created on today’s date.
Let’s start by we will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list. Please refer to the below image.
In the ‘Filter array’ action’s From field, we will provide the array of the SharePoint list item that we got from the previous step and then expand the ‘edit in advance mode’. Here we will provide the condition to check if the ‘Created’ that is a data-time column is equal to today’s date.
Here is the condition:
@equals(item()?['Created'], formatDateTime(utcNow(), 'yyyy-MM-dd'))We are using the formatDateTime() function to get the standard formatted today’s date to compare with the date-time field ‘Created’ column.
We can also use other operators to draw a comparison between the required date-time format and the ‘Created’ column like ‘greater than’, ‘less than’, etc.
Once we save and run the flow, we will receive all the items that got created at today’s date, however, as we don’t have any record in our SharePoint list that justifies this condition, we will not receive any email.
This is how we can compare the Date column of the Sharepoint list in the ‘Filter array‘ action using the Power Automate flow.
Read Power Automate IF Expression
Power Automate filter array multiple conditions
Let’s now learn how can we define multiple conditions in the ‘Filter array‘ action of Power Automate.
Suppose we have a Sharepoint list with employee detail as shown in the below image and the requirement is to get all the employees whose age is equal to ’50’ and whose department is equal to ‘IT’.
To achieve the above requirement, we will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list. Please refer to the below image.
In the ‘Filter array’ action’s From field, we will provide the array of the SharePoint list item that we got from the previous step and then expand the ‘edit in advance mode’. Here we will provide our multiple conditions using @and logical operator. We will check the condition where the ‘Age‘ column is equal to ’50’ and also if the ‘Department‘ column is equal to ‘IT’.
Power automate flow will automatically add the ‘Apply to each’ action for the array of filtered items and then we will add the ‘Send an email’ outlook action to send out the emails.
Here is the condition:
@and(equals(item().Department, 'IT'),equals(item().Age, '50'))
After successfully manually running the flow, we will receive the above email with the employee’s name that has justified both conditions as per the SharePoint list.
And this is how we can join multiple conditions together using any logical operator of the ‘Filter array‘ action in the Power Automate Flow.
Power Automate filter array of objects
In this section, we will see how to get the objets of array or items of the ‘Filter array’ action in Power Automate.
Suppose, we have a Sharepoint list as shown in the below image with all the employee details and we need the object or items of the array where age is greater than 41.
We will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list.
Here in this action, we are filtering the array of list items where the ‘Age’ column is greater than ’41’ by using the below condition in the advanced mode of ‘Filter array’ in Power Automate.
Here is the condition:
@greater(item()?['Age'], '41')Next, to get the objects of the array of the ‘Filter array’ action output, we will be using the compose action as shown in the below image.
We are using the length() function to get the count of the object in this array. And providing the body or the output of the ‘Filter array’ action as a parameter in the function.
Here is the expression:
length(body('Filter_array'))
After manually running the flow, we will receive the above length of the ‘Filter array” action items array as 3 in the output of the compose action in Power Automate and this is how we can get the object of the array in the ‘Filter array‘ action using the Power Automate flow.
Read Power Automate Multiple Conditions
Power Automate filter array SharePoint list
In this example, we will see how to use a SharePoint List item in the ‘Filter array’ action using Power Automate.
Suppose we have a Sharepoint list with employee details shown in the below image and we need to only get the employees from the ‘Chicago’ location. And we will send an email with the employee details.
We will begin by creating a power automate flow that will trigger manually, and then we will add the ‘Get Items’ action to fetch all the items from the given Sharepoint list on the Sharepoint site. We want to filter the array to get the object whose location is ‘Chicago’. Next, we will select the ‘Filter array’ data operator of the power automate flow.
In the ‘From’ field, we will provide the array of items that we will get from the pervious step the ‘Get Items’ action. Then we will click on ‘advance mode’, and we will write our condition to filter the array.
Here is the condition:
@equals(item()?['Location'], 'Chicago')Now, we have the array of filtered items. To send an email, Power automate automatically add the ‘Apply to each’ action to loop the objects of the array, and then we will ‘Send an email’ outlook action as shown below.
After saving and manually running the flow, we will receive 2 emails with different employee names as per our Sharepoint list and this is how we can filter items of the Sharepoint list using the ‘Filter array‘ action in Power Automate.
Read Power Automate Parallel Branch with Examples
Power Automate filter array Lookup column
In this section, we will learn a very important concept of filtering data using the lookup column in the ‘Filter array‘ action of Power Automate Flow.
In this example, we have a SharePoint list with employees’ details as shown in the below image. Here we have a lookup column named ‘JobTitle’ which we are getting from a different SharePoint list. We need to filter the items as per this lookup column and send the employee name in an email body.
We will create a flow that will trigger manually. To fetch all the items from the Sharepoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list.
In the ‘From’ field, we will provide the array of items that we will get from the previous step the ‘Get Items’ action. Then we will click on ‘advance mode’, and we will write our condition to filter the array with the lookup column.
Power automate flow will automatically add the ‘Apply to each’ action to loop the array of filtered items, and then we will add the ‘Send an email’ outlook action to send out the emails.
Here is the condition:
@equals(item()?['JobTitle/Value'], 'Analyst')
Once we save and manually trigger the flow, we will receive the above email with the employee name and this is how you can filter the data using lookup columns in the ‘Filter array‘ action of Power Automate.
Power Automate filter array Choice column
In this section, we will see another complex column of the SharePoint list or library. How can we use a choice column of the SharePoint list or library in the ‘Filter array’ action of Power Automate?
Suppose, we have a SharePoint list as shown in the below image with all the employee details and here we have the ‘Manager’ column which is a choice field. The requirement is to filter the data based on the value of the choice column ‘Manager’. We will also be sending an email to verify our results.
To achieve the above requirement, we will create a flow that will trigger manually. To fetch all the items from the SharePoint list, we will be using the ‘Get Items’ action. Next, we add the ‘Filter array’ action to filter the array of values from the SharePoint list. Please refer to the below image.
In this action, we will provide the array of items to be filtered in the ‘from’ field. We will use the ‘equal to’ operator to filter the employees based on the choice column ‘Manager‘ and we will click on ‘advance mode’ to write the below condition.
The Power automate flow will automatically add the ‘Apply to each’ action for the array of filtered items. Next, we will ‘Send an email’ outlook action to send an email with employee details in the email body.
Here is the condition:
@equals(item()?['Manager/Value'], 'Julia Wayne')
Once we save and manually trigger the flow, we will receive the above email with the employee name and this is how you can filter the data using choice columns in the ‘Filter array‘ action of Power Automate.
Conclusion
In this Power Automate tutorial, we have learned how can we utilize the ‘Filter array‘ action of the Power Automate flow, and we have also gone through the below-listed points:
- introduction of Power Automate filter array
- Power Automate filter array equal to
- Power Automate filter array not equal to
- Power Automate get the length of the filter array
- Power Automate filter array starts with
- Power Automate filter array contains
- Power Automate filter array remove the empty item
- Power Automate filter array greater than
- Power Automate filter array less than
- Power Automate filter array AND
- Power Automate filter array OR
- Power Automate filter array Date
- Power Automate filter array multiple conditions
- Power Automate filter array of objects
- Power Automate filter array SharePoint list
- Power Automate filter array Lookup column
- Power Automate filter array Choice column
You may like the following Power Automate tutorials:
- Power Automate email body formatting
- Power Automate Do until
- Power Automate create a task in Microsoft Planner
- Power Automate Copy Folders
- Power Automate Copy Files
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com