Bring Data in Power BI via APIs using Basic Authentication

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: https://shopify.dev/docs

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: 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: 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: From the “Transform” ribbon, select “To Table”

Step 6: 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: Click on “Close & Apply”.

Conclusion:

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.