cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper I
Helper I

Can we match one column from one table to other table?

Hi

 

I have below two tables

 

 Table 1 
NameEmailSchool
Katekate@abc.comabc
Smithsmith@abc.comabc
Johnjohn@abc.comabc

 

 Table 2 
NameEmailSchool
Kateskates@abc.comabc
Smith josmithjo@abc.comabc
Johnjohn@abc.comabc

 

I want to check if the name column of Table 2 is present in Table 1. We can see different names are present in table 1.

Kates, Smith jo is not available in Table 1, hence I need to print these records in the different table.


I need output as below

 

 

 Output 
NameEmailSchool
Kateskates@abc.comabc
Smith josmithjo@abc.comabc

 

Both tables don't have any relationship. If I try to connect both the tables, it's coming many to many relationships. Can anyone please advise. I tried vlookup but no success.

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @bourne2000 

Please check the below picture and the formula for creating a new table.

 

Picture1.png

 

Output Table =
SUMMARIZECOLUMNS (
Table2[Name],
Table2[Email],
Table2[School],
FILTER ( Table2, NOT ( Table2[Name] IN VALUES ( Table1[Name] ) ) )
)

 

https://www.dropbox.com/s/xqcq4seqgypia8i/bournev2.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User III
Super User III

Hi,

I have solved it using the Query Editor.  You may download my PBI file from here.

Hope this helps.

Untitled.png


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

Hi, @bourne2000 

Please check the below picture and the formula for creating a new table.

 

Picture1.png

 

Output Table =
SUMMARIZECOLUMNS (
Table2[Name],
Table2[Email],
Table2[School],
FILTER ( Table2, NOT ( Table2[Name] IN VALUES ( Table1[Name] ) ) )
)

 

https://www.dropbox.com/s/xqcq4seqgypia8i/bournev2.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors