Forum Discussion
Resolver IIReplace values from one column to another based on a condition
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
Resolver IIHello,
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.
Solution SageIf 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"})