Forum Discussion

SteveDesmedt's avatar
SteveDesmedt
Frequent Visitor
2 years ago
Solved

Different Columns on Row level as columns

Hi all,    I have following problem in transforming my data.   Table 1 Entry No. Cost Code Cost Amount % Cost Cost Payed 1 A00 100 21 21 1 B00 200 0 0 1 C00 1000 6 60...
  • spinfuzer's avatar
    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"