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 There,
I am having an issue that I think can be solved by the same method, I'm just not quite there yet and would appreacite some help if possible.
I have police call data, and am trying to caluclate response times for calls that were requested by the public. My dataset has both public-requested, and deputy-inititated calls (like traffic stops). Deputy-Inititated calls dont have response times since they are self-inititated. The issue is, sometimes a response time is recorded in error in the data collection process based on the caputed timestamps for a deputy-inititated call, which makes no sense, but it's there nonetheless so I need to eliminate them from the actual public-dispatched response times data. I don't want to filter these calls out completely, as I still need the accurate call counts overall. I need to change all response times for these deputy-inititated calls to "0" mins so they don't affect my dispatched response averages.
So my condition is: IF [Inititated Method] = "Self-Inititated" then [Response Time (dec. mins)] = 0, else [Response Time (dec. mins)].
Inititation Method is a text-type column, and Response Time (dec. mins) is a decimal number-type column.
I tried to create a new "Accurate Response Time" column in Power Query using this condition, but I can't get it to stick. Using examples from this thread I came up with this, but it's not working. Any ideas? Much appreacited!
= Table.ReplaceValue(
#"Changed Type",
each [#"Response Time (dec. mins)"],
each if [Initiation Method] = "Self-Initiated" then 0 [#"Response Time (dec. mins)"]
Replacer.ReplaceValue,{"Response Time (dec. mins)"}
)
Ashley
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:
Cheers,
Matt
This was exsactly what I needed, thank you so much!
