Power Query lookup question

[cr731]

I have a query where there are 6 columns of values, representing a hierarchical path (such as Cost > Direct Cost > Labor > Meetings ... etc.). The values in the columns, however, are codes, not the descriptions as I've mentioned above.

A separate table contains the key to codes, something like

A1: Cost
A2: Direct Cost
A3: Labor

etc.

I'd like to get all of those descriptors into my query table, but I can only figure out how to do this by merging the two tables 6 times to get the description individually of each column.

Is there a better way to lookup multiple values at once?

I don't want to unpivot those columns, because I want to use the columns to create a hierarchy in a pivot table.

 

[ImkeF]

Unpivot is the way to start: Then do your lookup and pivot back

 

[cr731]

Okay... so I successfully unpivoted my column and then added the labels using Merge Queries... now I am having trouble re-pivoting it and getting it to look right.

So basically I'm trying to transform this,

[table="width: 500, class: grid"]
[tr]
[td]Level[/td]
[td]Code[/td]
[td]Description[/td]
[/tr]
[tr]
[td]1[/td]
[td]C[/td]
[td]Cost[/td]
[/tr]
[tr]
[td]2[/td]
[td]DC[/td]
[td]Direct Cost[/td]
[/tr]
[tr]
[td]3[/td]
[td]L[/td]
[td]Labor[/td]
[/tr]
[/table]

And I want this to become

[table="width: 500, class: grid"]
[tr]
[td]Level 1 Code[/td]
[td]Level 1 Description[/td]
[td]Level 2 Code[/td]
[td]Level 2 Description[/td]
[td]Level 3 Code[/td]
[td]Level 3 Description[/td]
[/tr]
[tr]
[td]C[/td]
[td]Cost[/td]
[td]DC[/td]
[td]Direct Cost[/td]
[td]L[/td]
[td]Labor[/td]
[/tr]
[/table]

What would be the way to do that?

 

[ImkeF]

Oh sorry, should have warned you - there is a trick when doing the Pivot-back: You need to remove the "List.Count" at the end:

Table.Pivot(MergeCols, List.Distinct(MergeCols[Merged]), "Merged", "Value", List.Count)

So your code would look like this then:

let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
UnpivotOther = Table.UnpivotOtherColumns(Source, {"Level"}, "Attribute", "Value"),
MergeCols = Table.CombineColumns(Table.TransformColumnTypes(UnpivotOther, {{"Level", type text}}, "de-DE"),{"Level", "Attribute"},Combiner.CombineTextByDelimiter(" Level ", QuoteStyle.None),"Merged"),
PivotBack = Table.Pivot(MergeCols, List.Distinct(MergeCols[Merged]), "Merged", "Value")
in
PivotBack

LinkToFile

 

[cr731]

Thanks... I will give this a try.

Out of curiosity, is this something that can be accomplished by using the Power Query interface, or can it only be done through the Advanced Editor?

 

[ImkeF]

Yes, this is pure Interface:

1. unpivot others (Dropdown at Unpivot Columns)
2. Merge columns with Custom Seperator " Level "
3. Pivot: In advanced Options choose "don't Aggregate"