Query editor replacing values based on another column

[spoony]

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,{"Gender"}),
#"Replaced Gender" = Table.ReplaceValue(#"Replaced OTH",each if [Surname] = "Manly" then "Male" else [Gender],[Gender],Replacer.ReplaceValue,{"Gender"})

in
#"Replaced Gender"

Im trying to change the gender column to "Male" if Surname column contains "Manly". It gives me an error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[ClackamasAshley]

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

[mattlancs]

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

[ClackamasAshley]

This was exsactly what I needed, thank you so much!

[fra123]

I am new to this, thank you in advance for your answers. 

 Have a dataset with 3 columns: Amount (decimal numbers positive and nebative values) - Id(several duplicate values - Type(a,b,c,d).

I just need to change values on amont upon conditions:

for type == "d" and amount <0 then change the amount into positive (abs value).

Is that possible? it seems a similar case to yours.

Thank you 

F.

[mattlancs]

Hi F,

The code would be something like:

= Table.ReplaceValue(#"Changed Type", each [Amount], each if [Amount] <0 and [Type] = "d" then Number.Abs([Amount]) else [Amount], Replacer.ReplaceValue,{"Amount"})

The safest/easiest way to add that (in my experience) is to do a "Replace Values" function on the column, by highlighting it and either choosing the option from the Transform tab at the top or by right-clicking on the column heading and choosing it. Pick something like replacing 1 with 2, you just want to get the fundamental line of code in there. Then edit that line to replace that "what am I replacing" and "what am I replacing it with" bit, by pasting in the section in bold above.

Good luck!

Cheers,

Matt

[fra123]

Hi Matt,

That's brilliant, thank you so much. It worked great. I hope I will get to learn more about coding in Power Query

[Rickmaurinus]

Marcel Beug gave a great solution there. For your reference, I wrote an elaborate guide on replacing values based on conditions. Also including capital insensitive replacements. The general construct is: 

= Table.ReplaceValue(
     #"Changed Type",
     each [Gender],
     each if [Surname] = "Manly" then "Male" [Gender] ,
     Replacer.ReplaceValue,{"Income"}
 )

https://gorilla.bi/power-query/replace-values/

Hope that helps!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[cuiping]

sorry i am a beginner study from scratch, may i know what this for..."#"Replaced OTH""

[KAURM]

Hi @cuiping the #Replaced OTH stands for the name of the step for which the  statement is being applied to.

You can see from below after #promote header the statement that has been applied to this dataset has been named #replaced OTH and the #Replaced Gender statement is applying those power query statements using both those datasets. 

#promote header = ....

#"Replaced OTH" = Table.ReplaceValue(#"promote header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
#"Replaced Gender" = Table.ReplaceValue(#"Replaced OTH",each if [Surname] = "Manly" then "Male" else [Gender],[Gender],Replacer.ReplaceValue,{"Gender"})

Hope that makes sense!

[mattlancs]

Hi Cuiping,

As far as I understand it, the bits before the = sign are just giving that line a name. So in this case it's saying "for my next trick I will perform an action called Replaced OTH". After the = sign, you get the name of the function that's being applied in this line, and then the first bit in the bracket is the name of the previous line. I presume this is so that it knows which order to run the lines in, somehow, but it can make it confusing to read at first.

You can change the names of the lines - Replaced OTH for example isn't a default sort of name, but it would have made the list of actions easier to read for its author - however if you're doing that in the editor you have to also remember to to change the name where it's mentioned in the next line, too.

Good luck!

Cheers,

Matt

[MarcelBeug]

Edit: wrong answer:

You can't replace values in a column based on values in another column.

Instead, create an additional column and replace the existing column with the new column.

Adjusted part of the code:

    #"Replaced OTH" = Table.ReplaceValue(#"Promote Header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
    #"Added Custom" = Table.AddColumn(#"Replaced OTH", "Custom", each if Text.Contains([Surname],"Manly") then "Male" else [Gender]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Gender"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Gender"}})
in
    #"Renamed Columns"

[spoony]

ok thanks that seem to work but i was checking this website and it shows someone got it to work. Is this not M power query?

https://social.technet.microsoft.com/Forums/en-US/6b006f20-f4a9-428f-94fa-c19d837dca0f/conditional-r...

[MarcelBeug · Accepted solution]

You are right. I was confusing Table.ReplaceValues with Table.TransformColumns.

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.

[brinky]

Any idea why this isn't working and when I add [Expiry_date] is works seemlesly

[mattlancs]

Hi Brinky,

I've had a play, and changing your formula to this should fix it:

= Table.ReplaceValue(#"Filtered Rows",
each [Route_Description],
each if [Route_No] = 125 then "Route" & Text.From([Route_No]) else [Route_Description],
Replacer.ReplaceText,{"Route_Description"})

It seems that where you're combining the text "Route" with a field you need to explicitly tell it that you're combining text with text.

Cheers,

Matt

[PaulMac]

Great answer @Anonymous .

Exactly what I needed to reference 2 columns!!

Paul

[Anonymous]

Thanks for this code :):

 #"Replaced 68 to 680" =

 Table.ReplaceValue(#"Replaced H1353",each [Payroll Reference Number],each if [Reporting Unit] = "Store5" and [Payroll Reference Number] = "68" then "680" else [Payroll Reference Number],Replacer.ReplaceValue,{"Payroll Reference Number"}),

I wanted to ammend an individual Payroll Number, based on the Store Name!

Experienced the issue whereby two people from different stored were given the same payroll number. 

However, i did not want to create a new Payroll Number column, as most other people suggested.

So once again, thanks 🙂

[vaibhav_osc]

Is there a way to do the same for multiple columns at once? 

I need to update entire row as #NA if a certain value is found in a column

[mattlancs]

I just got this working for multiple columns with the following line:

= Table.ReplaceValue(#"Expanded TS opps", null, each if [Sales Stage] = "Closed" then "Closed" else "Absent",Replacer.ReplaceValue,{"SP Status", "TS Status"})

This is part of a list of potential projects we might work on. This list is physically repeated in two other places, on our Sharepoint and in our timesheet system, and I wanted to check they line up to some degree. I've merged my queries, now I wanted to check where there's a null value in the Sharepoint or Timesheet system lists, if the project's closed on the master list, consider it closed, otherwise mark it as absent.

So what this is doing, blow by blow:

= Table.ReplaceValue(  <- we're replace some values here

#"Expanded TS opps",  <- this is just the name of the previous step. Yours will be different.

null,  <- find null values to replace

each if [Sales Stage] = "Closed" then "Closed" else "Absent",  <- check what the master list status is, and respond accordingly ...

Replacer.ReplaceValue,{"SP Status", "TS Status"})  <- ... in these two columns

I hope that's clear enough.

[gk2go]

@mattlancs, how to skip specifying an else?

I.e. i want to replace the value of a column if another column = something. Else leave everything as is.