Excel data used in check is variable, causing errors

[AussieD]

I'm working on a flow that takes an Excel inventory sheet, goes through each table in all tabs, and checks a calculated expiration date column labeled here as "days to exp". If any of the calculated values fit a criteria then the information from that line is placed in an email and sent as a reminder to complete an inventory check.

Yes i know there are better programs than excel, don't get me started lol.

The table looks like this:

I've Omitted the unnecessary/confidential portion with the red boxes. but my issue is where I've placed the star icon.

Not all of our materials have associated expiration dates and you can see above that at line 29 the first blank "Days to expiration" shows up. This empty space results in the following error in my flow:

First Note, the flow error says this is at step 26 and the line is step 29. this is because there are 3 rows of headers before the table starts so 29-3=26 and the correct first issue.

Second note, below is where the erroring step is in the process. before it is a get tables function and then two for each loops that run for all the tables in the document and then for each row of each table:

Third note, the step that is checking is using the following code:

and this is the core of the issue. The information being passed in from excel is variable and the check is expecting a standard format for the data. Hence the error saying that a parameter is invalid

My Question:

How do i write a condition that can accept either a blank entry or a text entry to convert to a number for the comparison point?

[creativeopinion · Accepted solution]

@AussieD Instead of nesting a List Rows Present in a table action inside an Apply to Each action—I'd recommend pulling that action outside of the Apply to Each loop and using a Filter Array action instead. It would help your flow to run more efficiently since you'll only need to call the List Rows Present in a table action once.

Refer to this YT Tutorial on how to use the Filter Array action: Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

----

To convert a numerical string of text to a number—use the int() function.

int([string of text here])

To avoid an error in your flow you'll need to check if the cell is empty first. Use an empty() function.

empty([days to exp dynamic content]) 

 This will return a true or false value.

To set a number for the empty value (such as zero) you'll need the if() function. The if() function takes 3 parameters.

if([boolean],[value if true],[value if false])

Your expression would look something like this:

if([days to exp],[0],[days to exp])

However, if you adjust the logic of your flow you may not even require this statement. 

For more flow troubleshooting tips—check out this YT Tutorial: 5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

IN THIS VIDEO:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response! If you're feeling generous— ☕️  Buy me a coffee: https://www.buymeacoffee.com/acreativeopinion

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[AussieD]

I appreciate the detailed and thoughtful response! Thank You 😁 

Follow up Question:
Wouldn't i still need a nested list rows action if my excel document has multiple sheets that each have a table?
Taking the list rows present in a table action only allows for the selection of a single table from the entire workbook.