Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone,
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.
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.
@sun-and-sea 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.
@sun-and-sea js and regex can be natively written in PQ now.
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"