Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

How to delete a record from an Excel Table using a Flow

(0) ShareShare
ReportReport
Posted on by 2
I have a Canvas application that is used to input Assets into Dataverse table, this then enters this information into an Excel Spreadsheet.
I want a Automated Cloud Flow that will trigger deletion of a row from the Excel Spreadsheet with the pressing of a button within the Canvas Application. However I cannot input a Key Value as the Key Value would be the Serial Number, this obviously is different each time. When I try to create a Automated Cloud Flow it wont let me leave the key value blank.
 
 
Is this possible to not know the Key Value or input the Serial number I have selected within the Canvas Application and still get it to delete the row in the Excel Table? 
 
Thanks for any help!
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,365 on at
    How to delete a record from an Excel Table using a Flow
     
    For your step-by-step, I'm using this Excel table, which doesn't contain any column with unique values (I'm assuming that your scenario has the same restriction):
     
    The table is already tied to a Power App, as you can see from the __PowerAppsId__ column. The Power App is simply a gallery+form, where the selected record can be deleted by clicking in a button. In the "backend", the "Delete record" button triggers a flow, requiring only the properties title and due_date of the selected record:
     
     
    Once the flow is triggered, we return all records from the table with a List rows present in a table action, and then filters its body/value property by title and due_at within a Filter array action, where they match to the data received from the Power App. Since we are using two filter conditions, we need to encapsulate them within an expression, which in my case is and(equals(item()['title'], triggerBody()?['text']), equals(item()['due_at'], triggerBody()?['text_1'])). From your end, you would need to replace the text in red by you actual column name, and the text in blue by the dynamic contents of your trigger properties. For the other two inputs of Filter array, I'm setting the dropdown as is equal to and simply typing true to the right-side input:
     
     
    At this point, the Filter array action is returning only the records that match the provided criteria, which must ideally be only one, unless you want to delete more than one table row. Now we can add the Delete a row action. You can set the Key column as __PewrApsId__ (highlighted in red), and setting its value to an expression that references the __PowerAppsId__ property from the single record got after the Filter array step. You can use the expression body('Filter_array')[0]['__PowerAppsId__'] for it:    
     
    Now you are good to go. Refresh your flow in the Canvas App and let's test it from there:
     
     
    After the click, the flow runs successfully:
    And the record (hydro, due at Nov 30 2024) is no longer in the table:
     
     
     
    Knowledge base
    For further reading, I'm sharing a few contents that I produced about the topics discussed here, which can be helpful for you or for any other users that find this thread in the future.
    - Video about dynamic contents: https://youtu.be/jpledJfaEPQ
    - Inserting and updating data to Excel tables with Power Automate: http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
     
  • William_0505 Profile Picture
    William_0505 2 on at
    How to delete a record from an Excel Table using a Flow
     
    I am not following the Similar example, can I ask for that step-by-step more suitable to my use case? 
     
    Thanks, 
    William 

     
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,365 on at
    How to delete a record from an Excel Table using a Flow
     
    For the Delete a row action, you have to pass any identifier (a key) for the record that you want to delete. This identifier must be unique (located in a column that doesn't contain duplicates), otherwise Power Automate may delete the incorrect record by accident. I understand that you don't have access by default to the autogenerated id column in Excel when you connect this table to Power Apps, but we can find a few workarounds to it.
     
    First, have in mind that you can choose any other columns available in your table as a key for identify the record to be deleted (you choose it in the Key Column dropdown). If you don't have any column that contains only unique values, you can then use a List rows present in a table action, filter the records based on one or more columns, and then access the __PowerAppsId__ value to use it as key for your Delete a row action. 
     
    I have a step by step of a similar use case in this blog, under "Updating more than one record in an Excel table" section: http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/
     
    In the example above, we're filtering the table records for the data from a single column, but you can use some filter queries for narrowing the data based on the values from more than one column. For further reference on filter queries in Power Automate, refer to this article: http://digitalmill.net/2024/06/21/working-with-filter-query-in-power-automate/
     
    If the instructions above are not clear enough, let me know and I will be glad to share a step-by-step more suitable to your use case.  
     
    Let me know if it works for you or if you need any additional help!

    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard