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...

Need Help

MarcelBeug
9 years ago
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

Advocate II

2 years ago

Hi Ashley,

You're really nearly there! So, the approach I was discussing in this thread was about replacing values in an existing column without adding any more, while you were trying to add a new column to supersede the one you've got.
To take the original approach which keeps it all tidily within the existing columns, I'll give you the dumb-and-clumsy approach which I always take:

Right click on your Response Time column header, select "Replace values", and it gives you a prompt for what values you want to replace. Say you want to find 1, and replace it with 2 - this is just to get a line added to the code which you can then finesse later.
In the formula bar at the top of the screen, you'll hopefully then see something like

= Table.ReplaceValue(#"Changed Type",1,2,Replacer.ReplaceValue,{"Response Time (dec. mins)"})

You can then edit that line, and change it to basically what you'd written above, though with an added comma and an 'else':

= Table.ReplaceValue(#"Changed Type", each [#"Response Time (dec. mins)"], each if [Initiation Method] = "Self-Initiated" then 0 else [#"Response Time (dec. mins)"], Replacer.ReplaceValue,{"Response Time (dec. mins)"})

Hopefully that'll do it. Otherwise, if you want to tackle the issue by adding a new "Accurate response time" column then go to the Add Column tab at the top of the page, select Conditional Column, and fill it out like this: