Handling Null Values

[Mederic]

Hello,

The article in the following link presents 2 methods for handling null values.
However, the 2nd method does not seem to give the same result as the 1st method.
How can we correct this problem?
Thanks in advance

Best regards

Date	Description	Credit	Debit
01/12/2024	Order 1234	1000
02/12/2024	Materials		300
03/12/2024	Order 1235	750
04/12/2024	Project Tulip	1000	540

[slorin]

Hi @Mederic 

another solution

List.Sum({[Credit],-[Debit]})

Stéphane 

[Mederic]

Hello @slorin ,
Thank you for this suggestion,
This solution would logically have its place in this article in a method 3
I have the impression that it is faster than method 2

In any case, I'm keeping it for my personal training and documentation. 

Best regards

[Mederic]

Hello @lbendlin 
Thank you for your reply and these clarifications,
Unfortunately, it doesn't work in my case,
I tried in the 2 versions of Excel: Microsoft Office Pro Plus 2019 and Microsoft 365.

In the article, method 1 has 2 steps : Replace and subtract
Method 2 is supposed to do this in 1 step with only Coalesce
The screenshots below, which I have copied from the article, show that the result is not the same

Best reagards

[lbendlin]

As @AlienSx mentioned you need to protect the COALESCE operator from the higher level subtraction operation with parentheses.

[AlienSx · Accepted solution]

Operator precedence comes in to play

1000 ?? 0 - 540 ?? 0 is equal to 1000 ?? (0 - 540) ?? 0 which is 1000

[Mederic]

Hello @AlienSx ,

Thank you for your support,
I think I've understood the logic behind the brackets,
This code now works :

= Table.AddColumn(#"Changed Type", "Custom", each ([Credit] ?? 0) - ([Debit] ?? 0), type number)

Have a nice day

Best regards

[lbendlin]

First off - replacing null values is not necessary in 99% of scenarios.  You can work with them just fine in your DAX calculations.

The Coalesce operator needs to have an actual null value to work with. By themselves the "Credit" and "Debit"  columns are initially typeless (defaulting to text) and will not show nulls.

Only after you explicitly indicate the column types will you see the nulls

And now you could apply the Coalesce transform (or any of the other transforms)

As stated earlier - most of the time this isn't necessary.