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.
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.
ok it works but i got a minor issue after this step. All my column types has changed to any. I dono how this would affect it.
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.