Skip empty row(s) when processing Excel file in Power Automate
Posted On December 27, 2020
“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.
As you can see, you can save a lot of work using the ‘Filter Query’. Instead of processing all the rows you take just the ones you need. 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.