Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Dynamic-value-in-datatime-QueryFilter/m-p/360343#M162637
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Dynamic-value-in-datatime-QueryFilter/m-p/360343#M162637
For everyone else struggling with this...
I just wanted to add that @v-chuncz-msft solution doesn't affect the GA connector data. For more detailed explanation watch this video (solution starts at 9min).
As for me, I only needed last month's data so this formula worked like a charm:
#"Filtered Rows" = Table.SelectRows(#"Added Items", each Date.IsInPreviousNDays([Date], 31))
Also, to check if the GA data is being sampled for the desired period please use https://ga-dev-tools.appspot.com/query-explorer/
Good luck!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |