Excel delete row problem or bug?

[leyburn19]

I have a flow that was working fine for months,  but yesterday started to play up.  I do a list rows in table action then do a delete row action based on that result. The flow is then supposed to add some new rows,  but as it fails it stops. 

It was working fine until two days ago. Suddenly I get a fail based on my Key ID.  My Key IDs are unique and I get the error on an  ID no longer exits.  If I debug and scroll through the action  I find it is wanting to delete a row which was deleted some rows earlier.  Why would it suddenly bring it up again to delete?

I can no longer get past the problem as it happens nearly every time the flow is run even when IDs are a different set.  Is this a bug as it is pretty important the I work through this issue ASAP?

[Pstork1]

Can you post a copy of the Flow so we can see how it works.  Then we can try to figure out what is going on.

[leyburn19]

Not really as there are many steps and I am modifying to try and get something workinq

But steps are:

1. Schedule run  once a day

2. List rows in Table - get data in the template

3. Delay (built in to allow Excel to release file)

4. Delete Rows  based on Two - using KEY ID column which is a unique ID.  The fails regularly now.  When I get it down to a few row it will work.

5. Get my sites from Sharepoint

6. Apply to each - The loops the flow through each of my 29 sites

7. Redo steps 2 and 3 - (delete row action also sometimes fails here if there are too many records)

8. Another delay

9. Add Rows to table

10. Another Delay

11. Get File content of the excel file

12. Send email to site with excel file attached

So - have had no issue for many month even when some rowws are greater than 1000

Now in teh last two days it is failing all the time.  I am assuming some sort of change by developers or some sort of issue with one drive now

[Pstork1]

There have been some timing issues in FLOW lately due to increases in load.  It could be that the delay in #3 is causing issues.

[leyburn19]

Must be something like that.  I successfully got it to run,  but had to manage this issue with a work around of at least 6 Configure To run after on fail.  In most cases my loop did not need any is the row number was under 20 but in my larger sites where rows were 200 to 1220 it varied from 1 fail to using all 6.

[leyburn19]

This problem is now replicating acros my other Flows that do delete rows in Excel on one drive.  Seems something has changed.

[leyburn19]

This problem is a real pain and now appears to be getting worse.  It was ok for months.  I now have up to 10 list rows and delete actiosn as a work around

[leyburn19 · Accepted solution]

Ok,  for those that are interested I at least found a simpler work around to the delete row failure that made process one definitive loop.  Using two integer variables,  StartCount set to 0 and the other CountRows set to number of rows that are to be deleted

Then creating an action of Do Until based Variable Startcount to Equal CountRows.  Then put the Actions:

1. List Rows present in Table
2. Delete rows based on the list
3. Set Variable countrows to equal the new number of rows in the Table at the end of the Delete.  

When the countrows variable equals 0 then the table is empty

This effectively meant that when the Delete rows met with an inability to delete a row,  the loop started again.  I no longer need to have any process based on failure.

[manivineet]

we're encountering the same issue

after deleting a few rows from the excel sheet, it throws a "The argument is invalid or missing or has an incorrect format"  error

[manivineet]

how do you count the number of rows in the table? 

can you please share the screenshot of your flow? 

[leyburn19]

I used a simple list rows in table then have a intger variable to is set to Length() of the List rows.

Since I start do the Do Until action all my flows work,  but I hav enoted that without this I would be in real trouble.  The process I posted earlier did need modifications to:

Do Until Variable1 eq var2   - Variable1 is set to 0 and Var2 is set to list count of the sharepoint list I filter for this

Then the steps in are:

1. ListRows in table

2. Delete a row based on step one so creates a for each

3. ListRows in table again - Set it to run if 2 fails and run if 2 is successful

4. Set Var2 to be the length of the 3

Basically Var2 reduces each time till it eaquals 0 which is Var1

You probelm might also be different in do you use a unique value for the key Column?  

[manivineet · Accepted solution]

1. Get the Row Code of the Excel sheet at the start

2. Loop through the excel sheet until each row has been deleted

If there's a failure or timeout, try again until the Row Count is zero 

[Anonymous]

Thanks, this seems very useful in fixing an issue I'm having at the moment with deleting rows. 

Can I please confirm the expression for the value of the variable CountRows? 

Is it the following: 

length(body('List_rows_present_in_a_table')?['value'])

Currently, my flow seems to delete the table rows fine, but then won't stop running. I'm guessing that countRows isn't reducing to zero for whatever reason? 

Also, just out of interest, why set the condition for 'do until' to be equal to a variable, StartCount? Why not just set it to 'do until equal to 0', and do away with the variable? 

Thanks 🙂

Thanks. 

[Ander101]

@manivineet Thanks for the great solution. It worked perfectly in my flows. 

[NCPowerAutomate]

Can someone please go into more detail with the length variable?  I can't get this to work.

[santee]

How do you get the StartCount?

[Beat]

I had this same issue some time ago, and really struggled, but then found an easy workaround.

- leave the first row of the table blank

- add skip count = 1 in the List rows present in a table

Now the Delete all rows will leave that first blank row alone. This did the trick for me.

[santee]

Going with the simple route: List Rows/Apply to each/Delete a row ... with your tip (1 blank row +skip row1)... went better than before (deleted rows for 10 minutes before erroring out), but still no go:

I am wondering if my choice of Key Column&Key Value is the issue, although i did try 2 others. IDs are unique, but not consecutive (sometimes the entries skip few numbers, like from 17 to 24, based on some internal rule, which is not reflected in the list). What i don't understand is why is the ID even an issue, while my flow just states...delete rows.

This flow I'm working on, was filled with fun (not) ... seems like every step has to be an issue. Is it me, or poor integration from MS!? /s

[takolota]

@leyburn19 @santee @Beat @NCPowerAutomate @Ander101 

Would a batch delete action help with this?

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/td-p/1634375

Sounds like some of you have long-running flows that time out.

See this video for related set-up instructions: https://youtu.be/HiEU34Ix5gA

[Beat]

@santee I think I forgot to mention one point. I have one column in the Excel table that is the same for all (except the first where it is blank, too) and use that as the key column. How many rows are there in that table that you want to delete? I work with pretty small tables and so far never hit the 10 minute timeout