Note
Supermetrics offers two ways of pulling data to Power BI: via API (a weblink) and with the help of a custom connector that offers an interface and lists your queries.
This guide applies to connecting your data to Power BI using the Supermetrics API weblink. To use our Power BI connector, see our guide on installing the Supermetrics connector for Power BI.
Using the Supermetrics Query Manager, you can easily create queries and connect your data from various data sources to Power BI via the Supermetrics API. Once you're set up, you can schedule refreshes for your data in Power BI.
We recommend using the KEY-JSON output format. Follow these steps to turn KEY-JSON data into a table in Power BI.
Before you begin
See Power BI’s documentation to learn more about connecting to a data source.
Instructions
- Log in to the Supermetrics Hub.
- In the sidebar, click API.
- Select for Supermetrics API from the dropdown menu next to the page title.
- Click Select data source.
- Select a data source and connection to use.
- In the sidebar on the left, build your query. Click Run to test it and review your data in the preview.
- From the URL format options, select K-JSON.
- Copy the K-JSON URL.
The query URL includes an API key that gives anyone with the URL access to your data. Treat this as you would a password and keep it safe. If you need to share the URL publicly, use the Short URL option that does not display your API key.
- Open your PowerBI workbook.
- Navigate to Home → Get data.
- Search for and select Web, then click Connect.
- In the From Web view, select the Basic setting, and paste the query URL you copied from the Query Manager into the URL field. Click OK.
- Give your data source a memorable name.
- Click Close & Apply in the top menu bar to complete the setup.
Convert KEY-JSON to a table in Power BI
These instructions are based on these steps in Stack Overflow.
- Define the KEYJSON output format in the URL you generated in the Query Manager. Replace the value after /data/ with keyjson.
For example, a URL ending in /query/data/powerbi would become a URL ending in /query/data/keyjson. - Open the Power BI query editor.
- Convert the list "to table".
- Click the expand column icon for "Column1".
- You'll see a popup that lists every column in your query.
- Select your columns and click OK. Unclick the Use original column name as prefix option to avoid having "Column1" as a prefix in every column.
- Select data types for your columns. Power BI interprets all columns as "Text" by default.
- Click Close & Apply.