Forum Discussion
Query editor replacing values based on another column
You are right. I was confusing Table.ReplaceValues with Table.TransformColumns. :smileyembarrassed:
My solution works though, but the code you are looking for:
#"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})Edit: it seems you switched "old" and "new" in your cide.
I am new to this, thank you in advance for your answers.
Have a dataset with 3 columns: Amount (decimal numbers positive and nebative values) - Id(several duplicate values - Type(a,b,c,d).
I just need to change values on amont upon conditions:
for type == "d" and amount <0 then change the amount into positive (abs value).
Is that possible? it seems a similar case to yours.
Thank you
F.
Hi F,
The code would be something like:
= Table.ReplaceValue(#"Changed Type", each [Amount], each if [Amount] <0 and [Type] = "d" then Number.Abs([Amount]) else [Amount], Replacer.ReplaceValue,{"Amount"})
The safest/easiest way to add that (in my experience) is to do a "Replace Values" function on the column, by highlighting it and either choosing the option from the Transform tab at the top or by right-clicking on the column heading and choosing it. Pick something like replacing 1 with 2, you just want to get the fundamental line of code in there. Then edit that line to replace that "what am I replacing" and "what am I replacing it with" bit, by pasting in the section in bold above.
Good luck!
Cheers,
Matt
Hi Matt,
That's brilliant, thank you so much. It worked great. I hope I will get to learn more about coding in Power Query
