cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Honne2021
Frequent Visitor

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!