Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spoony
Helper I
Helper I

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,{"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?

1 ACCEPTED SOLUTION

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

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.

Specializing in Power Query Formula Language (M)

View solution in original post

34 REPLIES 34

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
Not applicable

@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

@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

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?

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

@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

  

Hi @gk2go , no problem at all, glad I've been some help!

 

This might be a bit simplistic, but here's one solution:

 

= Table.ReplaceValue(#"Changed Type",each [Path],each if Text.Contains([Path],[Host]) = true then [Path] else [Host] & "/" & [Path],Replacer.ReplaceText,{"Path"})

 

To explain:

 

= Table.ReplaceValue(#"Changed Type",  <- same start as ever

each [Path],  <- we're tackling every value in Path

each if Text.Contains([Path],[Host]) = true  <- check if the [Path] contains the text in [Host].

then [Path]  <- if so, then we're happy, so can just leave the [Path] as it was...

else [Host] & "/" & [Path],  <- otherwise, put the [Host] value in, then a forward-slash, then the value that was in [Path] in the first place.

Replacer.ReplaceText,{"Path"})  <- and end by saying which column we're looking at.

 

Hope that's a help.

 

Cheers,

Matt

Hi mattlancs

 

I hope it's okay to ask another question to this old post. I tried to use your example to replace any value over multiple columns based on a criteria but can't seem to get it to work.

 

 

So - in your example. What if you wanted to replace not only null but any value from "SP Status" and "TS Status" based on your criteria. 

 

Thanks in advance

Lars

Hi Lars,

 

Sorry for the slow reply I only stumbled upon your question when I found this thread looking up the same problem again!

 

I've wrestled with this for a while but can't get it to work for more than one column. To do one column, replacing the null with each [#"TS opps.Status"] eventually worked. But I don't know how to refer to 'every value in either column', which seems like it should be straightforward. Then again I still don't understand what that little # adds to the previous example...

 

Apologies I can't give you more help - hopefully you're well past the problem by now anyway.

 

Matt

Hi mattlancs

 

I hope it's okay to ask another question to this old post. I tried to use your example to replace any value over multiple columns based on a criteria but can't seem to get it to work.

 

 

So - in your example. What if you wanted to replace not only null but any value from "SP Status" and "TS Status" based on your criteria. 

 

Thanks in advance

Lars

The last argument {"Gender"} is the list of columns in which values must be replaced. If you first select the applicable columns, and then choose for Replace Values, the generated code will include the names of the selected columns in the last argument.

Specializing in Power Query Formula Language (M)

ok it works but i got a minor issue after this step. All my column types has changed to any. I dono how this would affect it.

That seems to be a side-effect of Table.ReplaceValue.

 

You can either use my first solution or - after Table.ReplaceValue - select all columns and choose "Detect Data Type"  on the Transform tab (check if the detected types are correct and adjust the generated code where applicable).

 

Detect Data Type.png

Specializing in Power Query Formula Language (M)

ok thanks, i might use the column solution for now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.