Forum Discussion

XidoMM's avatar
XidoMM
Frequent Visitor
4 years ago
Solved

transform table

Hello guys, How can I convert the table above to the format below?   thx a lot!
  • lbendlin's avatar
    4 years ago
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Attribute]),
        #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "Value", List.Sum),
        #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Pivoted Column"))
    in
        #"Replaced Value"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".