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.
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 Team Site.
- Navigate to Integrations → Query Manager.
- 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.