Replacer.ReplaceValue / Replacer.ReplaceText

[colinmaitland]

Hi, I am trying to replace null and empty string values in a text data type column named Country with a value from another text data type column named "Country (Parent Organisation)" without changing the data type of the Country column from TEXT to ANY in a single step.

(a) Here is my source data …

(b) This is what I get when I use Replacer.ReplaceValue … i.e., the Country column data type is now ANY rather than TEXT.

 #"Replace Value" =
        Table.ReplaceValue(#"Previous Step",
            each [Country],
            each if [Country] = null or Text.Trim([Country]) = "" then Text.Trim([#"Country (Parent Organisation)"]) else Text.Trim([Country]),
            Replacer.ReplaceValue,
            {"Country"}
        )

(c) This is what I get when I use Replacer.ReplaceText … It is not replaceing the empty text values and of course it is not replacing the null values because null is not a text data type.

#"Replace Value" =
        Table.ReplaceValue(#"Previous Step",
            each [Country],
            each if [Country] = null or Text.Trim([Country]) = "" then Text.Trim([#"Country (Parent Organisation)"]) else Text.Trim([Country]),
            Replacer.ReplaceText,
            {"Country"}
        )

(d) This is what I get when I wrap the Replacer.ReplaceValues version in a Table.TransformColumnTypes step …

#"Replace Value" =
        Table.TransformColumnTypes(
            Table.ReplaceValue(#"Previous Step",
                each [Country],
                each if [Country] = null or Text.Trim([Country]) = "" then Text.Trim([#"Country (Parent Organisation)"]) else Text.Trim([Country]),
                Replacer.ReplaceValue,
                {"Country"}
            ),
            {{"Country", type text}}
        )

But is there a way to use Replacer.ReplaceText, or some other more straightforward way, to achieve this result in a single step?

Many thanks.

[Anonymous · Accepted solution]

Although I'm not at my computer and cannot offer you a proper technical solution, in situations like this with Power Query I would just do each of the individual steps--that is, replace the nulls, with the GUI function,  replace the blanks with the GUI function, and I mean replace them by right clicking on the value and then selecting Replace value, that way you get the correct technical steps that you can just cobble together into the if-then-else statement.

--Nate

[Anonymous · Accepted solution]

Although I'm not at my computer and cannot offer you a proper technical solution, in situations like this with Power Query I would just do each of the individual steps--that is, replace the nulls, with the GUI function,  replace the blanks with the GUI function, and I mean replace them by right clicking on the value and then selecting Replace value, that way you get the correct technical steps that you can just cobble together into the if-then-else statement.

--Nate

[colinmaitland · Accepted solution]

Hi Nate

Using your approach, the following works perfectly, including handling the fact that the last row has null for both Country and Country (Parent Organisation). It does, however, require more nested business logic than (d) in my original post, and also uses a mixture of Replacer.ReplaceValue and Replacer.ReplaceText, but it has been a useful exercise to explore this.

    #"Replace Value" =
        Table.ReplaceValue(
            Table.ReplaceValue(
                Table.ReplaceValue(
                    Table.ReplaceValue(
                        #"Previous Step", null, "X", Replacer.ReplaceValue, {"Country"}
                    ),
                    "", "X", Replacer.ReplaceValue, {"Country"}
                ),
                each [Country],
                each if Text.Trim([Country]) = "X"
                    then [#"Country (Parent Organisation)"]
                    else [Country],
                Replacer.ReplaceText,
                {"Country"}
            ),
            "X", null, Replacer.ReplaceValue, {"Country"}
        )

Here is the result ...

Many thanks
Colin