cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
indhu
Helper III
Helper III

Handle null values in custom column

I have two columns which have null values I want to create a new custom column which finds the difference between them producing null values as such. 

 

I am aware that I can convert the null values of two columns to 0 and proceed further but I wish to keep it as such.

 

I tried using advanced editor and using if conditions in a custom column but couldn't find a way to handle null values.

 

Code used in custom column:

 

if [ColumnA] < [ColumnB] then 0 
else if [ColumnA] = "null" or [ColumnB] = "null" then "null"
else [ColumnA] - [ColumnB]

This is how code looks in advance editor:

 

#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Result ", each if [ColumnA] < [ColumnB] then 0 
else if [ColumnA] = "null" or [ColumnB] = "null" then "null"
else [ColumnA] - [ColumnB]),
    #"Result" = #"Added Custom1"{0}[#"Result "]

Error I get:

 

Expression.Error: We cannot convert the value null to type Logical.
Details:
    Value=
    Type=Type

PS: This logic works fine in a calculated column but I wish to work this custom column

 

Can somebody please point out in the right direction?

 

Thanks,

Indhu

1 ACCEPTED SOLUTION

sorry there was typo in my formula, it suppose to be then instead of the

 

if [ColumnA] = null or [ColumnB] = null then null
else if  [ColumnA] < [ColumnB] then 0 
else [ColumnA] - [ColumnB]





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

15 REPLIES 15
chandni90
Frequent Visitor

Hello,
I have a similar situation where I am looking at two columns and creating a custom column based on the two columns.
However, I am not getting the expected result. What am I doing wrong?

error.PNG

error.PNG

@chandni90 you first condition should be like below

 

if [Active] = true





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thank you Smiley Happy

eizquierdo
Frequent Visitor

I've searched for different ways to do IF or Case statements in power BI and keep running across folks using code simillar to the one in this thred.

 

i.e. IF logic THEN true ELSE false

 

However when I try to use this sort of function in Power BI now it requires for the IF statement to being with " IF( " and further makes it look just like it would in standard excel.

 

i.e. IF(logic,[true],[false])

 

I'm I missing something here? If there a certain setting in power bi that must be checked to adjust the functions? Was there a recent update?

 

Emilio

Hey @eizquierdo,

 

this discussion has been about "if condition then something else somethingdifferent" this is the syntax in the M language used by PowerQuery, you mentioned the more EXCEL like IF(condition, trueresult, falseresult) syntax used in DAX.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
nmckbcs
Helper I
Helper I

Can you just replace the null values with nothing. So it would be a replace values and then put replace null and then leave the replace with section blank?

@nmckbcs thanks for your reply. 

 

Can I please know how do you replace null with nothing? 

 

In home ribbon replace a value doesn't allow to do it without any values 

 

Thanks,

Can you share a sample file?

replace you "null" without quotes null

 

if [ColumnA] = null or [ColumnB] = null the null
else if  [ColumnA] < [ColumnB] then 0 
else [ColumnA] - [ColumnB]





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Screen Shot 2018-07-18 at 2.29.33 PM.png

 

@nmckbcs please find the sample. 

 

Error screen shot:

Screen Shot 2018-07-18 at 2.38.40 PM.png

 

@parry2k I tried that as well didn't work getting the same error

 

Thank you both 🙂 

sorry there was typo in my formula, it suppose to be then instead of the

 

if [ColumnA] = null or [ColumnB] = null then null
else if  [ColumnA] < [ColumnB] then 0 
else [ColumnA] - [ColumnB]





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@indhui just tested at my end and it worked see below

 

null.PNG

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





I figured out what is wrong. I didn't use null case first used that in else if so it didn't work and got error. 

 

The code I used:

 

if [Column A] < [Column B] then 0 
else if  [Column A] = null or [Column B] = null then null
else [Column A] - [Column B]

Thanks @parry2k 😄 works now 

@indhualthough it worked after you moved the first if condition but I'm still not sure why it didn't work when it was first condition, still curious to find out the underline issue.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





weird but felt the same. Will dig it if I can and post here 🙂 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.