Excel Table: Delete a row: "No row was found with Id (xxx) issue"

[Anonymous]

This should be fairly simple. I just want to remove all existing rows from a table within an Excel spreadsheet.

If it's relevant, the spreadsheet sits in a Sharepoint site. No permission issues etc; I have full access to this site and table.

The flow is fairly simple: 

Manual trigger => List rows present in table => Delete a row. For testing purposes, there are currently only 2 rows in the table. 

The key column is 'ProjectCode'. For the 2 rows, the ProjectCode values are 'xyz' and 'abc' respectively. Changing these values does not solve the problem. 

Every time I try to run the flow, I get the error: "No row was found with Id 'xxx'" ('xxx' is whatever value that happens to be in the keyColumn for the first row).

(I've actually tried following this workaround for the issue, but I'm relatively new to Flow and couldn't get past the 'Set row count to the number of rows in the excel sheet' step, since I couldn't assign the length() function to the number of rows-perhaps this would be a workaround if someone could explain how to do this, since Flow doesn't seem to have any countRows() function??)

This is very frustrating. It seems like a very basic task...what obvious thing am I missing here? 

Thanks. 

[ChristianAbata · Accepted solution]

hi @Anonymous  please see this example I made it.

My excel:

Empty compares if I have values in list rows.

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

[efialttes]

Hi!

Can you share a screenshot from your Flow design? Please make sure the details of the 'Delete row' inputs are visible

Thanx!

[PieterVeenstra]

I did this last week and it worked. See the Cleaning up the table section in the following post:

https://sharepains.com/2020/03/05/sort-an-array-in-power-automate/

Could it be that your key column isn't the column you are expecting it to be?

[Anonymous]

***EDIT-just realised the table/flow is incorrect for this particular query. Will update shortly with a corrected table that's consistent with the original query.***

Thanks for the response. Here's a screengrab of the table concerned and the flow. 

I just tried it now, and it's working again...which is odd. Could the errors observed earlier have been caused by a bandwidth issue, or something else? 

I am currently looking at a 'continue to attempt to delete rows until countrows = 0' style solution, to avoid this problem-any help would be greatly appreciated 🙂 

[Anonymous]

Hi, here's the screenshots. 

Having tried the flow again just now, it appears to work about half of the time, given the same 2 rows to delete. 

Could this be a bandwidth issue? 

I am currently going for a 'keep attempting to delete table rows until countrows = 0' approach. I believe this will help address this issue? 

Any help would be greatly appreciated. 

Sorry for the earlier reply, which had the wrong screengrabs!

[Anonymous]

Hi, thanks for the reply. I'm looking into the 'cleanup the result' section of your link (as much as I can get my head around some of the steps involved, eg how much of the json info is applicable to Excel tables!)

I am confident I've got the right keycolumn. There is no other column called 'ProjectCode' so I think it's correct.  

[Anonymous]

Ok...your 'Empty' guidance below helped me solve the workaround I was originally trying to do!

Turns out the syntax is very similar, and is as follows:

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

Using this, and a 'do until (table is empty)' approach, I was able to get it to work. 

Whilst the solution you suggested did not work for me, the above was very useful-thank you.