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

Look up column value in another column and return value of 3rd column

Hi all,

 

next daft moment I need help with 🙂

 

I have a table that contains employee information that I use to gather data for mass mailings. Mostly I need this table to provide the first names. The relationship to other tables is the eMail.

eMailName
jane.doe@company.comJane
joe.doe@company.comJoe
jenny.doe@company.comJenny
Jack.doe@company.comJack
Maggy.doe@company.comMaggy
Marcus.doe@company.comMarcus

 

This table also contains the persons Manager as eMail adress:

eMailNameManager
jane.doe@company.comJaneMaggy.doe@company.com
joe.doe@company.comJoeMaggy.doe@company.com
jenny.doe@company.comJennyMarcus.doe@company.com
Jack.doe@company.comJackMarcus.doe@company.com
Maggy.doe@company.comMaggyCeo.doe@company.com
Marcus.doe@company.comMarcusCeo.doe@company.com

 

Now sometimes I may need to adress mass mailings to the Manager of the person and require their first name.

Now that's where I'm stuck. Any way to create a new column "Manager Name" that takes "Manager", looks it up in "eMail" and returns the corresponding value in "Name"?

eMailNameManagerManager Name
jane.doe@company.comJaneMaggy.doe@company.comMaggy
joe.doe@company.comJoeMaggy.doe@company.comMaggy
jenny.doe@company.comJennyMarcus.doe@company.comMarcus
Jack.doe@company.comJackMarcus.doe@company.comMarcus
Maggy.doe@company.comMaggyCeo.doe@company.comCEO
Marcus.doe@company.comMarcusCeo.doe@company.comCEO

 

I guess it'll have to be a new table. I tried creating a DISTINCT table with "Manager" and get their names. Works fine until I bring in the "eMail" column again. Then it displays the employees names under that manager instead of their own:

ManagerName
Maggy.doe@company.comJane
Maggy.doe@company.comJoe
Marcus.doe@company.comJenny
Marcus.doe@company.comJack
Ceo.doe@company.comMaggy
Ceo.doe@company.comMarcus

 

Can someone point me in the right direction?

Much appreciated 🙂

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@PuddleRunna  If you want them as two tables you can duplicate and relate them on Manager[Email] to Employee[Manager] as per the attached file.

 

You could also do a Merge in Power Query to make this 1 flat table. 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

2 REPLIES 2
AllisonKennedy
Super User
Super User

@PuddleRunna  If you want them as two tables you can duplicate and relate them on Manager[Email] to Employee[Manager] as per the attached file.

 

You could also do a Merge in Power Query to make this 1 flat table. 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Perfect, thanks for your swift support, @AllisonKennedy 👍

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.