Parse part of column text

[Anonymous]

I have a column that includes a number of web site URLs. The web site has language localizations, so some of the page urls start with /xx/ where xx=language code. I want to be able to filter my traffic report based on country, to see which country localizations are getting the most traffic.

I can't figure out how to filter based on this string of text. I tried this , which works for up to 2, but I have more than 2:

country =
IF(
IFERROR(SEARCH("/de/",[Page]),0) = 0,
IF(IFERROR(SEARCH("/en/",[Page]),0) = 0, "other", "en"), "de"
)

[GilbertQ]

Hi @Anonymous, what I have found works really well is to not create any calculated columns. 

Instead what I do, is to open the Query Editor and create my column inside the Query Editor.

The first reason is that you have a wealth of options in terms of how you want to create your new column. As with your example you could duplicate the URL and then split column by delimeter and split it by "/" and then once you get that you could then apply some conditional logic based on where the localizations are. (Even if they are in different columns, you could combine them into a new column)

And secondly by creating your columns in the Query Editor, it makes your Power BI model faster and more efficient, because when it loads from the Query Editor to the Power BI Model, it will then do the compression then.

[Anonymous]

Hi @guavaq, yes I am wanting to create a new column for this. I thought about using "split", but not all URLs have the 2-digit county codes. 

Here's a sampling of the column:

[Baskar]

Hi , Try this one u should help u

create one calculated Column

Column 2 = var cur_text = Page   ---- Your column Name

return LEFT(cur_text, FIND("/",cur_text,2,0))

It will give u the language like /en/ , /de/, from there u can achieve your result .

if u can't able let me know i will help u 

[GilbertQ · Accepted solution]

Hi @Anonymous, thanks for the image it helps a lot.

What I would do is to split it by the delimeter "/". From your image it does appear that the language is always at the start of the URL you have shown.

Then it would then take all the first values and put that into a column. (Column 1)

Next I would duplicate this first column (Column 1) and then change it to count the length of characters.  (The reason for this is that the language codes are always 2 charcters)

Finally I would create a new column using the Conditional Column and where the length is 2 characters then put this into my new column.

This is what the output looks like when I did a quick mock up, with the Language column being my final column.

NOTE: Columns URL - Copy.1,URL - Copy.2,URL - Copy.3,URL - Copy.4 are from the delimeter. Whilst column URL - Copy.2 - Copy is the duplicated column from URL - Copy.2

[Anonymous]

Thank you for your help!  🙂

[Anonymous]

@Anonymous,

I agree with @GilbertQ about using Power Query.  Here's a variant of his method that picks the Country from a List of the Page split up by "/", if the length of the first split is 2:

AddedCountry = Table.AddColumn(#"YOUR LAST QUERY STEP", "Country", 
     each if Text.Length(Text.Split([Page],"/"){1}) = 2 then 
             Text.Split([Page],"/"){1} 
          else null 
     )

[austinsense]

This will make life a little better using SWITCH()/TRUE() ... http://www.powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

Copy/paste a sample of that column into here and people can offer some better advice on how to extract the countries.