Delete all rows in a table

[Anonymous]

I'm new to PA/Flow and very confused by this-deleting rows (no conditions, just empty the entire table) seems like a pretty obvious objective. I am trying to delete all rows in a table when a flow is triggered.

(Yes, my table is definitely an excel table, not a range! 🙂 )

The flow is marked on Power Automate as running successfully (which I believe confirms the flow is running and that there are no apparent errors, so that's good at least) but the table rows aren't deleted. 

The flow is triggered when a Sharepoint list is modified. 

The destination is an excel spreadsheet, which must be emptied/have all rows removed when this list is modified. 

(I am assuming that in order to delete a row, a condition must be met (ie, there is no way to simply delete rows without performing a logical test).

When an item is created or modified (in the specified Sharepoint site address and list name):

List rows present in a table: 

Location: Sharepoint name containing Excel file

Library: Sharepoint folder containing Excel file

File: Excel file

Table: Excel table

Apply to each VALUE:

Excel column 2 ('ID_Number') is equal to Excel column 2 ('ID_Number').  (Note that I am simply looking for a test that will always evaluate to true, and I see no reason why this wouldn't do so). 

If YES:

Delete a Row:

Location: Sharepoint name containing Excel file

Library: Sharepoint folder containing Excel file

File: Excel file

Table: Excel table 

Key column: ID_Number  (I honestly wasn't sure what to put here. I presume that the key column is important when performing logical tests, since it is checked for its value??)

Key value: ID_Number (Again, I am not sure of the relevance of the key value here. Presume this is a value that is tested for, eg greater than/less than?)

Note: the above test did not work, so just to confirm that the logical test was not evaluating as false, I duplicated the 'YES' code into 'NO':

If NO:

Delete a Row:

Location: Sharepoint name containing Excel file

Library: Sharepoint folder containing Excel file

File: Excel file

Table: Excel table

Key column: ID_Number  

Key value: ID_Number 

No joy. The flow appears to run without errors, but the table retains all of its values (all rows should be deleted). 

Could someone please assist? 

Much appreciated-thanks. 

[manuelstgomes]

Hi @Anonymous 

It looks ok, at least what you wrote.

Are you defining the name of the table, the key column and the key correctly? Probably it runs correctly but, since it can't match the key with the one in Excel, it doesn't delete anything.

Also be careful with types. You may have a String or a Float that, when compared, may be considered different than your value's type in SharePoint.

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

[Anonymous]

Thanks for the response. I think the 'key column' and 'key value' fields are the issue. But if the intention is to remove existing rows regardless of their currently contained value, then how do I go about removing the rows using the 'delete a row' method? Or is there an alternative approach? 

It does not matter whether the row / field is blank, contains 'aaa' or '123' or any other value-I just want to delete the row 🙂

[Anonymous]

@Anonymous if you are still having issues, could you provide screenshots of your flow?

You shouldn't have to run through a condition in order to delete the rows. Your question is asked and answered here  but basically you can use the List Rows Present in a Table as you had, then nest "Delete a row" in an "Apply to each" loop.

Make sure you're using Dynamic Content that matches the value you choose for your key column so that as the "Apply to Each" loops through the rows in your spreadsheet, you're matching the value in the key column to itself (thus always evaluating as "true") and prompting the Flow to delete the row.

It doesn't matter what field you choose as your "key column", but you need to make sure that the "Dynamic Content" that you select for your "key value" is the same field.

See example screenshot below:

I hope this helps, let me know if you have any questions.

-Kyle

[greg2k]

I'm using this approach for a scheduled Excel update, but I find one of the slowest steps is deleting the 1000+ rows in a table so I can replace them with new data. Is there a quicker way to empty the table than listing all current rows and then recursively deleting them?

Thanks!

[dgwilson]

Did you find a way to do this quickly and efficiently.

I too have about 1000 rows to delete (whole table contents actually)... so I can populate with new contents.

I'm doing this from Power BI --> Power Automate

- David

[J0el]

I would have thought a standard action to clear table should be created.

I don't suppose there's any update to this?

[TonyDew]

On your "Apply to each" where you delete the rows, click the "..." and select setting. Turn on Concurrency Control and crank up the Degree of Parallelism. You'll get a tenfold or greater speed boost.

Having said that, I don't know what any downsides would be to using this method. I use it myself and haven't had any issues that I know about.

[J0el]

The downsides are:

Maximum of 5000 rows

High utilisation of flows, therefore risk of daily limits being reached

Time taken to delete 5,000 rows takes hours, whereas a delete table contents option would mitigate all of the above

[TonyDew]

Thank you for the clarification! I was sure there must be some downside.

[Anonymous]

Hi Joel,

Hope you suggested using Delete table in the Transform query using Power Automate since Execute SQL query is not allowed..But if we use it in Transform Query, rows are getting deleted even if we recreate again.. Did u face that problem.It would be grateful if you can suggest.

[shtryler]

I was also very annoyed by the slow performance of the "apply-to-each"+"delete"-loop and decided to use a workaround via the "Run script"-action. With the following script I managed to reduce the runtime from 30 minutes to 30 seconds. Maybe it's helpful to some of you as well:

function main(workbook: ExcelScript.Workbook) {
   let table = workbook.getTable("YOUR_TABLENAME_HERE");
   let tableRange = table.getRangeBetweenHeaderAndTotal();
   // errorhandling if the table is already empty
   if (tableRange.getRowCount() == 1) {
      console.log("nothing to delete");
   }
   else {
     // delete all the cells in the table data range, the table range is then reset automatically
      tableRange.delete(ExcelScript.DeleteShiftDirection.up);
   }
}

FYI, I also tried the table method for row deletion:

//delete all rows from index 0
table.deleteRowsAt(0, tableRange.getRowCount())

This was very slow as well and resulted in a script timeout.

Hope this helps some of you, best wishes

Hans

[TommyBlomberg]

Thanks, this solution was helpful and useful.

[Anonymous]

this is a nice idea but i get an error when i try. my sheet is called Ark3 and there is only one table on that sheet. there is other tables in sheet Ark1 and Ark2. can you explain why it says Cannot read properties of undefined reading getRange

[shtryler]

Hey, sorry about that. There was an error in my example code (I already corrected it in the post), you can delete lines 3 and 4 and replace them by

let table = workbook.getTable("YOUR_TABLENAME_HERE");

 The name of the sheet on which the table is located doesn't matter in this case.

Best wishes

Hans

[Anonymous]

HI 🙂

the code wors perfectly 🙂 however when i run it from a power automate flow i do not work. if i want it to work in a power automate flow do i need the have a code that point to a specific excel sheet and  a specific table name?

[FabvE]

Wow, that was really helpful.

I only have the problem that my table has no total row and cannot have it as it will be exported afterwards. Is there any solution without the total row or can I add/remove the row via script?