Power Query add two columns with a null value

[millhouse123]

I have a data set and I am trying to simply add to columns together, both columns are decimal type. Some of the values are null in one column or the other and no matter what I have tried the sum always returns a null value in the calculated column for those rows with null values in one column.

I have tried replacing all null with 0 and I have tried writing a formula so it only includes the non null value in the formula.

Any ideas to solve this would be much appreciated.

 

[Norie]

Try this formula replacing Col1 and Col2 with your column/field names.

(if [Col1]=null then 0 else [Col1])+(if [Col2]=null then 0 else [Col2])

 

[MarcelBeug]

Just use List.Sum:

= Table.AddColumn(Source, "Addition", each List.Sum({[Number1],[Number2]}), type number)

 

[Rickmaurinus]

There's several ways to approach it. You could try replacing nulls, using error handling, or using List.Sum.

Hope this helps: