Filter URL in column using regex & R script

[Anonymous]

Hi everyone,

I'm trying to create a regex expression that only select web URLs with 3 or more non-consecutive forward slash and nothing has worked so far. All these URLs begin with "page.com/language (en or fr)/content_type". Here are examples of the desired results and the unwanted results:

Desired result: page.com/language (en or fr)/content_type/category/subcategory/year/content

Unwanted results: page.com/language (en or fr)/content_type/category/ | page.com/language (en or fr)/content_type/category/subcategory/ | page.com/language (en or fr)/content_type/category/year

Notice the desired results only contain the content pages... the desired results have 3 non-consecutive forward slash after the "page.com/language/content_type" portion...

Note: many pages have "page.com/language (en or fr)/content_type/category/year/content" as the URL format. Not all pages have a "/subcategory" component.

Any help from you or the community is much appreciated!

[Anonymous]

Hi @smpa01 , thank you for your advice. I'm good with using Javascript if I can use Google Analytics and SQL as the data sources, since I need to parse URL from multiple web pages (same domain, different sites) that follow the above format I have mentioned above. I did read through your posted articles but find it hard to follow the steps outlined.

[Anonymous]

Thank you @smpa01 ! The format of this URL column in a table imported from Google Analytics. I'm also not familiar with JSON and just need the regex syntax so I can put this into R script.

[smpa01]

@Anonymous  I meant js(javascript) and not JSON. Since I found out about the native capacity, I have not been using stringR anymore. I am sorry, someone else will help you.

[smpa01]

@Anonymous  js and regex can be natively written in PQ now. 

https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-extraction/bc-p/2185482#M3126

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p/2132960

In this case, you only want the string that has a total forwardslash count=6 which you can write by writing js and combining regex in it

var x='"&input&"';
var b = (x.match(/\//gm)).length;
document.write(b)

The full code

let
   fx=(input)=>
    Web.Page(
        "<script>
            var x='"&input&"';
            var b = (x.match(/\//gm)).length;
           document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text],
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkhMT9VLzs/Vz0nMSy8FchQ0UvMU8osU0oo09ZPz80pS80riSyoLUvWTE0tS0/OLKvWLS5Pg7MrUxCKYMqVYHbLMI1cfsjvINQPkfqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each (fx([Column1]) = "6"))
in
    #"Filtered Rows"