Power Automate: Multiple Conditions in Filter Array

I’m sure you need to filter arrays all the time. It’s a common action, and it’s simple to do – if you have only one condition. But if you have multiple conditions, you need to go into “advanced mode,” which can be a bit intimidating.

Today we’ll explore how the “advanced mode” works and how to take advantage of multiple conditions to filter the data.

Test Case

Let’s think about Excel. You have some information that you want to filter but only during your run. I’ve seen this case showing up on the Power Automate Community, where people have lists of tasks (could be Excel or SharePoint Lists) and send emails to people when the tasks are due.

So let’s set up an Excel with that. A task name, a due date, a person’s name, and an email address.

For reference, I’m writing this article on the 5th of March 2021.

We want to group the tasks into the only email and send them to the person in question.

The first part is straightforward.

Let’s first get all distinct emails and put them in an array. Then we can go through each and filter the array with only those. To do that, we need:

1. An empty array variable
2. “Apply to each” that fetches all emails.
3. If the email doesn’t exist in the email, adds it. If it exists, do nothing.

Here’s how to do it.

Now that we have an array, let’s go to the second part.

The second part no so much, but let’s take it step by step.

I wanted Excel because it will show us one thing that we usually forget. Excel returns the dates as numbers and not as dates. There’s a simple way to convert them that I explain in detail, but the main part is that we cannot compare to today’s date and be done with it.

We need to build the email to send so, for that, let’s go through all distinct. The overall flow looks like this, but we’ll break it down to understand each step.

Apply to Each

We need to go through each distinct email, filter the data and send the email. Since we only want to send one email, this is the easiest way to do it. We can use the array that we built in the previous step to achieve this. Ok, so far, so good.

Compose

This composes optionally, but I like to have it.

It will store the current value, in our case, an email. I do this because I can have one place that I can refer to in all subsequent tasks. Otherwise, I needed to refer to the “current item” everywhere. But let’s imagine that future Excels would have issues in the email addresses that I need to fix. I could go to this Compose, fix them, and don’t change anything in the rest of the Flow. If I didn’t have it, I would need to propagate the same code over and over again, and that’s error-prone:

Filter Array (the scary part)

Ok, let’s take a deep breath and look at the formula:

@and(equals(item()?['Email'], outputs('Compose')),not(equals(item()?['Completed'], '1')),less(addDays(formatDateTime('1-1-1900','dd-MM-yyyy'),sub(int(item()?['Deadline']),1)), utcNow()))

Let’s break it down into parts:

@and(<Condition 1>,<Condition 2>, <Condition 3>)

We want to do 3 checks:

1. If the email is the one that we are currently using to filter?
2. Is the task not completed, in our case, different than 1? Why not 100? Good question. Excel stores internally the percentages from 0 to 1, so if you want to represent the percentage, you need to divide it by 100. For example, 23% would be returned as 0.23
3. If the deadline is over the current date

In the first condition, we’ll check if the current item’s email is the same as the one we have in the compose. If it is, then it’s one row that we want to get.

equals(item()?['Email'], outputs('Compose'))

The second condition will check if the current item’s completed value is different than 1.

not(equals(item()?['Completed'], '1'))

Since we want different or not equal, we use the “not()” function.

The last one is a bit more complex, but you can understand it better if you read my “Power Automate: Convert Excel Number to Date” article.

less(addDays(formatDateTime('1-1-1900','dd-MM-yyyy'),sub(int(item()?['Deadline']),1)), utcNow())

We want the date to be less than the current date (represented by “utcNow()”). To do that, we need to format the number that we get. That number is the number of days since the reference date (the 1st of January 1900). So we add the days (the number from the Excel) to that date and get the formatted date.

There are 2 details to take into consideration:

1. The number comes from Excel in a string format. We need the “int” function to convert it to an int.
2. The date is subtracted by one. The reference day is “day 1”, so if we add the number, we will get tomorrow’s date since the number already contains the reference day. Again, my “Power Automate: Convert Excel Number to Date” article will explain this further.

Now that we have the data

We don’t want to send empty emails, so we have a condition that checks if the filter result is empty. If it is, skip to the next one.

The formula is:

length(body('Filter_array'))

We use the length function to know how many elements we have in the array.

Building the table is quite straightforward. We pick the column names we want and fetch the items for those arrays:

The formula is:

item()?['Task']

Sending the email

OK, welcome to the last step, and it’s an easy one. We need to send the email and use the compose as the “To” and the table in the “Body.”

Final thoughts

All of this looks quite complex, but I wanted to show you many sneaky details that can make you scratch your head; if you understand the why in these details, you won’t be surprised when some of them show up in the future.

Photo by JJ Ying on Unsplash