Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

Remove Excel rows with empty value with Power Automate

Posted on December 19, 2021December 19, 2021 by Tom

“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.

Power Automate Excel remove empty rows

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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

1 thought on “Remove Excel rows with empty value with Power Automate”

  1. Tyler says:
    December 6, 2022 at 4:51 am

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with 1000s of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes