Forum Discussion

lf963's avatar
lf963
Frequent Visitor
4 years ago
Solved

Replace null with if statement

Hello,

 

I have a table shown in the following screenshot:

 

What I want to do is: if column Color is Red AND column Like is null, replace the null in the Like column with "N".

Therefore, the Like column of Apple, Cherry and Dragon fruit should be N.

 

However, the following syntax does not work:

 

= Table.ReplaceValue(#"Changed Type", each [LIKE], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceText,{"Like"})

 

 

Does anyone know how to do this?

 

Thanks

Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    OK, now the nulls are real nulls (as they are in italics now).
    So you have to go back to without quotes and use a different replacer function (ReplaceValue instead of ReplaceText) like so:

    Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceValue,{"Like"})

     

9 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    OK, now the nulls are real nulls (as they are in italics now).
    So you have to go back to without quotes and use a different replacer function (ReplaceValue instead of ReplaceText) like so:

    Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceValue,{"Like"})

     

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi lf963 ,
    your null does look like simple text and not the "real" null. So this would probably work:

     

    Table.ReplaceValue(#"Changed Type", each [LIKE], each if [Color] = "Red" and [Like] = "null" then "N" else [Like],Replacer.ReplaceText,{"Like"})

     

     

    • lf963's avatar
      lf963
      Frequent Visitor
      4 years ago

      Hello ImkeF,

       

      The screenshot shown in my original post was manually created. I should've made them real "null". The following screenshot is the correct one:

       

      The following syntax doesn't work:

      = Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceText,{"Like"})
  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    ..and the M-language is case sensitive, so you have to adjust to this:

    Table.ReplaceValue(#"Changed Type", each [Like], each if [Color] = "Red" and [Like] = "null" then "N" else [Like],Replacer.ReplaceText,{"Like"})
    • lf963's avatar
      lf963
      Frequent Visitor
      4 years ago

      Tried lower case Like but no luck

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Did you also try putting null in quotes?

    • lf963's avatar
      lf963
      Frequent Visitor
      4 years ago

      Yes I put the null in quotes in the "Replaced Value1" step in the following screenshot but it doesn't help.

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    4 years ago

    Hi lf963 ,

     

    You can try the following code and use the Replacer.ReplaceValue function. Hope that helps!

     

     

     

     

     

    = Table.ReplaceValue(#"Replaced Value1", each [Like], each if [Color] = "Red" and [Like] = null then "Y" else null, Replacer.ReplaceValue,{"Like"})

     

     

     

    --------------------------------------------------

    @ me in replies or I'll lose your thread

     

    Master Power Query M? -> https://powerquery.how

    Read in-depth articles? -> BI Gorilla

    Youtube Channel: BI Gorilla

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly.