Forum Discussion

kamijaja's avatar
kamijaja
New Member
7 years ago
Solved

Avoid sampling Google Analytics connector

Hi, 

 

I have read some threads about sampling data with the connector and there are some ways how to avoid it mentioned here

let
    Source = GoogleAnalytics.Accounts(),
    #"1" = Source{[Id="999"]}[Data],
    #"2" = #"1"{[Id="999"]}[Data],
    #"3" = #"2"{[Id="999"]}[Data],
    #"newCube" = Cube.Transform(#"3",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddAndExpandDimensionColumn, "ga:deviceCategory", {"ga:deviceCategory"}, {"Device Category"}},
            {Cube.AddAndExpandDimensionColumn, "ga:userAgeBracket", {"ga:userAgeBracket"}, {"Age"}},
            {Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
        }),
    combinedData = Table.Combine({
        Table.SelectRows(#"newCube", each ([Date] = #date(2017, 1, 1))),
        Table.SelectRows(#"newCube", each ([Date] = #date(2017, 1, 2))),
... {days in between} ...
        Table.SelectRows(#"newCube", each ([Date] = #date(2018, 3, 4))),
        Table.SelectRows(#"newCube", each ([Date] = #date(2018, 3, 5)))
    })
in
    combinedData

and 

let
    Source = GoogleAnalytics.Accounts(),
    #"1" = Source{[Id="999"]}[Data],
    #"2" = #"1"{[Id="999"]}[Data],
    #"3" = #"2"{[Id="999"]}[Data],
    #"Added Items" = Cube.Transform(#"3",
        {
            {Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}},
            {Cube.AddAndExpandDimensionColumn, "ga:customfield", {"ga:customfield"}, {"Customfield"}},
            {Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}},
            {Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"},
            {Cube.AddMeasureColumn, "Transactions", "ga:transactions"}
        }),

 #"combinedData" = Table.Combine({
Table.SelectRows(#"Added Items", each ([Month of Year] = "201803")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201804")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201805")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201806")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201807")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201808")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201809")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201810")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201811")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201812")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201801")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201802")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201903")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201904")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201905")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201906")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201907")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201908")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201909")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201910")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201911")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201912")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201901")),
Table.SelectRows(#"Added Items", each ([Month of Year] = "201902")),
Table.SelectRows(#"Added Items", each Text.Contains([Month of Year], "2017"))
 })
in
    combinedData

What I am trying to find is a way between these 2 codes - to find a way that I can specify e.g. year 2018 or last 12 months, it would get me data on the daily level. This part of code was really good for monthly, but trying to change it to daily level is giving me errors. I am guessing it is not text and it does not read it with Text.Contains.

 

Table.SelectRows(#"Added Items", each Text.Contains([Month of Year], "2017"))

Any ideas how to simplyfy the code for daily level? Is it also posible to have some 'unfixed' way of ranges like start 12 months ago till today?

 

Thank you!

2 Replies