Creating Relationship in PowerBI when Employees are in columns by role

Question

I'm trying to create a relationship in PowerBI between an Account Assignments table, which lists each Account in a role, and then the assigned employee in the columns. One column is used for each role.

So for example:

Account Sales Rep Account Manager Account Coordinator Account A Tony Dianne Bob Account B Fred Steve Francis Account C Jack John Geraldine

I'm trying to create the relationship with an employee table which maintains data about each employee.

I tried creating multiple relationships, one for each column. Tying the Employee name from the employee table to the employee name for each role column in the account assignments table. I tried looking up some techniques to build relationships across multiple columns but didn't find any that applied to this situation.

Answer 1

Use PowerQuery to unpivot it, like this (don't be fooled by the M code generated... it's a pointy-clicky task. 1. Promote headers (so you have named columns), and then 2. Unpivot other columns (all but the Account column).

Once you unpivot your data, you end up with data that looks like this: Account, Position, Staff Member Account A, Sales Rep, Tony Account A, Sales Manager, Dianne

etc

Then you can either use that to join, or add an index column and join on that.