Power Query add two columns with a null value

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
334
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,395
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Just use List.Sum:

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

Forum statistics

Threads
1,085,813
Messages
5,386,040
Members
401,980
Latest member
chaithanyakrishnagck

Some videos you may like

This Week's Hot Topics

Top