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

[varung8899]

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):

ID	REQUEST DATE	STATUS	TEAM
123	03-JAN-24	Development	ABC
456	08-APR-23	Development	ABC
890	01-JAN-24	Deployed	ABC

TABLE2 (TODAY):

ID	REQUEST DATE	STATUS	TEAM
123	03-JAN-24	Deployed	DEF
456	08-APR-23	Deployed	XYZ
789	20-MAY-23	Development	ABC
890	01-JAN-24	Deployed	ABC

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

ID	REQUEST DATE	STATUS	TEAM
123	03-JAN-24	Development	ABC
123	03-JAN-24	Deployed	DEF
456	08-APR-23	Development	ABC
456	08-APR-23	Deployed	XYZ
789	20-MAY-23	Development	ABC

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

DESIRED OUPUT:

ID	REQUEST DATE	STATUS	TEAM	PREVIOUS TEAM
123	03-JAN-24	Deployed	DEF	ABC
456	08-APR-23	Deployed	XYZ	ABC
789	20-MAY-23	Development	ABC

[varung8899]

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 ?

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[varung8899]

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

[Ashish_Mathur]

You are welcome.

[varung8899]

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]

Hi,

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