cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhanney2323
Resolver I
Resolver I

Delete rows that now longer exist in query output question

Precursor: I am not a data analyst, I can read SQL, but I don't mess around in our SQL server, I just take data and transform it into other things.

 

Hey, so I am brand new to using data verse and I wanted to utilize it to speed up the pull time on a lot requests for one of our canvas apps by pulling in the SQL tables into Data verse instead of using the SQL server connector. What my issue is, we mainly use views as our primary pull option from SQL, not direct tables and a lot of these views don't have a primary key (don't ask me why, I don't know, but our data analyst says it has to be that way) and correct me if I'm wrong but when checking the box "Delete rows that now longer exist in query output" it will keep the two tables in sync with one another on refresh, but when unchecked, it refreshes the data but keeps the old and the new rows based on what has changed since last refresh? Ideally I just want to keep these in sync with each other, but every time I check the box, it insists I have an alternative primary key, which I don't really have one since these tables don't have one. If I keep it unchecked and publish the table and use it, am I at risk of having bad/old data or possibly pulling duplicates from dropdowns and such? 

 

bhanney2323_1-1649263975693.png

if checked but no alternative primary key is defined on initial dataflow creation

bhanney2323_0-1649265485190.png

 

 

 

UPDATE as of 10 minutes since I posted this - Well so after I published one of my dataflows with that box unchecked (Every time when I initially created that table and had it checked I got publishing errors) and then just edited it and checked the box and republished, it publishes fine and refreshes fine. (I did not specify any alternative primary key id) What the heck? Can someone explain? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AhmedSalih
Continued Contributor
Continued Contributor

@bhanney2323 , you have to have alternative primary key in you Dataverse Table which is a a Key that can't be duplicated. This same key must be in the source view as well for the delete rows feature to work. Your SQL view should have a primary key and if not create an auto-number column and use that when mapping your column in the Dataflow to load data into the Dataverse table. If you can't create this column in the SQL view, create an Index Column in the Power Query of your Dataflow. 

View solution in original post

1 REPLY 1
AhmedSalih
Continued Contributor
Continued Contributor

@bhanney2323 , you have to have alternative primary key in you Dataverse Table which is a a Key that can't be duplicated. This same key must be in the source view as well for the delete rows feature to work. Your SQL view should have a primary key and if not create an auto-number column and use that when mapping your column in the Dataflow to load data into the Dataverse table. If you can't create this column in the SQL view, create an Index Column in the Power Query of your Dataflow. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (1,678)