cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

I have a button in a Power App that runs a Power Automate file.  My Power Apps screen has a gallery that is populated from a SharePoint list. When the button in Power Apps is clicked, it runs the Power Automate file that inserts new rows into my Excel worksheet into a specified table. It works and is very simple (steps are shown below). 

 

  1. SP - Get Items
  2. Excel (Apply to Each) - Add a Row to a Table

The only issue is that I need it to delete all rows (except table headers) every time it runs BEFORE it adds new rows. This is basically exporting a SP list to an Excel table and it needs to write all new data after removing all existing rows. 

Pwr-Automate---Button-exports-SP-list-to-Excel-table.pngHere is my existing Power Automate. How do I remove all rows (except table headers) before "Add a new row in a table" runs?
  
When I try to add Delete a Row it requires a specific value for one of the fields but I need to delete all rows. Is there a solution?Pwr-Automate---Button-exports-SP-list-to-Excel-table-DELETE-ROWS.png
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

Hi @bolin-stratus 

 

Your Key Value should be like: items('Apply_to_each_2')['File']

 

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

 

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

Hi @bolin-stratus  , Delete row need a key-value pair to find and delete the row. here is what you need to :

Select one unique column and pass its value to function . 

PA5.png

 

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

Highlighted
Post Patron
Post Patron

Re: Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

@DeepakS  Thank you for your response. I didn't know about the formula items('Apply_to_each')['File'] so that is great to know. 

Although there were no errors detected when I clicked Save, an error occurred before it actually saved the file: "The template validation failed: 'The inputs of template action 'Delete_a_row' at line '1 and column '4605' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Delete_a_row' to be referenced by 'repeatItems' or 'items' functions.'."  

As you can see from this screenshot, I used the same steps as your screenshot.  The steps below the screenshot are unchanged (SP Get Items from List followed by Apply to Each & Excel Insert New Rows).  Any idea on how to rectify this error? 
Pwr-Automate ERROR template verification failled.png

Highlighted
Super User II
Super User II

Re: Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

Hi @bolin-stratus 

 

Your Key Value should be like: items('Apply_to_each_2')['File']

 

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

 

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Deleting All Rows in an Excel Table (Except Headers) Before Adding New Rows?

Thank you @DeepakS - that was the solution!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (6,721)