Forum Discussion
Dynamic parameter value
I managed to found a solution. Below is the code that made it happen. Now I can successfully pull the value of the cell based on my parameter selection. This allows me to download data from Google Analytics once the template loads. This is great if you have lots of different sites but you want to use a master template.
let Source = GoogleAnalytics.Accounts(), ChangeDataTypes = Table.TransformColumnTypes( xClients, {{"GA Account ID", Text.Type}, {"GA Property ID", Text.Type}, {"GA View ID", Text.Type}}), AccountID= ChangeDataTypes{0}[GA Account ID], PropertyID = ChangeDataTypes{0}[GA Property ID], ViewID = ChangeDataTypes{0}[GA View ID], #"GA Account" = Source{[Id=AccountID]}[Data], #"GA Property" = #"GA Account"{[Id=PropertyID]}[Data], #"GA View" = #"GA Property"{[Id=ViewID]}[Data], #"Added Items" = Cube.Transform(#"GA View", {{Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}}, {Cube.AddAndExpandDimensionColumn, "ga:country", {"ga:country"}, {"Country"}}, {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Avg. Session Duration", "ga:avgSessionDuration"}, {Cube.AddMeasureColumn, "Bounce Rate", "ga:bounceRate"}, {Cube.AddMeasureColumn, "Session Duration", "ga:sessionDuration"}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}}) in #"Added Items"
Super UserHi Anonymous
If I understand you correctly, if you had to go into the Parameters and add a new Parameter called "Google Account ID"
Then in your code you could replace where you want the Google Account ID to be like the following below?
let
Source = GoogleAnalytics.Accounts(),
#"GA Account" = Source{[Id=#"Google Account ID" & "(columnname1)"]}[Data],
#"GA Property" = #"GA Account"{[Id=#"Goolge Account ID" & "(columnname2)"]}[Data],
#"GA View" = #"GA Property"{[Id=#"Google Account ID" & "(columnname3)"]}[Data],
#"Added Items" = Cube.Transform(#"GA View", {{Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}}, {Cube.AddAndExpandDimensionColumn, "ga:country", {"ga:country"}, {"Country"}}, {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Avg. Session Duration", "ga:avgSessionDuration"}, {Cube.AddMeasureColumn, "Bounce Rate", "ga:bounceRate"}, {Cube.AddMeasureColumn, "Session Duration", "ga:sessionDuration"}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}})
in
#"Added Items"Please let me know if I have misunderstood, as I am not 100% sure that this is what you are looking for.
Hey GilbertQ,
Thank you for your reply.
What I am trying to do is to pull the IDs from a table instead of a parameter. I want to have one parameter when the templates loads to select the site, and based on my selection to filter the data of the table that has all the IDs for the Google Analytics connection.
So each of the below variables is equal to the value of their respective columns
#"GA Account ID" to be equal to 'sites'[GA Account ID]
#"GA Property ID" to be equal to 'sites'[GA Property ID]
#"GA View ID" to be equal to 'sites'[GA View ID]
I tried many different Power Query functions like
#"Account ID" = Table.FromValue(sites[GA Account ID])
But not working.
I don't know if this is possible.
Let me know if now you can understand what I am trying to achieve.
Regards
George
I managed to found a solution. Below is the code that made it happen. Now I can successfully pull the value of the cell based on my parameter selection. This allows me to download data from Google Analytics once the template loads. This is great if you have lots of different sites but you want to use a master template.
let Source = GoogleAnalytics.Accounts(), ChangeDataTypes = Table.TransformColumnTypes( xClients, {{"GA Account ID", Text.Type}, {"GA Property ID", Text.Type}, {"GA View ID", Text.Type}}), AccountID= ChangeDataTypes{0}[GA Account ID], PropertyID = ChangeDataTypes{0}[GA Property ID], ViewID = ChangeDataTypes{0}[GA View ID], #"GA Account" = Source{[Id=AccountID]}[Data], #"GA Property" = #"GA Account"{[Id=PropertyID]}[Data], #"GA View" = #"GA Property"{[Id=ViewID]}[Data], #"Added Items" = Cube.Transform(#"GA View", {{Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}}, {Cube.AddAndExpandDimensionColumn, "ga:country", {"ga:country"}, {"Country"}}, {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Avg. Session Duration", "ga:avgSessionDuration"}, {Cube.AddMeasureColumn, "Bounce Rate", "ga:bounceRate"}, {Cube.AddMeasureColumn, "Session Duration", "ga:sessionDuration"}, {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}}) in #"Added Items"Anonymous - this works great, thank you for sharing it! A quick question: If I wanted to have an ability to select from multiple sets of analytics configurations, how could I set that up?
Do you mean pulling data from different GA accounts or properties?
If this is the question, as far as I am aware, Power BI allows one single connection to GA per report. So if you want to pull data from different configurations, you need to create separate reports, one for each GA account and then create a dashboard.Does the above answer your question?
Regards
George
