If your organization doesn't restrict its usage, you can go with Office Scripts for this task. No loops, no worries with key columns - just clear a table with a single action and that's it.
I'm sharing some blogs that I posted about Office Scripts, so you can have more context about it, but as a short description, it works like a VBA for Excel on the Web and it can be initialized by Power Automate:
Here is the table that I will use as example, containing 100 entries and many repeated values in both columns:
First thing to do is to create your Script, which will look like this. Go to Automate tab and click New script:
Make sure to replace the text in green ("Table 2") for the actual name of the table that you want to clear:
function main(workbook: ExcelScript.Workbook) {
let table = workbook.getTable('Table2')
table.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up)
}
Just these two lines of code are enough. You will basically access the table object (line 3) and then delete all entries (line 5).
After saving the Script, go to Power Automate and add a Run Script action, informing the file where you want to run it and the script to be used (the one that you just saved):
That's all: you can now run the flow (for these 100 records, it took 8 seconds to run):
And once you're back to Excel, the table will now be empty:
Let me know if it works for you or if you need any additional help!
✅
If this solved your issue, please mark it as Accepted Answer.
👍
If it helped, feel free to give it a like!
🌐 Explore more Power Platform content on my
Website or on my ▶️
YouTube
💼 Find me on
LinkedIn
