Replace null with if statement

[lf963]

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

[Rickmaurinus]

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.

[ImkeF · Accepted solution]

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"})

[lf963]

This works!!! Thank you!!!

[ImkeF]

Did you also try putting null in quotes?

[lf963]

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

[ImkeF]

..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]

Tried lower case Like but no luck

[ImkeF]

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]

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"})