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 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

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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] } )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ok, thank you very much.

  • 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