06-06-2023 07:47 AM - last edited 06-06-2023 07:48 AM
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
Hello @takolota
Since I am just trying it out, I didnt remove anything.
I am using a source file with just 1 line and that line will not appear in the destination.
Hello @takolota ,
I've remove most of the cross checking with host and destination and only kept the parts needed to remove everything, however unfortunately I am having the same issue:
The Flow seems to run into an error, but no error message is given, so I'm unsure on what could be causing it:
Any advice would be appreciated.
It’s “failing” with the terminate action on purpose because an error was found while running the script.
The most likely error is that the number of rows / values sent to the script to be deleted do not match the number of rows the script actually deleted.
You can try to investigate the data to see why this is or if you don’t care if some extra item(s) were / were not deleted, then you can remove the section checking for deletion / script errors & it will continue to loop / run.
I've been looking into it a bit more, but am still unable to figure out the exact issue.
To make sure that my data isnt the issue, I set the values in the Key column to just be 1 to 4806 (total number of lines in the file).
When even this didnt work, I removed the checking for deletetion / script errors section and the error I receive is the following:
I've also tried lowering the batch sice, but all that did was increase the number of lines left behind in the file.
Any ideas?
This is line 29
The key itself isnt a problem since I used sequential numbers:
The only thing I can think of is that the flow is not properly splitting the table into batches and is sending the entire list with each iteriation again to script (hence it always fails on the 2nd iteration of the "Do until").
I will rebuild it in case I removed something I shouldnt have.
@Reinand
In that case, this is a piece you may want to pay attention to. The expression is supposed to take only the relevant batch data each time through these take & skip functions & the batch size variable.
You may need to replace "body('Union_all...)" in the expression with whatever you are using to get your deletion rows.
And we may want to double check & make sure like the last row in batch 1 isn't showing up as the 1st row in batch 2.
I rebuilt the entire thing and the issue is still there.
After a bit more digging the problem seems to be with the script.
After counting, the first iteration of the "Do until" send an input of 500 lines to the script.
But for some reason the script is deleting 4432 lines instead of only 500.
I'll deleted the script and recreated it in case something copied over incorrectly, but no luck.
Do you have extra blank rows or exact duplicates in your table?
The only thing I’m thinking that would do that is if the table had duplicates in it as the script actually deletes rows by making each deletion row completely blank, then removes all rows that are exact duplicates, like the blank rows.
Because if a table has a proper primary key, there should not be any duplicate rows in the table to begin with.
Primary key has no duplicates and no blanks.
I even tried a new file with 1 to 1000 as keys and no other data.
Could this be due to regional variance?
I know that sometimes there are small changes in the Excel formats from one region to another.
Maybe this is causing the script to interpret incorrectly?