2 Ways to Delete All Rows in Excel with Power Automate

Do you want to delete all the rows in your Excel table with Power Automate?

You might be using your Excel table as a temporary storage for your data. Perhaps during the flow, you want to reset the table to empty by deleting all the existing rows.

There is no action to delete all the rows in your table, but there are still ways to get this done.

This post will show you how to delete all the rows in your Excel table within your flow.

Delete All Rows with the Delete a Row Action

This first method will only work if your Excel table data has a key column.

This is a column that uniquely identifies each row of data in your table.

If your data has this key column, then you can use the List rows present in a table action to list all the key column values and then use these in the Delete a row action to delete the row.

  1. Add the List rows present in a table action to your flow.
  2. Select the Location of the file.
  3. Select the Document Library the file is in.
  4. Select the File.
  5. Select the Table within the file in which you want to delete all the rows.

The List rows present in a table action will only retrieve the first 256 rows of data in your Excel table by default.

If your table will have more data then you need to turn on the pagination settings and increase the threshold.

  1. Click on the Ellipses icon in the List rows present in a table action.
  2. Choose the Settings option from the menu.
  1. Turn On the Pagination.
  2. Add a large number to the Threshold input to allow the flow to return more rows from the table.

Now that you have the Excel table data you can use this to delete each row based on the key column.

  1. Add the Detele a row action to the flow.
  2. Fill in the Location, Document Library, File, and Table details to be the same as the List rows present in a table action.
  3. Select the Key Column in the table. This needs to be a column with unique values.
  4. Add the dynamic content for the key column from the List rows present in a table action into the Key Value input. In this example, the key column is the ID column.

When you add the dynamic content from the List rows present in a table action, Power Automate will automatically add an Apply to each action to the flow.

This allows the Delete a row action to loop through all the rows and deletes them.

This will delete each row one by one, so it can be very slow. But you can speed this up by adding a degree of parallelism to your Apply to each action.

  1. Click on the Ellipses icon in the Apply to each action.
  2. Choose the Settings option from the menu.
  1. Toggle On the Concurrency Control setting.
  2. Increase the Degree of Parallelism to the maximum number of 50.

This will allow flow to perform 50 of the delete actions at the same time, helping to drastically increase the speed of the flow.

Delete All Rows with an Office Script

The previous method can still be slow when you have thousands of rows of data in your Excel table that you need to delete since it can delete at most 50 rows at a time.

There is a more efficient way.

You can use Office Scripts to delete all the rows and use the Run script action from the flow.

This won’t require you to get all the rows and loop through them.

Here’s how you can create an Office Script in Excel.

  1. Go to the Automate tab in Excel.
  2. Click on the New Script command.

This will open up the Code Editor where you can add your Office Script code.

function main(workbook: ExcelScript.Workbook) {
	let myTable = workbook.getTable("Orders");
	let rowCount = myTable.getRowCount();
	myTable.deleteRowsAt(0, rowCount);
}
  1. Paste in the above code so that it replaces everything in the Code Editor. This will delete all the rows in a table named Orders, but you can adjust this in the code to suit your needs.
  2. Give the script a descriptive name such as Delete All Rows.
  3. Press the Save script button.

Now you will be able to run this script from your flow.

  1. Add the Run script action to your flow.
  2. Select the Location, Document Library, and File that contains the table where you want to delete all the rows.
  3. Select the Delete All Rows script from the Script dropdown menu.

Now the flow will run the script and the script will delete all the rows from your Excel table.

Conclusions

Power Automate doesn’t have a prebuilt action to delete all the rows in your Excel tables, it only has an action to delete a single row based on a key column ID.

This means you will need to list all the rows first and then loop through them and delete each one by one.

The other option available is to use Office Scripts to delete all the rows and then call the script from your flow.

Have you ever used these techniques to delete all the rows in your Excel tables? Let me know in the comments!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Related Articles

Comments

2 Comments

Get the Latest Tech Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in tech!