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)
- ExcelBatchDeleteV4 is the core piece.
- ExcelBatchDeleteV4Sync includes a simplified method of filtering to just the rows in Excel that are not in another dataset, but is limited to data sources & Excel tables with 100,000 rows or less.
- ExcelBatchDeleteV4b includes a Do until loop set-up if you plan on deleting more than 750 rows in a single flow run. It also includes a fuller set-up that can filter to just the rows in Excel that are not in another dataset with a 300,000 table row limit that can be further expanded by following the pattern of intersections, joins, & filters in the "Get all data, filter to items in destination not in source" Scope.
