cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnrezk
Helper I
Helper I

How to add RegEx in a filter for in Advanced Editor for Google Analytics connector

Essentially I want to do this but 5 years ago when this was asked it was not possible and I was wondering if anything has changed:

https://stackoverflow.com/questions/36373406/how-to-filter-dimension-values-when-fetching-data-from-...

 

 want to fetch data from Google Analytics With M (PowerQuery/PowerBI), but want to filter the dimension values on the fily, Let's Say my dimension is "Page" and I want "Pageviews" and "Unique Pageviews" measures, provided that the "Page" is following a regex filter (ex. ga:pagePath=~^.*?([0-9]{6,7}|mpg[0-9]{1,3}){1}\.html[/]?[^ ]*)

I could use "Table.SelectRows", but as M doesn't support regex, this filter should be passed to GA api directly. Here is what M generated for me:

    let
    Source = GoogleAnalytics.Accounts(),
    #"1234567" = Source{[Id="1234567"]}[Data],
    #"UA-987654-1" = #"1234567"{[Id="UA-52004541-1"]}[Data],
    #"11111" = #"UA-987654-1"{[Id="1234567"]}[Data],
    #"Added Items" = Cube.Transform(#"11111", {{Cube.AddAndExpandDimensionColumn, "ga:pagePath", {"ga:pagePath"}, {"Page"}}, {Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"}, {Cube.AddMeasureColumn, "Unique Pageviews", "ga:uniquePageviews"}}),
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each Text.Contains([Page], "html"))
in
    #"Filtered Rows"

 

1 REPLY 1
mahoneypat
Super User
Super User

You need to use R or Python in your query to do this.

 

Using Regular Expressions(RegEx) in Power BI | by Shivam Shukla | Medium

 

Or with javascript like shown here.

RegEx in Power BI and Power Query in Excel with Java Script – The BIccountant

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors