Forum Discussion
Different Columns on Row level as columns
- 2 years ago
1) Select Entry No. and Cost Code. Right Click and Unpivot Other Columns
2) Select Attribute and then cost code. Next Right Click and click merge. Seperator is space in the example.
3) Select Column Name of Merged Column. Transform Tab --> Pivot Column --> Values Column = Value. Aggregate Sum.
If you want to replace to null values with 0 then edit your last step from List.Sum to each List.Sum(_) ?? 0. Or just select all of them and do a replace value null with 0 with the GUI.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1BCsAwCAT/knMoamJLjm2fEfL/b8S4xEIPu4KDY++JU043kTV7C6NGBnuw9UY2effVGucKmIRRPyOTBoTyUodHW6ciWZyX4OX3ssZLaJulmnRM", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry No." = _t, #"Cost Code" = _t, #"Cost Amount" = _t, #"% Cost" = _t, #"Cost Payed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry No.", Int64.Type}, {"Cost Code", type text}, {"Cost Amount", Int64.Type}, {"% Cost", type number}, {"Cost Payed", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cost Code", "Entry No."}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Cost Code"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum) in #"Pivoted Column"
1) Select Entry No. and Cost Code. Right Click and Unpivot Other Columns
2) Select Attribute and then cost code. Next Right Click and click merge. Seperator is space in the example.
3) Select Column Name of Merged Column. Transform Tab --> Pivot Column --> Values Column = Value. Aggregate Sum.
If you want to replace to null values with 0 then edit your last step from List.Sum to each List.Sum(_) ?? 0. Or just select all of them and do a replace value null with 0 with the GUI.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1BCsAwCAT/knMoamJLjm2fEfL/b8S4xEIPu4KDY++JU043kTV7C6NGBnuw9UY2effVGucKmIRRPyOTBoTyUodHW6ciWZyX4OX3ssZLaJulmnRM", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry No." = _t, #"Cost Code" = _t, #"Cost Amount" = _t, #"% Cost" = _t, #"Cost Payed" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry No.", Int64.Type}, {"Cost Code", type text}, {"Cost Amount", Int64.Type}, {"% Cost", type number}, {"Cost Payed", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cost Code", "Entry No."}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Cost Code"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"