“My Power Automate flow errors out because there are blank values in the processed Excel file, how can I skip the empty row(s)?”
To process an Excel file in Power Automate, you must ‘List rows present in a table’. The action will take every row and add it to an array. It doesn’t matter if the row has data or if it’s empty. If it’s in the table, it’ll be processed. Unless you filter the empty row(s) out.
Using ‘Filter Query’
The ‘List rows present in a table’ action itself supports filtering. When you ‘Show advanced options’ you’ll see a field ‘Filter Query’. Here, using an OData filter, you can define which rows will the action return. Format of the query is below.
<ColumnName> <operator> '<value>'
e.g. filter where column ColumnName is not equal to empty value:
ColumnName ne ''
Note 1: the column name can’t contain any spaces, it must be a single string.
Note 2: the filter currently supports only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ operators.
You pick one column in your Excel which must have a value. If that specific column doesn’t have any value, skip the whole row. If the column has a value, keep it in the array.
The ‘value’ output from such ‘List rows present in a table’ action will be an array that will contain only rows that fit the condition. In this case it will be only rows where the selected column has a value.
Summary
As you can see, you can save a lot of work using the ‘Filter Query’. Instead of processing all the rows you use Power Automate to take only the ones you need, e.g. filtering out the empty row(s) from the Excel file. Your flow will be easier to read, understand, develop and debug. The benefits of filtering vs condition in Power Automate apply to all sources, no matter if it’s an Excel file or SharePoint list.
This post was only on filtering empty rows, but you can use the same process, just with different operators and values, to get your desired results. Just make sure to check what values are passed from the Excel file if you encounter any problems.
Hi! Can i filter more than 1 column in Power Automate, since we have just 1 filter field?
And, let’s suppose that i want to exclude 2 values from the same column, is it possible?
Hello Edinho,
you can filter by multiple fields, but you must use the advanced edit mode and add the conditions in @and(…) or @or(…), take a look on the trigger condition post, it’s similar approach.
Thank you! That’s what I wanted.
Working perfertly.
Hi!
When I try to use the filter query that you explain in this article, I get an error in the “List rows presented in a table” action. The error says “The requested resource does not exist…”. If I remove the filter query the action works fine. Questions:
– Does the column name I use in the query have to be the first column in the table? (I don’t use the name of the first column since that name has multiple words, see my next question)
– The column name I use in the query is a single word, but I have other columns in the table with multiple words in the name. Could this be the cause of the error?
Hello Daniel,
it can be any column in the table, but it must be a single word. If your columns have multiple words you’ll have to list all the rows and filter them later using the ‘Filter array’ action.