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