Search Contents of String, Group Similar Results, and Sum Results

[KJoy]

Hello,

I am working with a large number of rows of URL page paths and their corresponding number of sessions. What I would like to be able to do is search for a specific part of the URL page path, group all of the results together, and calculate the sum of the sessions for those results.

I'd like to figure out how to do this with DAX and M.

Page URL	Sessions			Grouped Page URL	Sessions
/new-snowboard-23jk4j	118			/new-snowboard	235
/used-snowboard-3jfjk3y	67			/used-snowboard	148
/new-snowboard-54d51	22			/videos	54
/videos	54			/support	40
/used-snowboard-34d78c	81
/support	40
/new-snowboard-a351v2cc	95

On the left is an example of the type of data I have. On the right is what I'd like to accomplish.

Thanks!

[chuck_stones]

Similarly to the OP, I am trying to use Power BI to pull SharePoint site usage information from Google Analytics. 

I have a number of top level sites, with sub-sites, and in some cases more sub-sites, and pages under those sub sites. The hierarchyis something like: 

Host URL: 

https://companyname.sharepoint.com

Sites: 

/sites/sitename

/sites/sitename/subsite1/subsite2

/teams/teamname

/search/pages

etc. 

What I'd like to do is to group the pages by common Site/sub-site and sum session counts for each site/subsite using PowerBI to see, overall which sites have the most sessions, hits, bounce rates etc. 

I'm sure there is a way of doing it, but I'm a PowerBI novice and I don't really know where to start. I saw some of the responses here and figured they may be useful in my scenario, but I don't know how to put them into practice. 

I figure if I'm going to be using a delimiter, it would need to be on the '/'.

Any suggestions? 

[chuck_stones]

OK, so I achieved what I wanted to do by creating a custom column and using the following formula: 

=Text.BeforeDelimiter(Text.Lower([ColumnName]), "/", 3) 

This gave me a column by which I could group the sites & subsites. Bonza. However I still have a problem which is that for some reason, Google Analyics captures the site home page as two separate values, depending on how it is accessed, either; 

http://companyname.sharepoint.com/sites/sitename 

or 

http://companyname.sharepoint.com/sites/sitename/ 

This is creating two separate rows in my dataset.

Is there any way in which I can combine these two rows into one? I tried replace values, but that affects all the rows, thereby turning 

http://companyname.sharepoint.com/sites/sitename/subsite1

into 

http://companyname.sharepoint.com/sites/sitenamesubsite1

or 

http://companyname.sharepoint.com/sites/sitename/subsite1

into 

http://companyname.sharepoint.com/sites/sitename//subsite1

depending on which value I use as the source for the replace. Anyone got any genius ideas on how I can combine the two rows? 

[chuck_stones]

Similarly to the OP, I'm attempting to use Power BI to extract data from Google Analytics and look at grouping URLs by the begining of the URL. For example, our domain is: 

http://companyname.sharepoint.com and all the subsequent URLs follow a similar format to the following: 

/sites/sitename

/teams/teamname

/teams/teamname/department

/search/pages/results.aspx

etc. 

What I want to do is identify a way to sum the session counts for all the pages within each top level site and the sub sites. I figure this is possible, but being new to PowerBI I have no idea how this would be done. Any suggestions? Any use of delimiter would need to be on '/'. 

[KJoy]

@ImkeF that file helped a lot! Thanks!

I have a quick question that I'm having a problem with. To preface, I'm doing everything in Power BI Desktop. When I'm editing the queries, my pageviews shows the actual value for the page. However, when I close out of that and I am operating in the Data tab, my pageviews all become a value of 145. I have no idea why this is going on.

[ImkeF]

Clueless again 🙂

Check out my pbix

[KJoy]

Having issues with the reply function. In any case..

@ImkeF I've created a new Query and implemented the code that you created, however, what it did was duplicate my existing Query. Nothing grouped up as was expected. Any ideas on what I might have done wrong?

Thanks!

[ImkeF]

@KJoy

No ideas - have a look at the file.

[KJoy]

Thank you for the quick and helpful responses.

I will try out the suggestions here and let you all know if I have any further questions.

[ImkeF · Accepted solution]

If the pattern to create the grouped page URL is to skip the last element after the "-", you could use this M-code:

let

   Source = … YourSource …,

   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sessions", type number}}),

   #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Page URL",Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true),{"Group", "Page URL.2"}),

   #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Page URL.2"}),

   #"Grouped Rows" = Table.Group(#"Removed Columns", {"Group"}, {{"Sessions", each List.Sum([Sessions]), type text}})

in

   #"Grouped Rows"

You have to replace ... YourSource .. by what it says 🙂

[greggyb]

Very strange error going on with my post above - it is un-editable. There's a strange rendering glitch when I go to edit it.

Nevertheless, it is probably best to use the Power Query solution @ImkeF or something similar to do the URL text-munging. This is just standard data cleanup.

Rather than persist a sum as a field in the table, though, I'd recommend writing a measure that counts the instances of the URL stem. It would be as simple as a COUNTA():

CountURL = 
COUNTA( TestURL[URL] )

[Greg_Deckler]

A little ugly, but you could create a number of columns equal to your groups and use IFERROR and SEARCH (or FIND) to return the starting position or -1 if not found. You could then have another column where you have a big IF statement and put the right "friendly" name into the column based upon which of your grouping columns are not -1. Brute force, probably a slicker way to do it, but should work.