Supermetrics Excel Export Power Bi import Guide

This is how you get your Excel data into Power BI (Web)

The popular API as a Service provider Supermetrics offers several export destinations in its subscriptions. In addition to Google Sheets, you can also choose Microsoft Excel and Microsoft Power BI. If you have chosen an Excel package as your Supermetrics subscription, here you will learn how to automatically refresh your data from Google Analytics, Search Console, or Meta Business Manager and still use it in Power BI instead of Excel.

1 | Create Excel Export

Create your Supermetrics Excel Export via the Excel Add-On.

Excel Supermetrics Configuration

In this case it is a simple GA4 export including sessions for different traffic sources by date. Easy!

2 | Sharepoint storage and refresh trigger

Next, ensure that your Excel sheet is synchronized directly to your SharePoint. You can do this either via the OneDrive for Business desktop client on your local PC or via the SharePoint interface on the web.

Supermetrics Export Sharepoint

It is important that the Excel document is located within a SharePoint site. Although SharePoint uses OneDrive technology for file synchronization, it is significantly more difficult to use personal OneDrive documents in Power BI.

Next, use the “Schedule” function in the Supermetrics add-on. Create a trigger there that updates your data, for example, daily at 7:00 a.m.

Supermetrics Excel Export Zeitplan Trigger

3 | Daten in Power BI importieren

The Excel file or specific tables are then imported into Power BI using a SharePoint connection. This is done via Power Query.

Please note that authentication in Power Query on SharePoint is required. The connection steps in the Power Query Editor are as follows:

Establish a connection to the SharePoint site.

Source=Sharepoint.Files("https://company.sharepoint.com/sites/Seite", [ApiVersion=15])

Find the Excel file.

Navi=Excel.Workbook(Table.SelectRows(Source, each Text.Contains([Folder Path], "MeinFolder") and Text.Contains([Name], "data_ga4.xlsx"){0}[Content]

Open the Supermetrics (defined) table.

Table=Navi{[Item="zsupermetrics_1111", Kind="DefinedName"]}[Data]

Just like this you can use the data in the Power Query editor. Here is the complete Query expression in M: 

let
Source = Sharepoint.Files("https://company.sharepoint.com/sites/Seite", [ApiVersion=15]),
Navi = Excel.Workbook(Table.SelectRows(Source, each Text.Contains([Folder Path], "MeinFolder") and Text.Contains([Name], "data_ga4.xlsx"){0}[Content],
Table=Navi{[Item="zsupermetrics_1111", Kind="DefinedName"]}[Data]
in
Table
Tags