Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PuddleRunna
Advocate I
Advocate 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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

Perfect, thanks for your swift support, @AllisonKennedy 👍

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.