TL;DR
This guide shows how to bring data into Power BI from platforms that lack a native connector, using their API instead. With a Shopify example, it walks through Power BI’s Web connector, Basic Authentication setup, and transforming API responses into a usable table. A practical option whenever your data source isn’t on Power BI’s built-in connector list.
Power BI is used to develop reports based on data coming from various platforms. Though Power BI has its own list of connectors (which is increasing every month), there may come a case where the platform you are looking to connect to may not have a connector in Power BI.
In such cases, one option is to connect to the platform using its API’s.
A lot of services offer API as an option to fetch its data in a third-party application. One such service is Shopify. Currently, as Power BI doesn’t have a connector available for Shopify, we decided to use it as an example to show how an API can be used to bring data into Power BI data integration.
The method described in this blog can be used to connect API’s of any service as long as they support Basic Authentication, API Key or if the API can be accessed by a Username & Password.
What is Shopify?
Shopify is an e-commerce platform for online stores and retail point-of-sale systems. Shopify offers online retailers a suite of services including payments, marketing, shipping and customer engagement tools to simplify the process of running an online store for small merchants.
Though Shopify has its own dashboard for quick analysis of your orders or transactions, you may still want to analyse its data in Power BI to take advantage of its long list of features & the capability to provide useful insights.
Shopify has an API documentation where you can find its list of available endpoints here: Shopify API documentation.
Manual API connections get messy fast, especially with pagination or token refreshes. Let our Data Analytics team build a reliable integration for you. Talk to our experts →
Pre-requisites
Typically to access any API we need its endpoint and either an API key, access token or username-password.
For this example, we have
Base URL: https://sample.myshopify.com/admin/api/
Endpoint: https://sample.myshopify.com/admin/api/2020-04/orders.json?limit=250
Auth key: Basic UzNmNGE4ODc5YTVkYiNzMwOWMyA4NjU6c2h
You can obtain these information’s by creating a private app in Shopify. You can also use a public app if you have multiple accounts. Make sure you are able to access the API without any issues before getting started in Power BI.
Connect Shopify to Power BI
Step 1: Open Power BI desktop, from “Home” ribbon click on “Get Data” & select “Web”.
Step 2: Enter URL and Configure Advanced Options
In the dialog box Select “Advanced”. Add the URL, preferably in parts. You can add HTTP request headers according to the API’s requirements & also specify the authorization key.
Kindly don’t enter the authorization key if you are going to use Username-Password or API key to access the API.
Step 3: Handle the Authentication Window
If in case an authentication window pops up, select “Anonymous”. If you have not entered the authorization key in the previous step, you may select “API” to enter API key or select “Basic” to enter username & password.
Step 4: Click on List
Step 5: Transform to Table
Step 6: Select Columns
Click on the arrow button next to the column header. Select the list of columns you want to bring from the data. Then click on OK.
This is how the Applied steps will appear on the right once you complete the above steps:
Step 7: Close & Apply
No native connector for your platform? We’ve built custom API integrations across dozens of businesses. Explore our Data Analytics services →
Conculsion
With the above steps, you can connect to any desired platform using its API. In this blog, I have not covered how you can tackle pagination in Power BI. You can wait for my blog on “How to call API in Power BI recursively”.
There is one authentication method that cannot be tackled directly in Power BI, which is the OAuth2 authentication method. If your platform does not support any method other than OAuth2, then a Custom Connector must be built.
Let us know if you want a blog on how to build a custom connector for Power Bi Consulting Services.
Frequently Asked Questions
Common questions about connecting Power BI to APIs using Basic Authentication, API keys, and OAuth2.
From Basic Auth to OAuth2, we handle the technical setup so you get clean, reliable dashboards. Get a free consultation →
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
