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

[cgkas]

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

[Greg_Deckler]

@ImkeF  - I'm kind of interested in the answer to this one myself...

[ImkeF · Accepted solution]

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]

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

[ImkeF]

Hi @cgkas ,

in step "ColumnsToSum" you're creating a text string.

Power Query is happy with that string and returns it.

It is not aware that this should instead be interpreted as a command.

To tell PQ to handle this as a command instead, you'd have to use the function "Expression.Evaluate".

But the problem with that would be that for the special case of record constructors, even this would't work unfortunately.

Here's a tweak of your code that will work:

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}),
    AddThemUp = Table.AddColumn(#"Changed Type", "SUM_Columns", each List.Sum(List.Transform(IntersectDesiredColumnsWithAvailableOnes, (l) => Record.Field(_, l))))
in
    AddThemUp

If you use Table.AddColumn, the values if the current row will by default be passed onto the function that ist used in the 3rd parameter and will be represented by an underscore (_) if needed. Using the function Record.Field allows you to pass the name of the field as a string/variable ("l" in that case). 

So for each row in the table, you're iterating the "IntersectDesiredColumnsWithAvailableOnes "-list and use their expressions as the selector for the current rows/records fields.

[cgkas]

Thanks @ImkeF for the tweak and explanation. I almost understand what is the logic you used.

I've tried to separate in steps this part

each List.Sum(List.Transform(IntersectDesiredColumnsWithAvailableOnes, (l) => Record.Field(_, l)))

But I only see what happens up to here (like see Excel Formula step by step):

each List.Sum(List.Transform({"Col1","Col4"}, (l) => Record.Field(_, l)))

but I not sure what happens later. It would be something like this for first row?

//For first row:
each List.Sum(List.Transform(IntersectDesiredColumnsWithAvailableOnes, (l) => Record.Field(_, l)))
each List.Sum(List.Transform({"Col1","Col4"}, (l) => Record.Field(_, l)))
each List.Sum({Col1.value,Col4.value})
each List.Sum({6,0})
6

[ImkeF]

Hi @cgkas 

your steps look about alright to me. What exactly is still missing for you?

[cgkas]

@ImkeF I only had doubts in how it works the tweak you did in last step, but I think now I understand better. Thanks so much for the kind help.