Forum Discussion

spoony's avatar
spoony
Helper I
9 years ago
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...
Need Help
  • MarcelBeug's avatar
    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.

ClackamasAshley's avatar
ClackamasAshley
Frequent Visitor
2 years ago

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