Power Query Unpivot/Ingest question

[Greg_Deckler]

Perhaps @ImkeF or someone can help me out with this one. I have a work-a-round solution but would really prefer to solve this in Power Query if possible.

I have a table in Excel that is basically:

Metric, Actual, Budget, Prior Year

Metric1,100,200,150

Metric2,50,100,75

...

What I want to end up with is:

Metric1 Actual, Metric1 Budget, Metric1 Prior Year, Metric2 Actual, Metric2 Budget, Metric2 Prior Year

100,200,150,50,100,75

Any clean way of doing that in Power Query through some fancy unpivots, etc?

[MarcelBeug · Accepted solution]

Something like this:

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Metric"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Metric", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Created via standard menu options; the last step with aggregation "don't aggregate".

[Greg_Deckler]

Yep, thanks @MarcelBeug, that got me there. It always seems so easy and obvious when you and @ImkeF show me this stuff!!