Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkanda
Resolver I
Resolver I

Replace values from one column to another based on a condition

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

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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

View solution in original post

4 REPLIES 4
jennratten
Super User
Super User

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

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. 

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"}

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"})

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors