Unpivot

[Moniek]

Hi,

I would like to convert a table (Unpivot?) . So that I have a column for amount and hours, like the example below. How can I do it in Power Query, thanks a lot!

[m_dekorte · Accepted solution]

Hi @Moniek 

Select your columns Index and Name, go to the Transform tab on the ribbon and select: Unpivot Columns, Unpivot Other Columns.

Click on the Attribute column header to select that column, select Split Column, Split by delimiter (space)

Now select the Attribute.2 column and choose Pivot Column set the Value column as Values column

And that should be it!

You can copy this script into a new blank query, to see all the steps I've described.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lECYwMgYQTCBkqxOkAJIwRfR8kYhC2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Jan Hour" = _t, #"Jan Amount" = _t, #"Feb Hour" = _t, #"Feb Amount" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Jan Hour", Int64.Type}, {"Jan Amount", Int64.Type}, {"Feb Hour", Int64.Type}, {"Feb Amount", Int64.Type}}),
    UnpivoOtherCols = Table.UnpivotOtherColumns(ChType, {"Name"}, "Attribute", "Value"),
    SplitBySpace = Table.SplitColumn(UnpivoOtherCols, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    PivotedCol = Table.Pivot(SplitBySpace, List.Distinct(SplitBySpace[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    PivotedCol

Ps. If this helps solve your query please mark this post as Solution, thanks!