Forum Discussion
Replacing nulls or empty strings doesn't always work - what am i doing wrong?
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?
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 } } )
3 Replies
Hi Anonymous ,
You should replace the Replacer.ReplaceText in the code with Replacer.ReplaceValue.
Result:
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 } } )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
