List.Sum and List.Product together

[Oaktree]

This should be simple, but I can't get the syntax right.

I have 5 columns in my data, each of which might contain null values:

Field A Values
Field A Percentages
Field B1 Values
Field B2 Values
Field B Percentages

I just want to add a new column that equals (Field A Values * Field A Percentages)+((-Field B1 Values + Field B2 Values)* Field B Percentages)

This is what I tried:

#"Add Values" = Table.AddColumn(#"(Previous Step Name)", "Calculation Result", each
List.Sum(
List.Product(
{[Field A Values],
[Field A Percentages]}
),
List.Product(
{[Field B Percentages],
List.Sum(
{-[Field B1 Values],
[Field B2 Values]}
)}
)
)
)

That compiles OK (no syntax errors detected), but it outputs "Error" in each row.

What's the *right* way to nest List.Sum and List.Product usages here?

 

[GraH]

Hi Oaktree,

Maybe I misunderstand your query. But are your inputs on the row level? Then I believe these are not a list and you can write the formula like you did:
Field A Values * Field A Percentages)+((-Field B1 Values + Field B2 Values)* Field B Percentages.

Else doesn't your first List.Sum require a list input with curly brackets too?

"Add Values" = Table.AddColumn(#"(Previous Step Name)", "Calculation Result", each
List.Sum( {
List.Product(
{[Field A Values],
[Field A Percentages]}
),
List.Product(
{[Field B Percentages],
List.Sum(
{-[Field B1 Values],
[Field B2 Values]}
)}
)}
)
)

 

[Oaktree]

Thank you! The issue was exactly the missing curly brackets on the outer List.Sum.

 

[GraH]

Most welcome! Thanks for the feedback. Took a while before I spotted it