Excel Batch Delete (40x Faster)
Instead of performing a slow 1 deletion per action, these flows send arrays of deletion primary keys to an Office Script. On the Excel side the script can then delete each row it finds with a primary key in the deletion input array.
In testing the flow completes 1500 row deletions per minute, about 40x faster than the standard Excel delete row actions at max 50 concurrency. And it accomplished each 750 deletions with less than 8 actions or about 1% of the standard 750 action API calls.
The "results" output of the Run script action returns the change in the number of rows, so the number of rows removed since the script started running. The last few actions will check if the number of rows in the deletion array match the change in number of rows & force a flow failure to make it clear that there was an error.
Version 3.1 closer to what is reviewed in the YouTube video is still available here. But for version 4 you can follow the Import & Set-Up instructions below.
Version 4 Import & Set-Up
Go to the bottom of this post & download the BatchExcel_1_0_0_xx.zip file. Go to the Power Apps home page (https://make.powerapps.com/). Select Solutions on the left-side menu, select Import solution, Browse your files & select the BatchExcel_1_0_0_xx.zip file you just downloaded. Then select Next & follow the menu prompts to apply or create the required connections for the solution flows.
Once imported, find the Batch Excel solution in the list of solution & click it to open the solution. Then click on the Excel Batch Deletes V4 item to open the flow. Once inside the flow, delete the PlaceholderValue Delete after import action.
Open the Office Script Batch Delete action to the script code. Select everything inside the compose input & control + C copy it to the clipboard.
Then find & open an Excel file in Excel Online. Go to the Automate tab, click on New Script & remove all the placeholder script code.
Control + V paste the Batch Delete script code from the clipboard into the menu. Then rename the script BatchDeleteV4 & save it. That should make the BatchDeleteV4 reference-able in the later Run script flow action.
Go to the Batch Delete Excel scope to the List rows & Filter array actions. Input your Location, Document Library, File, & Table for your dataset.
Then in the Filter array action you can add any more complex filters to filter down to just the rows/records you want deleted.
Then in the Run script Delete Excel Rows action input your Location, Document Library, File, & Script. Once you enter the correct script it should load more parameters where you can enter your Table Name, Primary Key Column Names, & Delete Data. The DeleteDataPrimaryKeyColumnName should be the column name of the primary key from the data that is being fed in the DeleteData input. DestinationPrimaryKeyColumnName should be the column name of the primary key from the destination Excel table where the data is being deleted. In the regular Excel Batch Delete V4 flow & any flow where one is pulling from the destination table itself in the List rows to tell what rows to delete, the two PrimaryKeyColumnName inputs will be the same because they are both the primary key column name from the destination table. So they may only be different when the DeleteData is not coming from the destination table data.
For the DeleteData input, when you initially open this script action you may need to select the right-side toggle to "Switch to input entire array" to get a single input box for an array. There you should enter the dynamic content output for the action immediately preceding the Run script Delete Excel rows action, like "Filter array More filters Excel" or "BatchRecords".
And please remember to check your dataset & who might be using parts of it before running a large batch delete that may affect their work.
Thanks for any feedback,
Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).
And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.
Office Script Code V4 (Also included in a Compose action at the top of the template flow)
https://drive.google.com/file/d/1yLqB9Od_r3Mk9wvzBqubBO2bMzm4Ye3U/view?usp=sharing
If the legacy import method does not work, see this alternate Solutions package import method: Re: Excel Batch Delete - Page 5 - Power Platform Community (microsoft.com)
watch?v=LrzjH9dI0is
That worked perfectly. Thank you for the quick response. Would you happen to have the Sync version of this flow without the table references?
Nice! That worked also, I am all set up.
I did notice something that when using the V2, no matter what, the Condition Check always yields "false". The target table rows get deleted just fine but the result of "false" is unexpected. The "false" result happens whether I have over 750 rows or under 750 rows in the table. Since the rows get deleted the "false" result is not a big concern, just something that I noticed and could not figure out how to fix. I assume that it is having trouble with Initial Row Count 4, Number of Rows Deleted 3, Final Row Count 2.
Thanks again for the help on the .zip files.
How many rows/key values did that run have in the Select Get only key values action?
Were there 3 or 2 items listed in that action? Because it checks the length of that action vs the script result, not necessarily vs the other info in the script details.
There were 3 values in the Select Get only key values action.
I have not touched these conditions.
That makes sense, 3 rows in & 3 rows deleted.
If I remember correctly I designed it to compare those 2 instead of relying on the number of rows remaining because of this very scenario when someone deletes all the rows in the table. Because the count rows type of expression will count the extra blank row that remains in an empty table. So that’s when the remaining rows won’t line up with the number actually deleted.
So if you run a batch delete & leave a few rows in the table, then the conditions & info should make more sense. And it will trigger if something goes wrong in the script.
That makes sense, 3 rows in & 3 rows deleted.
If I remember correctly I designed it to compare those 2 instead of relying on the number of rows remaining because of this very scenario when someone deletes all the rows in the table. Because the count rows type of expression will count the extra blank row that remains in an empty table. So that’s when the remaining rows won’t line up with the number actually deleted.
Got it, it wasn't what I expected but it now makes sense. Thank you and have a great holiday weekend.
Hi @takolota
Another very useful Flow!
One issue that I ran into when running V2b was that the Flow is leaving 300 ish lines in the table and fails.
I have to run the flow a second time to get it to clear out those remaining lines.
The script seems to recognize those lines:
And the list of primary keys seems to generate correctly:
Any ideas?
Hello @Reinand
How is the rest of the flow set up? Could you share screenshots?
Are you trying to sync deletions across datasets or have you removed a lot of the sync pieces so you can just delete everything in the table?
Thanks,