web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : PNHdMbFvgOwcL2P+kPEtCk
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Delete rows that no lo...
Power Apps - Microsoft Dataverse
Answered

Delete rows that no longer exist in the query output

Like (0) ShareShare
ReportReport
Posted on 9 Feb 2023 07:31:05 by DanielJMINT 25

Hello community, 

I have a problem with "delete rows that no longer exist in the query output" as it does not work as I thought it was supposed to. Every day I get an e-mail with an Excel table that contains data. This Excel table then gets picked up by a Power Automate flow, gets moved to a SharePoint and initiates a Dataflow refresh. A primary key is defined. Now some changes have been made to the SQL statement, generating the Excel table, which means a lot of rows where left out and were supposed to be deleted from the Dataverse table, because "delete rows that no longer exist in the query output" is checked. This did not happen. After the new SQL statement, there are now 33000 rows in the Excel table. All 33000 rows were updated, but none were deleted. The rows that were supposed to be deleted are not longer in the Excel table, but are still in the Dataverse table.

 

Did I misunderstand the feature "delete rows that no longer exist in the query output"?

 

Best regards

Categories:
Microsoft Dataverse
Dataflows
Tables
I have the same question (0)
  • Verified answer
    DanielJMINT Profile Picture
    DanielJMINT 25 on 23 Feb 2023 at 08:19:23
    Re: Delete rows that no longer exist in the query output

    I solved my problem by another way, since I could net get the out of the box solution to work:

    1. Build a flow, which triggers after the Dataflow refresh is complete

    2. Compare the Source Name of the file, the Dataflow runs on, with the Source Name column in the Dataverse table

    3. Delete all rows that do not match

  • DanielJMINT Profile Picture
    DanielJMINT 25 on 21 Feb 2023 at 08:34:54
    Re: Delete rows that no longer exist in the query output

    @AhmedSalih I loaded the data to a new table with an alternate key, but this still does not work as expected. No rows were deleted although they should have been. The rows that were supposed to be deleted, were just ignored by the Dataflow.

  • DanielJMINT Profile Picture
    DanielJMINT 25 on 20 Feb 2023 at 14:17:03
    Re: Delete rows that no longer exist in the query output

    Seems like I can only select an Alternative Key, when loading the data to a new table instead of the existing one.

  • DanielJMINT Profile Picture
    DanielJMINT 25 on 10 Feb 2023 at 06:59:33
    Re: Delete rows that no longer exist in the query output

    @AhmedSalih Where do I create the Alternate Key? When I try do it in the Dataverse table "Key" view, it reports that there is already a key which uses the same column. This is everything I get when I reset the Dataflow settings.dataflow.PNG

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Moderator on 09 Feb 2023 at 16:11:41
    Re: Delete rows that no longer exist in the query output

    @DanielJMINT, You can use your Primary Key as an Alternate Key, and this for the first problem. As for the second problem, You have to reset load settings and remap the columns; and select the Alternate Key to be the Primary Key:

    AhmedSalih_0-1675959038099.png

     

     

    If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.


    I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.


    Visit my Blog: www.powerplatformplace.com


     

     

     

  • DanielJMINT Profile Picture
    DanielJMINT 25 on 09 Feb 2023 at 11:03:49
    Re: Delete rows that no longer exist in the query output

    @AhmedSalih I tried to set an alternate key, but found two problems. There is no second column in my table that could be used in combination with my primary key. Except for the primary key, every column data can and will likely by be changed over the time. The feature to build an alternate key in the Dataflow view as you showed on your screenshot is not available for me anymore. I can only select my primary key, the name of the Excel table, or nothing at all. What I do not understand as well is why an alternate key is necessary anyway, because my primary key is unique? Thank you for your help so far!

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Moderator on 09 Feb 2023 at 10:23:47
    Re: Delete rows that no longer exist in the query output

    @DanielJMINT, Yes, Alternate Key is needed for your case. Set the Primary Key in the Dataverse table as an ALternate Key. In the Dataflow, make sure that you "reset" the setting in the mapping page so you can select the Alternate Key:

    AhmedSalih_1-1675938215142.png

     

     

     

    If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.


    I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.


    Visit my Blog: www.powerplatformplace.com


     

     

     

     

  • DanielJMINT Profile Picture
    DanielJMINT 25 on 09 Feb 2023 at 10:15:49
    Re: Delete rows that no longer exist in the query output

    Hello, @AhmedSalih my table only has a primary key, which is referenced in the dataflow. Do I need to set an alternate key? The table only has the default relationships, which every table has out of the box, I did not create any additional ones.

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 Moderator on 09 Feb 2023 at 10:04:47
    Re: Delete rows that no longer exist in the query output

    Hello, @DanielJMINT, You are using Dataverse Alternate Key, aren't you? Also, is you Dataverse table has a relashionship with other tabl of a type parental table relationship-restric delete? 

     

     

    If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.


    I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.


    Visit my Blog: www.powerplatformplace.com


     

     

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

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

MS.Ragavendar – Community Spotlight

We are honored to recognize Ragavendar Swaminatha Subramanian as our September…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 892 Most Valuable Professional

#2
Power Apps 1919 Profile Picture

Power Apps 1919 356

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 310 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans