cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leyburn19
Memorable Member
Memorable Member

Excel delete row problem or bug?

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions

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.

View solution in original post

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

2019-10-24 10_40_40-PowerApps.png

 

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 


2019-10-24 10_41_48-PowerApps.png

 

View solution in original post

22 REPLIES 22
Pstork1
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

 

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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.

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

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

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
Advocate I
Advocate I

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

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

can you please share the screenshot of your flow? 

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?  

 

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

2019-10-24 10_40_40-PowerApps.png

 

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 


2019-10-24 10_41_48-PowerApps.png

 

Anonymous
Not applicable

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. 

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

NCPowerAutomate
New Member

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

How do you get the StartCount?

Beat
Advocate V
Advocate V

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.

 

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:

santee_0-1656592222389.png

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
Memorable Member
Memorable Member

@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

@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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,189)