CODEWITHSUNDEEP
excelvbapowerqueryget-transform
Sergei Trunov
Sergei Trunov

Reputation: 165

Excel Power Query decouple and unpivot columns

I have a data structure that is something comparable to this

| type | A-metric1 | A-metric2 | B-metric1 | B-metric2 |
| aRow |     1     |     2     |     3     |     4     |

I'd like to turn A and B into a dimensions and turn it into rows so that it's something like this

| type | dimension | metric1 | metric2 |
| aRow |     A     |    1    |    2    |
| aRow |     B     |    3    |    4    |

I could probably do this manually by duplicating the type row for as many times as there are dimension labels, and then extract the metrics by order and do this in excel with a macro.

However I am likely to be handling large files, and probably can't manipulate data of this size effecitvely in VBA. I was wondering if something like this is possible to do in Excels Power Query / Get & Transform?

Sorry if duplicate - Im not sure how to formulate this question to get relevant hits on searches.

Upvotes: 0

Views: 141

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

You may apply following approach:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"type"}, "Attribute", "Value"),
    split = Table.SplitColumn(unpivot, "Attribute", Splitter.SplitTextByDelimiter("-", 1), {"dimension", "metric"}),
    pivot = Table.Pivot(split, List.Distinct(split[metric]), "metric", "Value")
in
    pivot

enter image description here

Upvotes: 1

Related Questions