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.
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
Hey Matt,
I'm getting an error I don't see anywhere else in this thread.
I'm applying the solution as follows:
= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue{"mycode"})
And it is returning this error:
Expression.Error: We cannot apply indexing to the type Function.
Details:
Value=[Function]
Index=mycode
Any idea how to resolve this?
Thanks,
Sean
Anonymous
Hiya,
It looks like there's a comma missing after the last ReplaceValue. Fingers crossed that's all it is!
= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue,{"mycode"})
Cheers,
Matt