Forum Discussion
How to replace a value in column A only based on another value in column B while in direct query
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.
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"})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
4 Replies
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
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"})Sorry but what previous step are you referring to?
And where do I input that script?
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
