Subtracting formula

[Kishore_C]

Hi, I have values in column 'AM', 'U'&'S', which are all combination of positive and negative values, now I have used custom column with a standard formula which should give values by subtracting 'AM-S-U' columns, I observed that Powerquery taking only 'AM' column as base to subtract all rest of values from it, which is not accurate, which is if the Column 'AM' is 0 and 'U' or 'S' having values, the formula is not returning any values from U or S.
i have attached the powerquery source and output sheet.

expected output:

U	S	AM	AM-S-U
[td width="48pt"] 81.73​	0	0	[td width="48pt"] -81.73​
[td width="48pt"] 0​	[td width="48pt"] [td width="48pt"] 300​	[td width="48pt"] 0​	[td width="48pt"] -300​
[td width="48pt"] 0​	[td width="48pt"] 0​	[td width="48pt"] 10548.35​	[td width="48pt"] 10548.35​

​

[/td]

​

[/td]

​

[/td]

​

[/td]​

[/td]

​

[/td]

​

[/td]

​

[/td]

​

[/td]

​

[/td]

​

[/td]

]​

 

[Debaser]

You have null values in the source column, so you end up with null as a result. You can handle that using:

=[#"Current (Home Currency) [A]"]??0 - [#"Unapplied Payment (Home Currency) "]??0 - [#"On-Account Credit Memo (Home Currency) [C]"]??0

 

[Kishore_C]

I have tried that, it is completely ignoring "Unapplied Payment (Home Currency" column.

 

[Debaser]

Apologies, I missed out some brackets:

= Table.AddColumn(#"Reordered Columns", "Netoff check", each ([#"Current (Home Currency) [A]"]??0) - ([#"Unapplied Payment (Home Currency) "]??0) - ([#"On-Account Credit Memo (Home Currency) [C]"]??0))

You don't actually appear to have nulls in the B and C columns, but probably better safe than sorry. Also, you could simply replace null with 0 in all three columns before using the original formula you had, which would likely be a better option if you have other calculations to do.

 

[p45cal]

See in the attached whether the last step in query Table3 (2) called AddedCustom is giving you the expected results.
it uses List.Sum() which ignores null values:

=List.Sum({[#"[A]Current (Home Currency)"],-[#"[B]Unapplied Payment (Home Currency)"],-[#"[C]On-Account Credit Memo (Home Currency)"]})

so there is no need to convert nulls to zeroes (I've removed that step).