Forum Discussion
Solved
Unpivot Multiple Coumns
I have a direct connection to a databased containing sales transaction data from a point of sale. Each transaction can split the revenue between 6 profit centers. In the transaction data there a...
I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"), OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}) in OptionalRemove
ImkeF Thanks for the reply. Can you clarify what you mean on the step 3? Am I pivoting the first column "attribute.1" on the newly created "attribute.2" column?
Thanks!
I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
OptionalRemove