Replace null values in text with condition on other columns

[Anonymous]

Hi,

I am trying to replace null values in a column with condions but it does not seem to detect null's, I have tried  ="", = null and is null with the luck. I am not sure what I am doing wrong. Here is my code.

=Table.ReplaceValue(#"Expanded Column",each [Trial], each if [Trial] = "" and [Date] > #date(2024,1,1) and Text.Contains([Product], "reg prodct") then "Not Required" else [Trial],Replacer.ReplaceText,{"Trial"})

However, even simplifying the code does not work.

= Table.ReplaceValue(#"Expanded Column",each [Trial], each if [Trial] = "" then "Not Required" else [Trial], Replacer.ReplaceText,{"Trial"} )

Of course this works

= Table.ReplaceValue(#"Expanded Column","","Not Required",Replacer.ReplaceValue,{"Trial"})

Anyone tell me what I am doing wrong?

[Anonymous]

Hi @Anonymous ,

Please try this:

Table.ReplaceValue(#"Changed Type",each [Trial],each if Text.Contains([Product],"reg prodct") and [Trial]="" and [Date] > #date(2024,1,1) then "Not required" else [Trial],Replacer.ReplaceValue,{"Trial"})

Change here into ReplaceValue:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

= null  is correct.  But you have other conditions that may not be true at the same time.

Note :  if you always want to replace null with a certain value you can use the coalesce operator  ??