Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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" ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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"
  • nikjft's avatar
    nikjft
    Regular Visitor
    8 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      nikjft

      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

      • nikjft's avatar
        nikjft
        Regular Visitor
        8 years ago

        Anonymous - using the method above, I can change some table values and pull data from another profile. As I manage a large number of analytics accounts, what I was hoping to do would be to populate that table with a row for each account/property/view, so I could then select one of those rows (via a parameter or a value in the table, maybe), then refresh the data and have it pull from the selected row. This way, I could quickly run data for multiple properties without having to maintain any duplicate reports at all.