“Is there a way how could I use Power Automate to remove rows from an Excel table where a specific column is empty?”
When you work with an Excel file, the ‘List rows present in a table’ action allows you to filter rows based on a value. But using the filter you only skip the rows in the flow, they still stay in the file. If you’d like to remove all the empty rows from the file, you’ll have to update it. Keep only the valid rows, remove the rest. Which might be a problem, as unlike SharePoint rows there’s no id for Excel rows. And without some kind of a unique identifier you can’t identify the rows and delete them. Not to mention that the ‘Delete a row’ action won’t let you use an empty value as the Key Value.
It’s not possible to delete rows with empty values
Given the limitation mentioned above, I don’t think it’s possible to delete empty rows. To find and delete a row you need the Key Column and Key Value, where both of them are mandatory fields. And as such it’s not possible to use an empty value. There’s no unique identifier of a row which you could use, therefore, you can’t delete an empty row. If you need a file without the empty rows, you’ll have to create a new file.
Create new file without the empty rows
You should use a Filter Query in the ‘List rows present in a table action’ to skip all the empty rows.
<ColumnName> ne ''Note: <ColumnName> is a placeholder for the column name, replace it including the < and >. It must be a single word, if your column has multiple words in the name you’ll need a ‘Filter array’ action.
The action will return only the rows with a value as the output. You can take it and create a new file, let it be a .csv file or an Excel file, and then replace or update the original file.
Summary
Unfortunately, Power Automate doesn’t allow you to remove empty rows from an Excel file. The ‘Delete a row’ action needs an identifier of the specific row, and it can’t be empty. And since you’re trying to remove the empty rows, it’s a problem. The only solution is to recreate the file. Filter only the rows with value, and use them to create a new file.
This flow uses an Office Script to batch delete Excel table rows. And it actually does this by taking in a set of primary keys & updating each row with those keys to a blank row. Then it uses the remove duplicates command to delete all blank rows:https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735
I’ve an Excel file used as a template with formulas in a few of the columns in the first row. Without this row, the formulas don’t populate. When Excel populates the workbook it leaves the first row blank so I found the solution is to either hide the first row or leave a key value “REMOVE” or “DELETE” in that row to use as Key in Power Automate to delete it.
The flow duplicates the file, populates the copy with data, deletes the extra “blank” row and then sends a link to the requesting user.