Relate Multiple columns in one table to one column in another?

[Anonymous]

Hi, 

I have a table which tracks approvals.  It has been built using references for individuals, so Person A =10, Person B=11, etc.  I have another table which has the person information so Reference 10 = Person A.  

Unfortunately I have 3 approver columns, as each item needs to be approved by a Manager, Finance and Head of. 

The columns look something like:

Approver1  Approver2  Approver 3

  10                 15               23

  14                 22               23

The second table (Employees) will have 

Reference    Name

  10                Mark

  11                Matthew

I can build a simple relationship between the tables for Approver1, which gives me the names for that approver.  However, I need to work out a solution where I can do so for all 3 Approvers.  

The best option I have so far is to link the same table 3 times, so itll be Employees Table1, Employees Table2, Employees Table3.  Then link Approver1 to Table1, Approver2 to Table2 and Approver3 to Table3.  

Is there a more robust & simpler way of obtaining the data from the Employees table to provide the results for all 3 approvers in my Visualisations?

[jdbuchanan71]

You can apply an Unpivot Columns transformation on your data to get the people in a stack so you can join off a single colum.  I might also rename the columns to 'Manager', 'Finance', 'Head of'

Select the three columns and Unpivot:

Rename the two new columns:

[Anonymous]

@jdbuchanan71 Thanks for that.  

It seems I may have missed an important item. 😞 

The table with 3 approvers also has a lot of other columns, some of which I will need, for example, approval dates, the item being approved (and all it's details).  I will still need to reference all of this in visualisations.  

[jdbuchanan71]

That's fine.  When you unpivot just the three approver columns the rest of the line items will be replicated for each of the approvers.

Could you share a sample pbix? We might also be able to split the approvers into a separate table that has whatever the identifier for the thing they approved (like request id).

Your original idea of having three employee tables is also not a bad one.  You can replicate a table in Power BI simbly by clicking new table > give it a name and point it at an existing table.

Managers = EmployeeTable

[Anonymous]

Unfortunately samples aren't available, it's extremely sensitive. 

I'll have a play with the unpivot and will see how that goes.  

It is nice to try to improve my knowledge when I have a relatively suitable working solution as a fall back. 

[Anonymous]

That's that theory stuffed:

Expression.Error:  Acess to the resource is Forbidden. 

I can't unpivot the data.  

@jdbuchanan71   Many thanks for your input, it's much appreciated.