Replace values from one column to another based on a condition

[kkanda]

Hello All,

I have a table with several columns out of which I need to replace the values in Col A based on a condition in Col B. Some values of these columns are given below:

Col A                                      Col B

11031*                                11031

15216*                                15216

1000                                     null

3066 SNO                            3066

10013                                   null

309891                                29291

I want the result to be:

Col A                                      Col B

11031                                   11031

15216                                   15216

1000                                     null

3066                                     3066

10013                                   null

29291                                   29291

The table has 40K records and Col B has only about 200 values. The rest of the values in Col B (which is a merged column from another table) are null.

The condition is if the ColB value is null, there will be no change in the value of Col A; if the Col B has a value other than null, the value in Col A will be replaced by Col B value for that record.

After going through the posts in the Forum and the internet, I wrote the following query in the Advanced Editor:

    #"Replace Values" = Table.ReplaceValue(#"Filtered Rows2",

        each [Col A],

        each if [Col B] = "" then [Col A] else [Col B],

        Replacer.ReplaceText,{"Col B "})

This works for the first occurrence of the non-null value of the table and does not replace the value in Col A afterwards.

I’ll appreciate if anyone can point out where I am going wrong… maybe somewhere “each” is missing.

Thank you

[jennratten · Accepted solution]

Hello!  You were pretty close it.  Instead of searching for blanks you would want to search for null.  Also, Replacer.ReplaceText will replace substrings; Replacer.ReplaceValue will replace the entire cell's contents.  So there are two options....

This first option uses the coalesce operator (??) which will simply return the first value that is not null out of the options you provide it:

Table.ReplaceValue ( #"Previous Step", each [Col A], each [Col B] ?? [Col A], Replacer.ReplaceValue, {"Col A"} )

The second option is to use if/then:

Table.ReplaceValue ( #"Previous Step", each [Col A], each if [Col B] = null then [Col A] else [Col B], Replacer.ReplaceValue, {"Col A"} )

Complete example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDbUUtKBMJRidYBCpkaGZmAhEAMiZGBgABQAs40NzMwUgv38gXwQEyZvaIxQYGlhaQjkGVkaAenYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t]),
    Custom1 = Source,
    #"Replaced Value" = Table.ReplaceValue(Custom1,"",null,Replacer.ReplaceValue,{"Col B"}),
    Custom2 = Table.ReplaceValue ( #"Replaced Value", each [Col A], each [Col B] ?? [Col A], Replacer.ReplaceValue, {"Col A"} )
in
    Custom2

[kkanda]

Hello,

Thanks for the suggestion. I tried both the options you suggested, but both of them are not working. 

When I use the "ReplaceValue" option, the Col B data type changes to number/string but there is no change in the output in Col A. 

I read on the internet posts that "ReplacedValue" is for number data type and "ReplaceText" is for string data types. 

[spinfuzer]

If the formula is impacting Col B then you have the wrong column in your last parameter.

After Replacer.ReplaceValue should be {"Col A"} instead of {"Col B"}

[kkanda]

I started from scratch and modified the generated code for Replace A with B. Finally the following code worked for me:

	#"Replace Values" = Table.ReplaceValue(#"Expanded Repl_Notific",
        each [Col A],
        each if [Col B] <> null then [Col B] else [Col A],
        Replacer.ReplaceValue,{"Col A"})