Power Query conditional replace column

Question

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"

Rory · Accepted Answer · 2022-01-31 10:29:30Z

2

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

answered Jan 31, 2022 at 10:29

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power Query conditional replace column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related