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

[Honne2021]

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!

[BA_Pete · Accepted 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