How to replace a value in column A only based on another value in column B while in direct query

[drogzy]

Hello, 

Here is what I have: 

Column A	Column B
Development	Original
Observation	Original
Development	Re-entry
Development	Other
Abandon	Original
Observation	Original

What I want to do is replace the value in column B for every time Development is in column A with the word "Developing" but the thing is that for example the same value in column B is associated with other values such as Original. I don't want to create another column, I just want to make edits to column B. 

I tried to filter by development only in column a and then replace values in column B and then go back and delete the filtering step but it essentially changes all my original values in column B to "Developing". 

Not sure if I have to apply some type of script to the column or if there are simple edit steps. Keep in mind that this is a direct query linked to a SQL database. 

[Vijay_A_Verma · Accepted solution]

Use this (Replace #"Changed Type" with your previous step)

= Table.ReplaceValue(#"Changed Type",each [Column B], each if [Column A]="Development" then  "Developing" else [Column B], Replacer.ReplaceValue,{"Column B"})

[Vijay_A_Verma · Accepted solution]

Use this (Replace #"Changed Type" with your previous step)

= Table.ReplaceValue(#"Changed Type",each [Column B], each if [Column A]="Development" then  "Developing" else [Column B], Replacer.ReplaceValue,{"Column B"})

[drogzy]

Sorry but what previous step are you referring to? 

And where do I input that script? 

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktS83JL8hNzStR0lHyL8pMz8xLzFGK1YlW8k8qTi0qSyzJzM9Dl0LVFZSqC2QUVWKR8i/JSC0CizsmJealYJqEZIkCilwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [Column B], each if [Column A]="Development" then  "Developing" else [Column B], Replacer.ReplaceValue,{"Column B"})
in
    Custom1

[Anonymous]

Use the Custom Column by Example in Power Query to create a new column with the needed vaules

Add a column from examples - Power Query | Microsoft Docs