Google Analytics query URI in power bi?

[ai52487963]

I'm querying google analytics from power bi for use with dashboarding and all that. I'm trying to look at page views for a specific page, but power bi likes to bring in all the data first THEN filter. I know the google analytics query URI can be written to filter on just the page I'm interested in, though. I'm wondering: how do I convert my power bi query to make use of that filter?

The power bi query I'm using looks like this:

let
    Source = GoogleAnalytics.Accounts(),
    #"###" = Source{[Id="###"]}[Data],
    #"UA-###-10" = #"###"{[Id="UA-###-10"]}[Data],
    #"#####" = #"UA-###-10"{[Id="#####"]}[Data],
    #"Added Items" = Cube.Transform(#"#####",
        {
            {Cube.AddAndExpandDimensionColumn, "ga: pagePath", {"ga: pagePath"}, {"Page"}},
            {Cube.AddMeasureColumn, "Pageviews", "ga: pageviews"}
        })
in
    #"Added Items"

Note that the "ga: pagePath" syntax shouldn't have a space. I put a space in there to keep the text from inserting emoticons in the code.

But this gives me *all* pages, I only want a specific one.

The google analytics URI query looks like:

https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A###&start-date=30daysAgo&end-date=yesterday...

This gives me the correct result since it's doing the filtering for the site that I want at the query level.

Any ideas on how to get the `&filters=...` part at the end of the URI to work correctly in the power bi query?

[v-yuezhe-msft · Accepted solution]

@ai52487963,

When connecting to GA data in Power BI Desktop, bring the date attribute into the query as shown in the following screenshots.


Then you can add the following code in the Advanced of your query to filter date between last 30 days and yesterday.

  #"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()),-30) and [Date] <= Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1))  
in
  #"Filtered Rows"

Regards,
Lydia