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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Honne2021
Helper II
Helper II

1 Look up column, Several Column Data - Index Match for PowerQuery or DAX

Hi, my dataset looks like this. I had been checking alot of questions but mostly it is the opposite of what I have. I can either do it via powerquery or dax. We have about 600 employees and more than 20 department heads. The right table (department heads) is the look up table and to the left is the data table. Frank is the staff and Mike is the supervisor. Both are under the team of Jason however, Jason is not found in the same column because Frank reports to Mike. Mike reports to Jason. 

 

Picture1.png

I am thinking if I am just missing out something here but I have tried so many formulas and techniques but none worked so far!!!

Any help or tip would be great. Thank you!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Honne2021 ,

 

In Power Query, select your left-hand table, select the [employee] column, then go to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

You now have a properly normalised data structure with Line Manager level as an [Attribute] column, and the Line Manager names as a [Value] column. From here, you can either merge the two tables together on leftTable[Value] = rightTable[mgrName], or you can relate the two tables in the data model in the same way.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

Hi @Honne2021 ,

 

In Power Query, select your left-hand table, select the [employee] column, then go to Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

You now have a properly normalised data structure with Line Manager level as an [Attribute] column, and the Line Manager names as a [Value] column. From here, you can either merge the two tables together on leftTable[Value] = rightTable[mgrName], or you can relate the two tables in the data model in the same way.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors