Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
im new to power query ,
I Am currently trying to load the User dimension from Google analitics to power bi with the Date dimension ,
my problem is in the power query editor with the below query the user column has 0 values for all rows
= Cube.Transform(#"123456", { {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Users", "ga:users"} })
but if i filter the date column i get a value in the user column like below
#"Elementos agregados" = Cube.Transform(#"123456", { {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}}, {Cube.AddMeasureColumn, "Users", "ga:users"} } ), #"Sorted Rows"= Table.Sort(#"Elementos agregados",{{"Date", Order.Descending}}), #"Filter Rows" = Table.SelectRows(#"Sorted Rows", each ([Date] = #date(2019, 9, 11))) in #"Filter Rows"
granted i only get one value . for the specific date
How can i get the user count rows based on date
Solved! Go to Solution.
i think i figured it out there is no data from 2015 so it gives 0
if i add the filter below i get the column users populated and with this i can create the measures users 7 days etc
= Table.SelectRows(#"Sorted Rows", each ([Date] >= #date(2016, 09, 01)))
@anwar_b_uk Note, when using Users metric don't roll-up/sum over dates as this will give you wrong count of users. The Users mertric is a non-aggregatable..
If you need the User Dimension (as you write) so you can caltulate the correct Users metric you can take a look at https://scitylana.com/product
This service gives you the full dataset from GA (like Google Analytics 360) so you can count the distinct Users and even cross it with other data sets on, Client ID, Transaction ID, Geo Location, Time etc and filter any dimension while remaining correct counts.
And you don't have to worry about precision since data-sampling is removed too.
i think i figured it out there is no data from 2015 so it gives 0
if i add the filter below i get the column users populated and with this i can create the measures users 7 days etc
= Table.SelectRows(#"Sorted Rows", each ([Date] >= #date(2016, 09, 01)))