Google Analytics fetch data based on WHERE condition on a field not part of select list

[jimmymitra]

Hi Experts,

I am trying to get data in power BI from Google Analytics(GA). I have encountered a situation where GA only allows 7 dimension to be added in a GA view while taking data in power BI. But I need more than 7 dimensions as per the requirement , 

Since I can not get more than 7 dimensions , so I have found work around , I thought to get two views from GA based on User Type ( New Visistor or Returning Visitor ) and then combine them in one single view. User Type is the 8th dimension that I want .

While doing it I could easily filter the GA data in power query when the filter column( Social Netwrok) is taken in the select query itself. Below query is working fine -

let
Source = GoogleAnalytics.Accounts(),
#"10#######" = Source{[Id="10#######"]}[Data],
#"UA-10#######-1" = #"1010#######"{[Id="UA-1010#######-1"]}[Data],
#"16#######" = #"UA-1010#######-1"{[Id="1610#######"]}[Data],
#"Added Items" = Cube.Transform(#"1610#######",
{
{Cube.AddAndExpandDimensionColumn, "ga:city", {"ga:city"}, {"City"}},
{Cube.AddAndExpandDimensionColumn, "ga:country", {"ga:country"}, {"Country"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel1", {"ga:pagePathLevel1"}, {"Page path level 1"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel2", {"ga:pagePathLevel2"}, {"Page path level 2"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel3", {"ga:pagePathLevel3"}, {"Page path level 3"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:socialNetwork", {"ga:socialNetwork"}, {"Social Network"}},
{Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"},
{Cube.AddMeasureColumn, "Unique Pageviews", "ga:uniquePageviews"}
}),

#"Filtered Rows" = Table.SelectRows(#"Added Items", each ([Social Network] = "Facebook"))
in
#"Filtered Rows"

But when I try to filter the rows based on a different column  ( User Type ) which is not part of the select list , no data is being fetched. Please see the below query I am running -

let
Source = GoogleAnalytics.Accounts(),
#"10#######" = Source{[Id="10#######"]}[Data],
#"UA-10#######-1" = #"1010#######"{[Id="UA-1010#######-1"]}[Data],
#"16#######" = #"UA-1010#######-1"{[Id="1610#######"]}[Data],
#"Added Items" = Cube.Transform(#"1610#######",
{
{Cube.AddAndExpandDimensionColumn, "ga:city", {"ga:city"}, {"City"}},
{Cube.AddAndExpandDimensionColumn, "ga:country", {"ga:country"}, {"Country"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel1", {"ga:pagePathLevel1"}, {"Page path level 1"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel2", {"ga:pagePathLevel2"}, {"Page path level 2"}},
{Cube.AddAndExpandDimensionColumn, "ga:pagePathLevel3", {"ga:pagePathLevel3"}, {"Page path level 3"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:socialNetwork", {"ga:socialNetwork"}, {"Social Network"}},
{Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"},
{Cube.AddMeasureColumn, "Unique Pageviews", "ga:uniquePageviews"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each ("ga:userType" = "Retruning Visitor"))
in
#"Filtered Rows"

Please can someone help me out with the correct power query where I can filter the GA dataset with a column even though the column is not part of the select query ?

Also if there is planned work around for the limitation on number of dimensions (7)
Many thanks in advance for help!!

[OlegS]

Hi @jimmymitra 

As a workaround you can import your GA data into windsor.ai account and access it with Power BI.

I have replicated your case and imported 10 columns, including userType into windsor.ai.

Windsor.ai provides you a link that you can use to import your data in Power BI. Copy the link -> Power BI -> Get data -> Web -> Paste the link.

[v-yuezhe-msft]

@jimmymitra,

When you filter rows for a column in Power BI, the column should exist in Power BI.

In your scenario, you can get data of different dimensions into different queries in Power BI, then join the queries into a single query based on proper columns.

Regards,
Lydia

[jimmymitra]

Thanks for your reply. 

Just one clarification - when you say "join the queries" , what should be the joining criteria ? For example in two views we have Date, Page Path level 1, City and Country as common columns. 

Do you suggesting us to join based on these common columns or is there a particular priamry key that could be found/used to join the to views ? 

[v-yuezhe-msft]

@jimmymitra,

You can choose proper fields in the two queries to make relationships(active or inactive).

Regards,
Lydia