Forum Discussion
Dynamic parameter value
Hello all,
I have 3 parameters in place to pull data from Google Analytics once I open my template
The code looks like that
let
Source = GoogleAnalytics.Accounts(),
#"GA Account" = Source{[Id=#"GA Account ID"]}[Data],
#"GA Property" = #"GA Account"{[Id=#"GA Property ID"]}[Data],
#"GA View" = #"GA Property"{[Id=#"GA View ID"]}[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"Is it possible to pull the value of each ID dynamically? So instead of having 3 parameters to use only one that will filter the rows of a table based on the value provided. Then from that table to get the values for each ID, so the code looks like that
let
Source = GoogleAnalytics.Accounts(),
#"GA Account" = Source{[Id=tablename(columnname1)]}[Data],
#"GA Property" = #"GA Account"{[Id=tablename(columnname2)]}[Data],
#"GA View" = #"GA Property"{[Id=tablename(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"Is this possible?
Thank you in advance
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"
9 Replies
Hi 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"
