Forum Discussion
Custom column summing multiple columns - some rows not summing?
I am trying to sum mutiple columns in the same table using a Custom Column. This should be so simple, but some columns are summing correctly and others are returning a null value. What am I doing wrong?
The custom column Short-Term Savings should be the sum of columns Checking Account(s), Savings Accounts(s) and Cash. Here is what I have.
And here is what I get. Row 17 sums correctly, but rows 1, 10, 12, and 14 do not. I have already applied Trim and transformed the data type to fixed decimal. What is going on?
The first thing to note is that a null value in a formula is not a zero. And a null value is not equal to a blank value.
One solution is to replace null by 0 in each column using Replace Values.
Another solution is to create the costum column with the function List.Sum this way:
Short-Term Savings = List.Sum{(Checking Account(s), Savings Accounts(s), Cash)}
UPDATE! I figured it out. I just needed to replace the null values with 0.00 and now it works. Leaving this here in case someone else has the same question.
2 Replies
The first thing to note is that a null value in a formula is not a zero. And a null value is not equal to a blank value.
One solution is to replace null by 0 in each column using Replace Values.
Another solution is to create the costum column with the function List.Sum this way:
Short-Term Savings = List.Sum{(Checking Account(s), Savings Accounts(s), Cash)}
UPDATE! I figured it out. I just needed to replace the null values with 0.00 and now it works. Leaving this here in case someone else has the same question.