Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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] } )

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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