Compare Names between tables

[TapZxK]

Hello All, 

I have 2 tables with Names of People. 

Table 1
Name 
John
Max
Marry
Hilton
Mark
Chris
Leo

Table 2
Name
John
Max
Lee
Daniel
Oleg
Swetha

both of the tables contain unique values none of the names are repeating.

I want to be able to see which names are in Table 1 but missing in Table 2 and then which names are in Table 2 but are missing in Table 1 

I would appreciate your help on the same. 

Best Regards,
Kris

[v-jingzhang · Accepted solution]

Hi @TapZxK

What is your expected output in the table visual? If you only want to know which names only exist in Table 1 and which names only exist in Table 2, we just need to compare name values in Name column of each table and don't need to use other columns. 

One idea is to add a column in each table to mark "Yes" if a name is only in this table and "No" if it's in the other table too. You can use this column to filter visuals later. 

Only in Table 1 = IF('Table 1'[Name] IN VALUES('Table 2'[Name]),"No","Yes")


Only in Table 2 = IF('Table 2'[Name] IN VALUES('Table 1'[Name]),"No","Yes")

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[TapZxK]

Hi @v-jingzhang,

I think I should elaborate. 

I have attached some example Data Below.

File 1

File 2

I want to achieve 2 Table Visuals on a Page.

1st will show the names that appear in File 1 but are missing in File 2

2nd will show the names that appear in File 2 but are missing in File 1

When achieved correctly 

1st Table Visual should give the following names

Lana

Mark

Marcus

Dominik

John

2nd Table Visual should give the following names

Phil

Susan

Lee

Dana

David

Anna

I hope this helps.

Thank You for your help

BR,

Kris

[v-jingzhang]

Hi @TapZxK 

Sorry the two images you attached are the same...

I think my method still works. You just need to add two table visuals on a report page. In the first table visual, put Name from Table 1 into it as Values. Drag Only in Table 1 column to Filters on this visual section in Filter pane and set show items when value is "Yes". Repeat the same operation for the second visual for Table 2. 

Tour the report Filters pane - Power BI | Microsoft Docs

BR,

Jing

[TapZxK]

Thank You, It worked indeed!

[amitchandak]

@TapZxK , a new table  like

except(Table1[Name], Table2[Name])

[TapZxK]

Hi Amit, 
Thanks for your Prompt reply. 
what if both of the table do not contain the same amount of columns?
BTW I tried your solution as a measure on a table visual. 
I should have mentioned that Table 1 and Table 2 contain more than just "Name" column and the amount of columns in each of those tables differ from one another. 

BR,

Kris