Customer Column not subtracting where the value is null in Power Query

[DMfba]

Hi Everyone,
I am running into an issue subtracting one field from another field when one field is equal to null. If there is a better way to do this please give me some insight.

I am dealing with Year to date sales & profit values on a monthly basis. The goal is to determine the monthly sales and profit figures. To do this I created a custom column to subtract the Current Month Year to Date values from the Previous Year to Date values from last month. I added some conditions to take into account subtracting positive and negative values. All of which seems to be working. However, whenever I run into a case where there is a value in the Current Month but null in Last month it doesn't calculate a value. For example, If Feb has $10 and Jan was null, the MTD value should be positive $10 but Power Query is showing the MTD as blank.

Below is the "M" code for the custom column that I made.

try if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]- Number.Abs[PM_YTDProfit]
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]+ [PM_YTDProfit]
else if [PM_YTDProfit] = "null" then [YTD Profit]
else [YTD Profit] - [PM_YTDProfit] 
otherwise [YTD Profit]-[PM_YTDProfit]

I did try switching null to "null" both ways did not make a difference.

If any one has any suggestions, please let me know where I went wrong.


Thank You everyone.
David

 

[arunsjain]

Hi Everyone,
I am running into an issue subtracting one field from another field when one field is equal to null. If there is a better way to do this please give me some insight.

I am dealing with Year to date sales & profit values on a monthly basis. The goal is to determine the monthly sales and profit figures. To do this I created a custom column to subtract the Current Month Year to Date values from the Previous Year to Date values from last month. I added some conditions to take into account subtracting positive and negative values. All of which seems to be working. However, whenever I run into a case where there is a value in the Current Month but null in Last month it doesn't calculate a value. For example, If Feb has $10 and Jan was null, the MTD value should be positive $10 but Power Query is showing the MTD as blank.

Below is the "M" code for the custom column that I made.

try if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]- Number.Abs[PM_YTDProfit]
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]+ [PM_YTDProfit]
else if [PM_YTDProfit] = "null" then [YTD Profit]
else [YTD Profit] - [PM_YTDProfit]
otherwise [YTD Profit]-[PM_YTDProfit]

I did try switching null to "null" both ways did not make a difference.

If any one has any suggestions, please let me know where I went wrong.


Thank You everyone.
David

Hi,

You mentioned that MTD is blank, try replace blank with null.

if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]- Number.Abs[PM_YTDProfit]
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]+ [PM_YTDProfit]
else if [PM_YTDProfit] = null then [YTD Profit]
else [YTD Profit] - [PM_YTDProfit]

 

[I_cant_read]

Have you tried Transform> Replace Values (select the columns with null) "null" to 0 before you add a custom column. Or maybe after passing on a custom column?

 

[arunsjain]

Have you tried Transform> Replace Values (select the columns with null) "null" to 0 before you add a custom column. Or maybe after passing on a custom column?

Hi,

If you have replaced null with 0 then additional column should be like below

if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]- Number.Abs[PM_YTDProfit]
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]+ [PM_YTDProfit]
else if [PM_YTDProfit] = 0 then [YTD Profit]
else [YTD Profit] - [PM_YTDProfit]

Can you please upload some sample data?

 

[arunsjain]

Hi

I think you are trying to calculate current month profit. If that is the case why don't you use simple calculation [YTD Profit] - (minus) [PM_YTDProfit]. It will give same result as in conditional column you are tying to create.

Try following with conditional column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"YTD Profit", Int64.Type}, {"PM_YTDProfit", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"PM_YTDProfit"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Current month profit", each if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]+ Number.Abs([PM_YTDProfit])
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]- [PM_YTDProfit]
else if [PM_YTDProfit] = 0  then [YTD Profit]
else [YTD Profit] - [PM_YTDProfit]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Current month profit", type number}})
in
    #"Changed Type1"

 

[arunsjain]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"YTD Profit", type number}, {"PM_YTDProfit", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"PM_YTDProfit"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Current month profit", each [YTD Profit] - [PM_YTDProfit], type number)
in
    #"Inserted Subtraction"

 

[DMfba]

Hi I can't Read & Arunsjain,

Yes I did do a transformation to convert the blanks. The transformation I used was to replace Null to Zero. My goal was try to get the M code of :

if [PM_YTDProfit] = null then [YTD Profit]

However, I think it just may not be possible. Power Query, I think sees this as text and can't convert to a number when it's one column minus the other. The reason behind it, blank and 0 mean two different things in my data set. As far as value goes, I did need them to both be treated as a zero. Unfortunately, I won't be able to point out that difference in meaning.

Thank You,

David

 

[arunsjain]

Hi I can't Read & Arunsjain,

Yes I did do a transformation to convert the blanks. The transformation I used was to replace Null to Zero. My goal was try to get the M code of :

if [PM_YTDProfit] = null then [YTD Profit]

However, I think it just may not be possible. Power Query, I think sees this as text and can't convert to a number when it's one column minus the other. The reason behind it, blank and 0 mean two different things in my data set. As far as value goes, I did need them to both be treated as a zero. Unfortunately, I won't be able to point out that difference in meaning.

Thank You,

David

Hi,

Try following without replacing null with ZERO. I hope it will work.

if [PM_YTDProfit] = null then [YTD Profit] else if [YTD Profit] > 0 and [PM_YTDProfit] > 0 then [YTD Profit]-[PM_YTDProfit]
else if [YTD Profit] > 0 and [PM_YTDProfit] < 0 then [YTD Profit]+ Number.Abs([PM_YTDProfit])
else if [YTD Profit] < 0 and [PM_YTDProfit] > 0 then [YTD Profit]- [PM_YTDProfit]
else [YTD Profit] - [PM_YTDProfit]

 

[DMfba]

Hi Arunsjain,

I haven't tried that one and going in a different direction. That does sound like that way would work because there is no math portion in that 1st statement. Therefore, PQ won't run into the error of subtracting a number from text.

Thank you again for the help.
David