Simple SUM of two columns not working (only null values returned)

[Anonymous]

Hi,

I am having a strange problem in the query editor (in Power BI). I want to add a new column that is the SUM of two columns in the same table:

It's really strange, if the formula for PASok is just a SUM of the other 2 columns that have some values, how can the new column only return null values? any idea what can be going on here?

Thank you very much

[dufoq3]

This could be achieved simply by coalesce operator ??

let
    Source = #table(type table[A=number, B=nullable number], List.Zip({ List.Transform(List.Random(20,1), each Number.Round(_, 2) * 100) , List.Repeat({null}, 20) })),
    Ad_Sum = Table.AddColumn(Source, "Sum", each [A] + ([B] ?? 0), type number)
in
    Ad_Sum

[Anonymous · Accepted solution]

The + operator is returning a null because the [PAS] field is null ( number + null = null). This behavior is standard for all operators within PQ (+,-,/,*) and sql. You can override this behavior with List.Sum. List.Sum ignores nulls by definition.

List.Sum( { [Services Proactive], [PAS] } )

[PFJ86]

Thanks!

[Anonymous]

Ok, thank you very much.