Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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.
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!
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.