Power Query conditional replace column-CodePudding

I need to change the first letter of column A so that it starts with "2" if column A starts with "1" and column B is "on", otherwise no change. I added a step with the following formula in the Power Query Editor but no change!

= Table.ReplaceValue(#"Changed Type",each {"ColumnA"},each if Text.StartsWith({"ColumnA"}, "1") and {"ColumnB"}="on" then Text.ReplaceRange({"ColumnA"}, 0, 1, "2") else {"ColumnA"},Replacer.ReplaceText,{"ColumnA"})

Original:   
ColumnA     ColumnB
--------------------
"10500"        "on"
"21060"        "on"

Result: 
ColumnA     ColumnB
--------------------
"20500"        "on"
"21060"        "on"

CodePudding user response：

You've got the wrong brackets in there - you want something like:

= Table.ReplaceValue(#"Changed Type",each [ColumnA], each if Text.StartsWith([ColumnA], "1") and [ColumnB]="on" then Text.ReplaceRange([ColumnA], 0, 1, "2") else [ColumnA],Replacer.ReplaceText,{"ColumnA"})