Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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"
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.