Although Excel is known for being a standalone application, there’s a lot that we can do with Power Automate and tasks that we can automate. Today we’ll explore how to automate the deletion of rows in an Excel file by using the “delete row” action. This action works for files in SharePoint, Teams , or OneDrive for Business so let’s look at how to use it.
Where to find it?
You can find it under “Standard”.
Pick “Excel Online”. You may see or need to expand the list depending on your suggestions. Also, since it’s a common action, the “delete row” action is displayed in the list, as seen in the image below, so that you can pick it right away.
Select “Delete a Row” action:
Here’s what it looks like.
Power Automate tends to save the most common actions on the main screen, so check there before going through the full hierarchy. Also, you can use the search to find it quickly.
Now that we know how to find it let’s understand how to use it.
Fields
You can find some fields to configure before using the “delete row” action, so let’s look at them individually.
Location
As mentioned before, we can use SharePoint, Teams, or OneDrive For Business to find our Excel files. To do it, select the location where you want to find the data. For example, for OneDrive For Business:
As you see above, you have a lot of “Groups” sites that are commonly Microsoft Teams sites.
If you’re looking for SharePoint sites, they are commonly displayed like this:
Since they all behave the same way, I’ll refer only to SharePoint sites from now on, but keep in mind that you can also use Microsoft Teams and OneDrive For Business. I’ll highlight the differences when appropriate.
Document Library
Since SharePoint sites can have multiple document libraries, we need to pick them from the list.
There’s always one created by default called “Documents” or something different depending on your language, but since it’s a mandatory field, you need to pick it; otherwise, you won’t be able to choose the file from the following field.
The same behavior occurs for OneDrive For Business, even if it’s not possible to create document libraries. Microsoft creates a document library that you can pick from the list.
File
Now that we have the locations defined, let’s look at a file. You can pick the file in the “File Browser”.
Or you can provide the path yourself, although I discourage this. Although you can do it, Power Automate sometimes returns strange errors related to the path, especially if the file has special characters. It’s always recommended that you can, of course, select the file directly in Power Automate’s UI.
Table
Power Automate requires the information inside an Excel table to access the data. To find the table, you can go to your excel file, select a row and then check if you have a “Table Design” tab in the ribbon.
If you don’t, then chances are that you need to create a table in Excel before you can continue. Select the table in the dropdown.
The Excel file can have multiple tables, so it’s important to define the one you want to manipulate the data. Finally, you can provide the table’s name as a “dynamic field,” but to ensure that your Flows always work, I recommend picking it from the dropdown.
Key Column
Now that we have a file, we need to tell Power Automate how to identify the row we want to delete. Since there could be multiple tables in Excel, Microsoft required a key column that identifies the row we want to delete inside the defined table. If you don’t have one, you can use the “Add a key column to a table” action that will do it for you.
Since we provided the path before, Power Automate checks the file and gives us a list of the columns in the provided table.
Please be aware that if you dynamically define the path, table, or file, you need to provide the “key column” name since Power Automate won’t find the list of columns before the Flow runs.
In case you don’t know what “Key Columns” are, I have an article here that goes into a lot of detail explaining them.
Key Value
Finally, we need a key value that identifies the row we want to delete from the table. Notice that we’re deleting the item by providing an identifier, not the row number since the same row can be inside multiple tables. The key value needs to be unique within the “key column” and should be a number.
Usage
Let’s look at a test file with anonymous employee information.
Let’s say an employee leaves the company, so we won’t automatically delete their information. For our example, we’ll use OneDrive for Business, so here’s what it looks like:
After we run, let’s look at the file and see that the “ID” value 3 is no longer there.
Be careful to ensure that you have proper error handling, especially after this action, since the action will fail if the ID cannot be found with an exception like this:
No row was found with Id '3'.
It’s debatable if the “Delete a row” action should fail when the row cannot be found or keep the file as is, but this way, Microsoft enables us to have different paths in case the file contains the element to be deleted or not. It gives you more flexibility to react in different scenarios, but you must work to ensure your Flow doesn’t fail.
Limitations
It could have been a temporary problem, but I got a lot of issues with some key column names, like “identifier” for example. If you’re having issues with Flow returning strange error messages, consider changing to something like “Key Column”.
Although at this point should not be considered a limitation anymore, the “delete a row” action will only support Excel Workbook (xlsx) files, so you need to convert your Excel file before you can use the action.
Recommendations
Here are some things to keep in mind.
Name it correctly
In this case, the name is super important to provide the context of what we’re deleting. Always build the name so that other people can understand what you are using without opening the action and checking the details.
Always add a comment
Adding a comment will also help avoid mistakes since we can indicate the reason for deleting the Excel file row. Also, if you’re adding any elements dynamically, it’s a good practice to explain how the values are obtained. Indicate where the path comes from, for example, if it’s calculated and how. It’s essential to enable faster debugging when something goes wrong.
Always deal with errors
Have your Flow fail graciously and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked-for a while or generate even worse errors. I have a template that you can use to help you make your Flow resistant to issues. You can check all details here. Remember that if the “Key Value” doesn’t exist in the “Key Column,” the action will return an error, so it’s essential to have a parallel branch that catches these cases.
Back to the Power Automate Action Reference.
Photo by Katrin Hauf on Unsplash
Delete a SharePoint list item when the Excel row is deleted using Power Automate, can you help with this flow.