Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Triggers
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
  • Get help with 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!


NEW! Master the HTTP requests to SharePoint with a new cheat sheet!

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.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Use Power Automate to forward Outlook events upon registrationJanuary 29, 2023
  • Why the condition is false for the same numbers (Power Automate)January 25, 2023
  • How to forward event invitation to other calendar (Power Automate)January 22, 2023
  • Run ‘For selected item’ flow from non-default environment (Power Automate)January 18, 2023
  • Hide button in SharePoint list after Power Automate flow startedJanuary 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes