Google Analytics
Power BI is a powerful business analytics tool that allows you to connect to various data sources to create insightful reports and dashboards. One such data source is Google Analytics, which gives you access to a wealth of information about your website’s performance. In this article, we will explore how to connect to the Google Analytics data source from inside Power BI using Power Query M language code.
What is Power Query M Language Code?
Power Query M is a functional, case-sensitive language used to query and transform data. It is used in Power BI to import, transform, and shape data from various sources. Power Query M language code is used to define the steps needed to load, clean, and transform data into a format that is suitable for analysis.
Connecting to the Google Analytics Data Source
To connect to the Google Analytics data source from inside Power BI, follow these steps:
1. Open Power BI Desktop.
2. Click on the “Get Data” button on the Home tab of the ribbon.
3. Select “Google Analytics” from the list of available connectors.
4. Enter your Google Analytics login credentials.
5. Select the Google Analytics account and view you want to connect to.
6. Click on “Edit” to open the Power Query Editor.
Writing Power Query M Language Code
Once you have opened the Power Query Editor, you can start writing Power Query M language code to transform and shape the data. To connect to the Google Analytics data source, you will need to use the following code:
let
Source = GoogleAnalytics.Accounts(),
#”Navigation” = Source{[Id=”{Your View ID}”]}[Data],
#”Data” = #”Navigation”{[Id=”{Your Table ID}”]}[Data]
in
#”Data”
In this code, replace “{Your View ID}” with the View ID of the Google Analytics view you want to connect to, and “{Your Table ID}” with the Table ID of the data table you want to import. You can find these IDs in the Google Analytics interface.
The code uses the GoogleAnalytics.Accounts() function to connect to the Google Analytics data source. It then navigates to the specified view and table using the #”Navigation” and #”Data” steps.
Transforming and Shaping Data
Once you have connected to the Google Analytics data source and imported the data using Power Query M language code, you can start transforming and shaping the data to suit your needs. Power Query M language code provides a wide range of functions and operators for manipulating data, including filtering, grouping, pivoting, and merging.
For example, to filter the data to show only the sessions and pageviews for a specific date range, you can use the following code:
let
Source = GoogleAnalytics.Accounts(),
#”Navigation” = Source{[Id=”{Your View ID}”]}[Data],
#”Data” = #”Navigation”{[Id=”{Your Table ID}”]}[Data],
#”Filtered Rows” = Table.SelectRows(#”Data”, each [Date] >= #date(2020, 1, 1) and [Date] <= #date(2020, 12, 31)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Bounce Rate”, “Avg. Session Duration”, “Users”})
in
#”Removed Columns”
In this code, the #”Filtered Rows” step uses the Table.SelectRows() function to filter the data based on the date range. The #”Removed Columns” step uses the Table.RemoveColumns() function to remove the unnecessary columns.
Conclusion
Connecting to the Google Analytics data source from inside Power BI using Power Query M language code is a powerful way to access, transform, and analyze website performance data. By using the code examples and techniques outlined in this article, you can create insightful reports and dashboards that help you make informed business decisions.
