Deleting parts of URL

[Anonymous]

Hi,

I have a column of URLs which I have to clean in order to provide a nice chart.

Say, I have 

http://www.google.com/maps/ldfisuhgsuiogs

and I have to get 

http://www.google.com

The length of URLs is different.

[Eric_Zhang · Accepted solution]

@

---

@Anonymous wrote:

Hi,

I have a column of URLs which I have to clean in order to provide a nice chart.

Say, I have 

http://www.google.com/maps/ldfisuhgsuiogs

and I have to get 

http://www.google.com

The length of URLs is different.

---

@Anonymous

You can add a custom column in Query editor.

if Text.PositionOf(Text.Replace([URL],"//","Iamdoubleslash"),"/")<0 then [URL] 
   else Text.Replace(
           Text.Range(
		        Text.Replace(
				            [URL],"//","Iamdoubleslash")
		   ,0,Text.PositionOf(
		           Text.Replace([URL],"//","Iamdoubleslash"),"/"))
				             ,"Iamdoubleslash","//")

[Aron_Moore]

I would use a column, something like:

Trimmed URL = LEFT( [URL Column], FIND(".com" , [URL Column] +3))

[parry2k]

I guess following will do it, it will look for first "/" and then get the url, if "/" not found then give whatever value exists

ClreanUrl = IF(FIND("/", Options[OptionChoice], , -1) = -1, Options[OptionChoice], LEFT(Options[OptionChoice], FIND("/", Options[OptionChoice])-1)) 

Replace options[OptionChoice] with your table field name

[Greg_Deckler]

Probably an easier way to do this, but try something like this:

Column = VAR substring = SUBSTITUTE(SUBSTITUTE(urls[URL],"http://","^"),"https://","`")
         VAR shortstring = LEFT(urls[URL],FIND("/",substring,1,LEN(urls[URL]))+1)
         RETURN SUBSTITUTE(SUBSTITUTE(shortstring,"^","http://"),"`","https://")