Removing all values and adding new ones to a table?

[00000]

I am launching an Excel document that contains a preexisting table. I would like to remove all the table's values, replacing them with new values created by the flow. This is to update a weekly report with dozens of columns and hundreds of rows. I'm not sure what actions to take and any help would be much appreciated.

[Nived_Nambiar]

Hi @00000 

Yes that's possible

Are you trying to do it via power automate cloud flow or power automate desktop flow ?

Also could you tell how you are going to update table /excel values ?

Kindly let me know 🙂

[Agnius]

Assuming you're doing this with Power Automate Desktop (because this forum is for Desktop flows), there are several ways of doing it.

If you don't have any formulas pointing to this table, the easiest way to do it would be to use Delete Excel worksheet to delete the entire sheet, then use Add new worksheet to add a clean new worksheet with the same name and then use Write to Excel worksheet to write all your values.

An alternative could be using Get first free column/row from Excel worksheet first, then do a Loop from 2 to %FirstFreeRow-1% and using Delete row from Excel worksheet using the %LoopIndex% as the row number. This would delete all rows that have data, but leave the table headers, because you're starting at row 2. You could then use Write to Excel worksheet to populate the sheet with new data.

Finally, you could simply Write to Excel worksheet right away to overwrite the existing values, and then would need to simply delete any rows left by the previous dataset, if it was longer than the current one. To do that you would need to get the number of rows from the old dataset by using the Get first free column/row from Excel worksheet. And then you would also need the rows from the new dataset. Depending on what you are writing, there are several ways of doing it.:

• For tables, you can use the .RowsCount parameter - if you have a data table stored in %DataTable%, using %DataTable.RowsCount% will tell you how many rows there are in your table.
• For lists, you can use the .Count parameter - if you have a list stored in %List%, using %List.Count% will tell you how many items you have in the list.

You would then need to use an If condition to check if %FirstFreeRow% is greater than the count of values you wrote and if so, then do a Loop from the count of values +1 (to account for the headers) to %FirstFreeRow% and using Delete row from Excel worksheet with the %LoopIndex% as the row number.

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

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.