Power Automate: When Excel can make things easier

The idea for this article came from a comment posted on the website where we were trying to figure out the solution to filter an Excel file based on a date. Easy enough with ODAta, but since Excel’s action doesn’t support “greater than” as an OData operator, we would not be able to do it. But what about if we transfer the responsibility to Excel? This way, we can do a simple query in Power Automate, and Excel will deal with the date efficiently.

Today will explore when not using Power Automate is the best solution.

When Excel has a function for that

Some functions don’t exist in Power Automate but exist elsewhere, like Excel. In this case, who has to build very complex to filter the data in Power Automate. Even if it’s not complicated, we would need to fetch all data and then filter it, and depending on the size of the Excel, it could take a while to do it.

Let’s look at an example to understand this concept better. Let’s say you want to calculate business days after a specific date, like add five business days to date. To do that in Power Automate, we will have to do very tricky calculations that will undoubtedly return invalid results. Think about time zones, daylight savings, and other things that make dates super complex. Excel whoever has a function that takes care of this quickly to get the data, send it to Excel, and get the results back. Excel will work here as the temporary area that helps us do the calculations.

I explore this more in this example in this article, so let’s look at the question that triggered this article. The idea was to fetch information from 8 business days ago or earlier. To solve this, our Excel can have an extra column with the calculation, and then we would only need to do a simple OData query. In our case, I split into multiple columns to have a more precise formula, but you can have them all in one step or hide the columns that you don’t need to show.

The columns are:

1. A unique identifier for a row
2. The date that we want to do the calculation
3. The days to add (or remove in our case)
4. Today’s date (I included this one so that you can see the date)
5. The limit that we want to fetch in Power Automate
6. Do we want to fetch it in Power Automate?

The “Date Limit” column has a simple formula:

=WORKDAY([@Today];[@[Days to add]])

The “Parse” Column contains the following formula:

=IF([@[Start Date]]>[@[Date Limit]];"YES";"NO")

The “WORKDAY” function will do all the calculations that we need. Note that we can even hide the “calculation” columns, showing only the first and second columns.

This way, the query in Power Automate be as simple as.

We will get only the results you need and transfer all the complex calculations to Excel, where it doesn’t even break a sweat because it has a function to do it.

Let’s look at another example to bring the example home.

Translating some data

As we all know, translations are complex, but we need to display data to the users, and some of them don’t speak English. Power Automate is correctly translated into multiple languages; however, it depends a lot on the original settings, and this may not be the language you want to display. For example, let’s say that you have a Flow that shows the day of the week or the month, and based on the person’s country in the database, you want to send an email with that value translated in their language. It will be tough to do this correctly in Power Automate without having many “if statements,” but using Excel again, we can easily use functions and language ids.

Here’s how to translate the day of the week in excel.

=TEXT([@[Date Limit]];"[$-0816]aaaa")

You can find the list of languages and corresponding IDS here, but 816 is the code for “Portuguese.” So here’s what it looks like “Wednesday” in Portuguese.

Notice that we only need to do one thing. Add the language ID to the formula, and that’s it. Super helpful and easy to get. Again, you can hide these columns from the original Excel and have them only as “helper” columns in Power Automate.

Advantages/Disadvantages

Using Excel is super convenient because we all know how to use Excel; however, there are some things to consider and consider. The first is that Excel is not prepared to deal with concurrent requests. If the Flow is used by multiple people or fired numerous times in a short period, it will probably result in issues or return invalid data. In the examples above, we’re only querying information, so it’s ok, but in the example in this article, we’re providing a value in Excel to be parsed. Be careful so that you don’t get invalid data.

Also, keep in mind that this is a workaround, meaning that you should use it if you have a more sustainable tool. The purpose is to demonstrate that we can look at certain things outside Power Automate to take care of a task that is not possible there.

Finally, using Excel files means that you have to be careful where to put the file so it cannot be deleted or changed accidentally. If the Excel changes or is deleted, the Flow will stop working, and that’s something we don’t want to happen.

Final thoughts

Is this the only solution to this problem, or is Excel the “silver bullet” that solves everything? Of course not, but Excel solves our problem efficiently, and that’s what we need. Of course, there are many more tools that we can use with the same strategy, but the strategy part is what’s important here.

From this article, I want you to take that if you have a task too complex to do in Power Automate, you can probably stop and think a bit about other tools that can help perform the same tasks efficiently.

Photo by J W on Unsplash