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.
mattlancs, how to skip specifying an else?
I.e. i want to replace the value of a column if another column = something. Else leave everything as is.
Hi gk2go,
If you want it to stay the same as before, you can replace it with itself, so you put the same column name in as the one you're using the replace function on.
For example, in my table of employees, this line goes through the ContractedHours and looks for people with a JobTitle of "Admin". Then, if they're full time (i.e. have ContractedHours = 40) then replace that 40 with 100, otherwise leave it as it is.
= Table.ReplaceValue(#"Capitalized Each Word", 40, each if [JobTitle] = "Admin" then 100 else [ContractedHours], Replacer.ReplaceValue,{"ContractedHours"})
Step by step:
= Table.ReplaceValue( <- summoning the replace-some-values function
#"Capitalized Each Word", <- the name of the previous step, yours will be different
40, <- the number we're going to replace
each if [JobTitle] = "Admin" then 100 else [ContractedHours], <- here's the key bit: after the "else", replace it with the ContractedHours i.e. replace it with itself i.e. don't change anything
Replacer.ReplaceValue,{"ContractedHours"}) <- which column we're doing the replacement in.
Hope that's clear.
Cheers,
Matt