Forum Discussion
Set date range limit on google analytics query
Does anyone now how to set the date range for the Google analytics connector in Power Bi desktop.
Its a very powerful connector but when I create a query, it pulls it back all rows for all time which goes back 2.5 years.
Its nice to know I can get that if I need it, but its massive overkill for a last 30 days analysis on sales and makes for huge, slow and unwieldy data sets.
I just cant see a date range limiter in the connector, or figure it out in the advanced query editor!
Anyone got any suggestions?
Thanks in advance, Warren
When you bring the Time attribute in your query, you have the ability to right click on the time field and specify the filter condition (Before, After, etc.) after the filter has been applied you can click on a gear box next to the Filtered Rows step in the Applied Steps section on the right and tweak it if you need to.
13 Replies
When you bring the Time attribute in your query, you have the ability to right click on the time field and specify the filter condition (Before, After, etc.) after the filter has been applied you can click on a gear box next to the Filtered Rows step in the Applied Steps section on the right and tweak it if you need to.
Thanks Andre, super helpful of you!. Can I ask a couple of follow up questions for clarity.
The only place I could find what you suggested was in the edit queries section, where I went to to the column for date, clicked on the drown arrow in the 'date' column header, and filtered that way using the filter fucntion.
Was that what you mean't?
If so, does this filter the data post query, in which case my query to Google analytics still a large 'all time' one? If yes, can I chnage the actually query to Google Analytics, to speed up the query itself.
The latter is probably the larger issue for me, as the query can run for half an hour at a time.
If you edit your query, add something from the Time category and filter on that, then my understanding is that this will only bring down the unfiltered information from the service into the model, just like how it works with all other data sources. If you filter as part of the query, then only the unfiltered data comes into the model.
This is different if you didn't filter in the query itself but rather built a report after the fact and did your filtering there. In this case, your data model would have all of the data.
Hi Andre - I'm right clicking the date field and don't see the ability to apply a filter. Would it be possible for you to screen shot this and post.
Thanks
Correct, this works on helping to reduce sample sizes. Just a couple of caveats:
1. Filter dates are static and thus, you don't have the ability to specify dynamic stuff like 'last 90 days' - so you will need to update this from time-to-time
2. I have seen PowerBI reset all the datatypes and filters when adding additional additional columns to the dataset. So, after adding a new column (dimension/metric) and your report "blows-up" - go back and check the datatypes and filters.
Have you tried editing the query in the Advanced Editor. I posted a similar solution here http://community.powerbi.com/t5/Desktop/Power-Query-M-filter-data-by-Dynamic-Date-PQ-not-DAX-Start-of/m-p/55693#M22623
