Dynamic parameter value

[Anonymous]

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

[GilbertQ]

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.

[Anonymous]

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 · Accepted solution]

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]

@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]

@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]

@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.

[Anonymous]

Hello @nikjft

let
    Source = GoogleAnalytics.Accounts(),
    #"AccountID" = Source{[Id="AccountID"]}[Data],
    
    #"PropertyID1" = #"AccountID"{[Id="PropertyID1"]}[Data],
    #"ViewID1" = #"PropertyID1"{[Id="ViewID1"]}[Data],

    #"PropertyID2" = #"AccountID"{[Id="PropertyID2"]}[Data],
    #"ViewID2" = #"PropertyID2"{[Id="ViewID2"]}[Data],
   
    AddedItems =  Cube.Transform(
        #"ViewID1",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
        } 
    ),
    
     AddedItems2 =  Cube.Transform(

        #"ViewID2", 
        {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date2"}},
            {Cube.AddMeasureColumn, "Sessions2", "ga:sessions"}
        } 
    ),
    
    TableAGrouped=Table.Group(AddedItems,{"Sessions","Date"},{}),
    TableBGrouped=Table.Group(AddedItems2,{"Sessions2", "Date2"},{}),

    ResultTable = Table.Join(
        TableAGrouped, {"Sessions","Date"},
        TableBGrouped, {"Sessions2","Date2"},JoinKind.FullOuter)
  
in
     ResultTable

Maybe something like that? Can you give this a go and let me know if it works?

Regards
George

[nikjft]

That wasn't exactly what I was looking for, but it pointed me in the right direction. Here's what I ended up doing:

I created a table with all the GA configurations I might want, same as above, including a "label" column.

I then created another calculated table that just selected from the configurations based on a parameter value that equals the label column. So that returns a table with one row.

I then reference that filtered table within the GA queries, per your example.

This saves me from having to retrieve a ton of queries and slice them. Not a great UI, but since I'm just changing a parameter, it's not too bad.

Thanks for the help!

[Paulien1989]

Hey George, great old post, but could you help me achieve this as well? It tells me that the name "xClients" wasn't recognized when I use your code. So I suppose I should enter a table name here, but I'm not sure. Could you tell me how to customize this code? I already made the three parameters.