Creating a new column from existing column with applied text filter

[MTME]

Hi,

I'm trying to make a model that shows website traffic across all language versions of our website, using the Google Analytics conncetor, but the traffic data that I get in from Google Analytics contain both our global site's page data, as well as the various languages pages in the same dataset. 

So, what I was thinking to do was have one column that contains all data, and then calculate new columns for each subdomain, so that they can be discerned from each other in models. 

However, I'm not sure how to achieve this.

[MTME · Accepted solution]

I was able to find a solution that could somewhat do the same as what I was looking for. By creating a conditional column based on the original Page column, I could create a column which would replace any URL with for example /de to DE as a text string, and the same for /fr pages to FR, and so on.

By doing this I now have a column with all DE, FR, ES, etc. that I can use to filter for all pages related to their individual subdomain.

[amitchandak]

@MTME ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MTME]

@amitchandak I have created a table here with some sample data:

Page	Sessions	Date
/	10	12-06-2019
/test	3	16-07-2019
/test/example	1	16-07-2019
/de	5	12-06-2019
/de/example	2	14-06-2019
/fr	6	12-06-2019
/fr/example	2	16-07-2019

What we have here is some pages across different language domains (i.e. /fr and /de), but if possible I would like to create new columns or measures that filter so that it only shows for example the pages that begin with/contain /de
So, that I can create models that shows for example sessions from only the German (/de) website

[v-lili6-msft]

hi @MTME 

You may use Extract in edit queries to get it as below:

https://www.wiseowl.co.uk/blog/s2535/text-between-delimiters.htm

Regards,

Lin