Vote for us
Our integration for Power BI is in the process of getting certified by Microsoft. Once it's ready, you'll be able to get data directly from Supermetrics within Power BI.
We’d appreciate your vote — the more votes, the sooner the integration will be available. Vote here for our integration on the Power BI idea page. Thanks for supporting us along the way.
Using the Supermetrics Query Builder, 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 left side menu, under Transform, click Query Manager (API).
- 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 Builder 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 Builder. 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.