cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Re: Query editor replacing values based on another column

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

Highlighted
Helper II
Helper II

Re: Query editor replacing values based on another column

@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

  

Highlighted
Advocate I
Advocate I

Re: Query editor replacing values based on another column

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

Highlighted
Regular Visitor

Re: Query editor replacing values based on another column

@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

Highlighted
Advocate I
Advocate I

Re: Query editor replacing values based on another column

@Seany_boy 

 

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors