Replacer.ReplaceValue / Replacer.ReplaceText

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

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