Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
6 years ago
Solved

How to dynamically sum columns even some don't exist?

Hello,

 

I have 4 columns (Col1, Col2, Col3, Col4) that I import from a source and I want to add a new colum to sum Col1, Col3 and Col4.

 

My code is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUTIHYkMgNlCK1YlWMgGyjIHYCIhNwSKWUHmQiBlcDUjEAiISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SUM_1,3,4", each [Col1]+[Col3]+[Col4])
in
    #"Added Custom"

 

My issue is that sometimes some of the column I want to sum are not present. For example, the table only contains Col1, Col2 and Col4 (missing Col3). In this case the command I use above to sum the 3 columns gives error since Col3 is not found.

 

How can dynamically sum Col1, Col3 and Col4 avoinding errors if one or more of the columns are not present?

 

I hope make sence. Thanks

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi cgkas and Greg_Deckler ,

    I've split it up into digestible steps so that you can easily follow it up: 😉

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUTIHYkMgNlCK1YlWMgGyjIHYCIhNwSKWUHmQiBlcDUjEAiISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}}),
    GetNamesOfAllColumns = Table.AddColumn(#"Changed Type", "Custom", each Record.FieldNames(_)),
    IntersectDesiredColumnsWithAvailableOnes = Table.AddColumn(GetNamesOfAllColumns, "AvailableFields", each List.Intersect({{"Col1", "Col3", "Col4"}, [Custom]})),
    SelectTheMatchingRecords = Table.AddColumn(IntersectDesiredColumnsWithAvailableOnes, "Custom.1", each Record.SelectFields(_, [AvailableFields])),
    GetValuesFromTheSelectedColumns = Table.AddColumn(SelectTheMatchingRecords, "Custom.2", each Record.FieldValues([Custom.1])),
    AddThemUp = Table.AddColumn(GetValuesFromTheSelectedColumns, "DynamicSum", each List.Sum([Custom.2]))
in
    AddThemUp

     

7 Replies

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi cgkas and Greg_Deckler ,

      I've split it up into digestible steps so that you can easily follow it up: 😉

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUTIHYkMgNlCK1YlWMgGyjIHYCIhNwSKWUHmQiBlcDUjEAiISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}}),
    GetNamesOfAllColumns = Table.AddColumn(#"Changed Type", "Custom", each Record.FieldNames(_)),
    IntersectDesiredColumnsWithAvailableOnes = Table.AddColumn(GetNamesOfAllColumns, "AvailableFields", each List.Intersect({{"Col1", "Col3", "Col4"}, [Custom]})),
    SelectTheMatchingRecords = Table.AddColumn(IntersectDesiredColumnsWithAvailableOnes, "Custom.1", each Record.SelectFields(_, [AvailableFields])),
    GetValuesFromTheSelectedColumns = Table.AddColumn(SelectTheMatchingRecords, "Custom.2", each Record.FieldValues([Custom.1])),
    AddThemUp = Table.AddColumn(GetValuesFromTheSelectedColumns, "DynamicSum", each List.Sum([Custom.2]))
in
    AddThemUp

       

      • cgkas's avatar
        cgkas
        Icon for Helper V rankHelper V
        6 years ago

        Hi ImkeF  Thanks so much for your help. It works awesome! and it's self explained with the step names you defined. Excellent!

         

        Even I already accepted your code as solution, if possible and with the goal to learn, maybe you could help me saying why my attempt below fails or if that logic only needs an adjusment.

         

        The syntax to sum manually Col1, Col3 and Col4 is like this:

         

        Static_Sum = Table.AddColumn(#"Changed Type", "SUM_1,3,4", each [Col1]+[Col3]+[Col4]),

         

        Then, I thought in contruct the same syntax dynamically, I mean contruct the part "[Col1]+[Col3]+[Col4]" and when for example, in source, "Col3" be missing the contruction would be "[Col1]+[Col4]".

         

        I was able to complete the syntax in this step:

         

        ColumsToSum = Text.Combine(List.Transform(IntersectDesiredColumnsWithAvailableOnes,each "[" & _ & "]"), "+")

         

        But when I replace that step in the manual sum syntax, it shows in each row the value "[Col1] + [Col4]" instead to add them up.

         

        AddThemUp = Table.AddColumn(#"Changed Type", "SUM_Columns", each ColumsToSum)

         

        Below is my complete code.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlPSUTIHYgOlWJ1oJRMgyxiITcE8SyDLEIjN4HJQXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col4", Int64.Type}}),
    Static_Sum = Table.AddColumn(#"Changed Type", "SUM_1,3,4", each [Col1]+[Col3]+[Col4]),
    ColumnNames = Table.ColumnNames(Source),
    DesiredColumsToSum = {"Col1","Col3","Col4"},
    IntersectDesiredColumnsWithAvailableOnes = List.Intersect({ColumnNames,DesiredColumsToSum}),
    ColumsToSum = Text.Combine(List.Transform(IntersectDesiredColumnsWithAvailableOnes,each "[" & _ & "]"), "+"),
    AddThemUp = Table.AddColumn(#"Changed Type", "SUM_Columns", each ColumsToSum)
in
    AddThemUp

         

        Thanks and best regards