Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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]


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

17 REPLIES 17
AAwad
New Member

Try the Replacer function:

Replacer.ReplaceValue(value as any, old as any, new as any)

For Example:

Replacer.ReplaceValue([ColumnA],null,0)

 will return 0, so you can use it in your custom column for all the columns that you will use to handle nulls in these columns.

So using this function for any column in your formula will always return a value instead of returning an error.

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

One of these days I'll remember to lower case TRUE in M. Thank you parry2k!

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]


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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]


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

 

null.PNG

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.