Forum Discussion

indhu's avatar
indhu
Helper III
8 years ago
Solved

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

Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    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]

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?



    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous you first condition should be like below

       

      if [Active] = true
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you :smileyhappy:

  • AAwad's avatar
    AAwad
    New Member
    3 years ago

    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.

  • nmckbcs's avatar
    nmckbcs
    Helper I
    8 years ago

    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?

    • indhu's avatar
      indhu
      Helper III
      8 years ago

      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,

  • eizquierdo's avatar
    eizquierdo
    Regular Visitor
    7 years ago

    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

    • TomMartens's avatar
      TomMartens
      Super User
      7 years ago

      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