Power Query - Copy certain text from 1 column to another based on value

[floggingmolly]

I have 2 columns titled Reason and Code. In the reason column there are numbers like 300, 331, 600, and 401. In the Code column there are codes like 3.3A, LTD-F, etc. If there is a 401 in the Reason column, I need to place 401 in the Code column. I've tried replace value but can't get it to work. Here is the formula I tried = Table.ReplaceValue(Source,each

,each if [Reason] = "401" then [Code] = 401 else [Code],Replacer.ReplaceValue,{"Code"}) but it's not working. It says that the column Code cannot be found but its clearly on the table so I'm not sure what I'm doing wrong. Any help would be greatly appreciated.

 

[GraH]

You can add a new column code and apply an if statement. Then delete the original column. Renate the new column. You can't replace values in a column with a formula like that (too my level of understanding of M).

 

[floggingmolly]

The formula in my post works on another table but not on the one I need it to work on. Not sure why.

 

[GraH]

Why not simply use

Text.Replace([Code], "old", "new")

inside the if then else.

 

[schwiggiddy]

Might be just taking a shot in the dark, but should your

[Code] = 401

in your if statement be

[Code] = "401"

I'm suspecting maybe a datatype mismatch with no quotes as you have it.