Forum Discussion
Solved
Query editor replacing values based on another column
I can't seem to get this to work in query editor: let Source = Excel.Workbook #promote header = .... #"Replaced OTH" = Table.ReplaceValue(#"promote header"," ","OTH",Replacer.ReplaceValue,{"Ge...
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.
That seems to be a side-effect of Table.ReplaceValue.
You can either use my first solution or - after Table.ReplaceValue - select all columns and choose "Detect Data Type" on the Transform tab (check if the detected types are correct and adjust the generated code where applicable).
ok thanks, i might use the column solution for now.