Forum Discussion
Google Analytics connector missing startdate/enddate parameters
Thank you for the above code. This works to circumvent the sampling issue!
I use a code like this:
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
This works, but for performance reasons and scalability I'm looking for an improvement over this as I now query again and again also the historical data. Ideally I would like to create a combined datasource that contains all data up till and including last month. And then have a seperate connection for the data for this month. And combine the two. That way I don't have to send all seperate monthly requests for the historic data anymore, which saves on GA requests. That historic datasource should of course update as soon as a new month starts, so for instance when August starts my historic dataset should automatically contain data up till and inclusing July 2018 (as I aim to avoid manual tasks).
Is this possible in the query editor? Anybody any ideas?
Hi all,
I just wanted to see if there has been any updates on this topic or if anyone found a way around this?
I am getting the correct figures by applying the above mentioned steps for date and using collapse however I have to filter my data which contains a model name in all the page paths. This works really well on the GA query builder but when I add the "contains" filter in Power BI I don't get the correct visitors numbers. It would allow me to collapse and remove as it says that it is not a cube.
- artemmuntianu7 years agoFrequent Visitor
Hi, all
It seems, first you should check are actual GA requests which PBI do.
It is possible to see them in log files.
Turn logging on -> https://docs.microsoft.com/en-us/power-bi/desktop-troubleshooting-sign-in -> refresh your report -> navigate to the Traces folder on your local computer. Search for text like "https://www.googleapis.com/analytics/v3/data/ga". It will be somewhere in log files.
Compare requests URI with expected from GA query builder. If they are the same, your issue does not relate to GA Connector.