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?

[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

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

[mattlancs]

Hi @gk2go,

If you want it to stay the same as before, you can replace it with itself, so you put the same column name in as the one you're using the replace function on.

For example, in my table of employees, this line goes through the ContractedHours and looks for people with a JobTitle of "Admin". Then, if they're full time (i.e. have ContractedHours = 40) then replace that 40 with 100, otherwise leave it as it is.

= Table.ReplaceValue(#"Capitalized Each Word", 40, each if [JobTitle] = "Admin" then 100 else [ContractedHours], Replacer.ReplaceValue,{"ContractedHours"})

Step by step:

= Table.ReplaceValue(  <- summoning the replace-some-values function

#"Capitalized Each Word",  <- the name of the previous step, yours will be different

40,  <- the number we're going to replace

each if [JobTitle] = "Admin" then 100 else [ContractedHours],  <- here's the key bit: after the "else", replace it with the ContractedHours i.e. replace it with itself i.e. don't change anything

Replacer.ReplaceValue,{"ContractedHours"})  <- which column we're doing the replacement in.

Hope that's clear.

Cheers,

Matt

[Anonymous]

@mattlancs 

Hey Matt,

I'm getting an error I don't see anywhere else in this thread.

I'm applying the solution as follows:

= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue{"mycode"})

And it is returning this error:

Expression.Error: We cannot apply indexing to the type Function.
Details:
Value=[Function]
Index=mycode

Any idea how to resolve this?

Thanks,

Sean

[mattlancs]

@Anonymous 

Hiya,

It looks like there's a comma missing after the last ReplaceValue. Fingers crossed that's all it is!

= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue,{"mycode"})

Cheers,

Matt

[gk2go]

Thank you @mattlancs, that worked in indeed.

Can you perhaps help me apply this to this other case:

I have a path and a host columns, i want to find and remove all host substrings from the path.

So if [path] contains [host] then replace [host] in [path], else leave [path] as is.

How would you accomplish that?

[mattlancs]

Hi @gk2go,

That's an excellent puzzle, thank you! I've come up with a solution, though I will say it's well outside my comfort zone so it's perhaps not the most beautiful/efficient version. It seems to work from here though...

Because I don't know quite what your data looks like, I've had to assume that the [Host] could appear anywhere within the [Path], but it might be that it always appears at the end or at the beginning. In that case, you could trim a section out of the following formula to simplify it a bit.

= Table.ReplaceValue(#"Added Custom", each [Path], each if Text.Contains([Path],[Host]) = true then Text.Start([Path], Text.PositionOf([Path], [Host])) & Text.End([Path], Text.Length([Path]) - Text.PositionOf([Path], [Host]) - Text.Length([Host])) else [Path], Replacer.ReplaceText,{"Path"})

Using my now-established notation:

= Table.ReplaceValue(#"Added Custom",  <- the usual start, calling to do a replacement then namechecking the row before

each [Path],  <- here's a new bit to me: this is going to replace every entry in [Path], not just specific lines.

each if Text.Contains([Path],[Host]) = true then  <- first step is to test if the [Path] contains the [Host]. If so...

Text.Start([Path],  <- this function says we want to take the first few characters of what's in [Path].

Text.PositionOf([Path], [Host]))  <- you have to say how many characters, and you find that out by telling it the position of [Host] in [Path]. I'd have expected you'd need to put a -1 in here (because you want one less character than where [Host] appears) but apparently not!

&  <- So up to this point you've got everything from [Path] before where [Host] appears. But we also need everything afterwards. If [Host] only appears at one end, you only need either the two lines before or after this ampersand. This is just here to glue together the before and after. If you wanted to replace [Host], not just remove it, then you could add something else here then another & afterwards.

Text.End([Path],  <- next up is what comes at the end of the [Path], after the [Host]. So this function is saying we want the end of the [Path].

Text.Length([Path]) - Text.PositionOf([Path], [Host]) - Text.Length([Host]))  <- I don't know of an elegant way to do this - the number of characters we want is the total length of [Path] minus the number of characters before the [Host] minus the length of the [Host] itself. It's like trying to calculate how long you spent eating dessert: it's the time you spent eating the whole meal minus the time you spent on the starter minus the time you spent on the main course.

else [Path],  <- this is going back to the 'each if' line earlier - just put the [Path] value back if [Path] didn't contain [Host] in the first place.

Replacer.ReplaceText,{"Path"})  <- finally, as before, which column we're doing the replacement in.

Hopefully that's some help and will either work or inspire you to come up with an even tidier solution!

Cheers,

Matt

[gk2go]

@mattlancs it worked like a charm!

May I ask another related one?

I've got two fields, path and host. I want to replace the paths than don't contain the host, and if they don't pre-append the host to the path (so path becames host + path).

path                               | host              

example.com/a.html      | example.com //This is good do nothing

b.html                            | example.com // path should become example.com/b.html