Merge 2 columns from 2 tables and list unique rows

[JackDM313]

Hi, 

I have 2 tables, with 2 columns having the same information and the 3rd different information. 

Table1:

Table2:

I want to merge both ID and Team columns into a new table (Table3) and remove all duplicates so only unique values display

Outcome (Table3):

Both Table1 and Table2 have relationships to 2 other tables: 'Date' and 'Team' where the date is linked to a date column and team is linked to a team column. 

[amitchandak · Accepted solution]

@JackDM313 , Seem to me like left anti join in Power query Table1 to Table2 and then select ID and Team use remove duplicate

In DAX

Distinct(except(summarize(table1, [ID], [Name]) ,summarize(table2, [ID], [Name]) ))

refer if needed

Power BI: Power Query Functions one must know
: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=36407s
Power BI - Cheat Sheet: Power Query Functions one must know
https://amitchandak.medium.com/cheat-sheet-power-query-functions-one-must-know-a8531b6e321a