Replacing nulls or empty strings doesn't always work - what am i doing wrong?

[Anonymous]

If i want to replace several values in one step including any nulls/empty strings my mcode seems to skip these nulls/empty strings.

example:

= Table.ReplaceValue(#"Previous Step", each [mycolumn], each

if [mycolumn] = "x" then "Yes" else

if [mycolumn] = ""  then "Unknown" else

if [mycolumn] is null  then "Unknown" else

"No" , Replacer.ReplaceText,{"mycolumn"})

the above leaves me with "Yes", "No" and (blank)

What am I doing wrong?

[v-yanjiang-msft]

Hi @Anonymous ,

May I know could my solution solve your problem? If so, would you mind accept it as solution? More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Anonymous ,

You should replace the Replacer.ReplaceText in the code with Replacer.ReplaceValue.

Result:

                              

Best Regards,
Community Support Team _ kalyj

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

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Anonymous - I understand what you are trying to achieve but I am not a fan of Table.ReplaceValue.  I prefer the Table.TransformColumns approach  Check the last paragraph in Section 3. 

The formula would look like this:

= Table.TransformColumns(#"Previous Step", 
 { 
  { "mycolumn", each 
      if _ = "x" then "Yes" 
      else if _ = ""  then "Unknown" 
      else if _ = null then "Unknown" 
      else "No" , type text
  }
 }
)