Forum Discussion
Compare Names between tables
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
Hi Anonymous
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.
7 Replies
Hi Anonymous
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.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
Hi Anonymous
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
how to make it in Measure
Thanks
Anonymous , a new table like
except(Table1[Name], Table2[Name])
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
