how extract xyz... from www.abc/mnp/en-us/xyz...../123/opq......

[shiyip]

Hi,

I have a column that has urls. For example, www.abc/mnp/en-us/xyz..../123/opq.....

I need to create a new column that extracts the characters between the 3rd "/" and the 4th "/"/

How do I write that DAX in Power BI? 

In this case, the extracted value will be xyz...

Several notes:

The number of characters between the 3rd "/" and the 4th "/" is uncertain. It could be xyz, or xxyzz, or xyzzz, etc.

The total number of characters before the 3rd "/" is uncertain.

The value between 2nd "/" and the 3rd "/" is always en-us.

Thanks a lot!

Shiyi

[Greg_Deckler]

@shiyip - I agree with @MattAllington, the M code will look something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi8v10tMStbPzSvQT83TLS3Wr6is0gMBfUMjY/38gkKlWB24Mr3k/FyQ0sSUtMTi1ERidOQXpesnJhamZhZnV2Wm4tQRCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"address", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "address", "address - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","address - Copy",Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),{"address - Copy.1", "address - Copy.2", "address - Copy.3", "address - Copy.4", "address - Copy.5", "address - Copy.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"address - Copy.1", type text}, {"address - Copy.2", type text}, {"address - Copy.3", type text}, {"address - Copy.4", type text}, {"address - Copy.5", Int64.Type}, {"address - Copy.6", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"address - Copy.1", "address - Copy.2", "address - Copy.3", "address - Copy.5", "address - Copy.6"})
in
    #"Removed Columns"

If you really, really, really want to do it in DAX, this may be a more readable version of what @sornavoor provided:

Column = 
//Get the starting position by finding "/en-us/" and then adding 7 since "/en-us/" is 6 characters
VAR startpos = FIND("/en-us/",[address])+7
//Find the next "/" character and subtract the position found above to get number of characters to retrieve
VAR numchars = FIND("/",[address],startpos)-startpos
//Grab the text starting at the startpos and the calculated numchars
VAR mytext = MID([address],startpos,numchars)
RETURN (mytext)

[shiyip]

Hi @Greg_Deckler@sornavoor@MattAllington,

Thank you so much for all your quick responses. I didn't expect it to be so complex. WOW. I will try power query. 

In my data table, there are 2 columns. One column is website_domain. One is website_url. Depends on which domain it is, sometimes I need to extract the value between the 3rd and 4th "/" from the url column , other times I need to extract the value between 2nd and 3rd "/", othertimes I need to extract the value between 5th and 6th "/". I try to put all the extracted values in a  new column as the new "page_category". Will power query work with this flexiable situation?

Thank you,

Shiyi

[Greg_Deckler]

If you have a rhyme or a reason as to when you need which, you can probably code it. Again, I would go with @MattAllington's suggestion and split the column into parts and then code in the business logic of when you need 2nd and 3rd or 3rd and 4th or 4th and 5th. Any chance you can explain the logic as to when you need which value?

[sornavoor]

While I would  highly recommend following MAllington's  approach,  Below is something that may be useful.

=MID(<text>,FIND("/",<text>,FIND("/",<text>,FIND("/",<text>)+1)+1)+1,FIND("/",<text>,FIND("/",<text>,FIND("/",<text>,FIND("/",<text>)+1)+1)+1)-FIND("/",<text>,FIND("/",<text>,FIND("/",<text>)+1)+1)-1)

Example  <text> input:  "https://msdn.mgfgfdsgficrosoft.com/en-us/library/xyzzzzzz.aspx"

output:    "en-us"

Please make sure to trap situations where there are  no third "/"

Hope this helps!. 

[MattAllington]

You should use Power Query for this, not DAX.

When loading the table using "Get Data" you can duplicate the column (Add Column\Duplicate Column).  

Then with the new column Transform\Split Column\Split by delimiter and select the backslash.

Delete the extra columns you don't need