Extracting data from Excel using Power Automate (Part 6)

Using a condition to deal with empty rows

One thing that I ran into was that I had a table size that was larger than the data set inside the table. e.g. our faculty list had 850 filled rows, but I had set the table to be 1000 rows so that the data set could grow and not inadvertently cut off the newest faculty members because someone didn’t update the table size.

What I did not realize was that Power Automate would throw an error because it encountered an empty row. It gave me an esoteric error that I did not correlate to “empty cell” or “missing data” until I happened to click on “next failed” record in the run history.

I really wish that developers of computer programs would learn to speak human. A simple error of “Hey, this field is blank” would have sufficed. Having to Google error text is really not a great user experience.

I looked for all kinds of ways to create expressions for finding empty rows but they were all so complicated, without full instructions, that I decided to look for something else. I decided to go the easy route.

If you have a field like ROW NUMBER, that’s great. Technically, you could pick any field that would generally never be empty, like ID number, or name, or something else.

In my spreadsheet, I already had a field called ROW NUMBER that contained integers.

Inside the Apply to Each action, I added a condition action that said if ROW NUMBER is equal to

and then left the other field blank.

I then moved all the remaining Apply to Each actions into the NO column so that if ROW NUMBER is not blank, perform the actions.

If you’re one of these people who prefers to have all actions in the YES column, you would change the condition to ROW NUMBER is not equal to, and leave the field blank.

Adding this condition to the whole process allowed the flow to continue whether the row is empty or not.