Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
varung8899
Helper I
Helper I

Get the newly added, missing and updated records after comparing 2 data tables having same structure

Hi All,

Hope you are doing great. Appreciate if could you please help me in identifying a solution for my simple query. I am new to this tool and am sure you will have a solution for it. I have 2 tables with same structure which contains data for Yesterday and Today. I am trying to identify which rows has been added newly and updated in Todays file compared to yesterdays file. Columns are Date, ID, Status, Team but I am interested only about the ID whose value under Team column gets changed / updated or ID's that are added newly. I have used Left Anti, Right Anti Merge & Append queries to find out the rows that are unique in both the tables. But the problem is that if Yesterdays file has ID=123 & Team =ABC and if todays file has ID=123 & Team=DEF both these rows are populated in the final appended table. I would like to have only the record from Todays file (ID=123 & Team=DEF) which is the updated record and I would like to have the value (Team=ABC) under a new column called Previous Team. For the ID's that are newly added in todays file, previous team column would be blank. There are no relations established between Yesterday, Today, LeftAnti, RightAnti & Appended tables.  I am not sure if thats the way to find the records that are different in these tables. Could you please suggest a solution. Please see tables below.

TABLE1 (YESTERDAY):

IDREQUEST DATESTATUSTEAM
12303-JAN-24DevelopmentABC
45608-APR-23DevelopmentABC
89001-JAN-24DeployedABC

 

TABLE2 (TODAY):

IDREQUEST DATESTATUSTEAM
12303-JAN-24DeployedDEF
45608-APR-23DeployedXYZ
78920-MAY-23DevelopmentABC
89001-JAN-24DeployedABC

 

LEFT ANTI, RIGHT ANTI MERGE using ID , TEAM columns & after APPENDING TABLE OUTPUT :

IDREQUEST DATESTATUSTEAM
12303-JAN-24DevelopmentABC
12303-JAN-24DeployedDEF
45608-APR-23DevelopmentABC
45608-APR-23DeployedXYZ
78920-MAY-23DevelopmentABC

ID 890 is removed since its Team is same in both the tables as desired.

DESIRED OUPUT:

IDREQUEST DATESTATUSTEAMPREVIOUS TEAM
12303-JAN-24DeployedDEFABC
45608-APR-23DeployedXYZABC
78920-MAY-23DevelopmentABC 
1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1707441896978.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
varung8899
Helper I
Helper I

Thansk for your reply Ashish. I can update my query to generate new Report Date column to the tables which will be Yesterdays date In Table 1 and Todays date in Table 2 for all the records thereby populating it to the appended table. Could you please propose a solution based on that ?

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1707441896978.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry for late reply Ashish. Thanks for the solution. You are right and it works as expected.
I made it complicated. 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, I would need the records which are unique in yesterdays file but not in todays file as well. Thats the reason I have done Left Anti, Right Anti Merge & Append instead of Left outer. Let me remove the sensitive data and share the PBIX file with you in a while. 

Ashish_Mathur
Super User
Super User

Hi,

Why is there no Report date column in the 2 tables to know which is yesterday's and which is today's table?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.