How to Merge Query for multiple column

Question

I have a table of information like this:

And a lookup table for user names to IDs:

How do I do a Merge on each column to lookup the values from the other table so I get this result:

I do not want to manually apply an action to each role column, because the list of roles may grow or shrink. So the solution needs to all columns (except the first) in the table.

Can this be done?

Answer 1

Basically this calls for unpivot on the Project data, merge to the other table, then re-pivot to get back in proper order

Steps:

Load in the ID data; here I am assuming it is loaded in query ID_Table

Load in Project data; here I am assuming it is loaded in range Projects

In the project query, right-click the first (project) column, unpivot other columns

Home ... Merge queries...

Merge the two tables using the Value column in the project query and the Person column in the ID_Table query, and use Left Outer merge

Expand results using double arrows atop column and uncheck all except ID

Right-click the value column and remove

Click attribute column ... transform .. pivot column...

Use ID as value column ... advanced options ... dont aggregate

sample code

let Source = Excel.CurrentWorkbook(){[Name="Projects"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Project"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Value"},ID_Table,{"Person"},"ID_Table",JoinKind.LeftOuter),
#"Expanded ID_Table" = Table.ExpandTableColumn(#"Merged Queries", "ID_Table", {"ID"}, {"ID"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ID_Table",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "ID")
in #"Pivoted Column"