Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago
Solved

Power Query Unpivot/Ingest question

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?

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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".

     

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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".