I recently finished a project that introduced me to PowerBI. It’s a great piece of software—intuitive, powerful and fun to use. It’s inspired me to start exploring other Business Intelligence technologies like Tableau and DataStudio.
However I did run into one problem that took a lot of googling and some youtube video watching to solve.
Connecting PowerBI to Google Analytics
Connecting to GA is simple. You only need to select ‘Google Analytics’ as your data source from the list, enter your credentials and then you can import whatever data you like. The problem is that it can be excruciatingly slow.
By default, GA will import all of your history. If you have a large website with lots of visitors that has been up for years, this will take a very long time. My first time importing data I waited around 30 minutes in the “Applying changes” step.
A first instinct would be to then filter the data that GA imports at the API level. However, there are no options in the import data GUI to filter based on date ranges, page name or any features at all. It took around a day suffering though the tedious imports before I got fed up and scoured the internet for a solution. It was hidden well, in a Youtube video by Curbal.
It turns out there is a way to apply these filters to the Google Analytics API connector in the PowerBI Advanced Query editor. It all depends on one icon.
See here in the top left corner the icon is solid yellow.
Let’s say I apply a filter to check if a string contains a substring. You’ll notice that they yellow icon has changed to white table.
This means that the filter is being applied to the data after it has been imported into PowerBI.
We can leverage this icon to filter the data at the API level by testing the different filter options and seeing how the icon changes. For example, if you apply a filter for dates within a range, the icon remains yellow.
Applying these types of API level filter brought my loading times down from 20/30 minutes to ~1 minute.
String equality will work as well:
It was a matter of trial and error, but it drastically reduced loading times and made the experience of working with Google Analytics in PowerBI much more bearable.
By playing around with different filters, you can check the icon to get a sense of which will work. Generally, equality checks and date ranges filter at the API level. Filtering out for nulls, empty strings and checking that a string contains something will only work on imported data.
If you apply any intermediate steps that break the API connection any following API-level filters will not apply. For example, if you remove nulls and then filter for a specific date, the date range operation is invoked on the entire data set.
Maybe this would have been obvious to someone who knows PowerBI intimately, but as a newcomer this trick saved many hours. Here’s to hoping this blog post helps someone else! Thanks again to Curbal on Youtube for teaching me this trick.