This tutorial will discuss how to read data from API with Power BI. This will be demonstrated step-by-step by bringing in your data and representing it in a tabular form before plotting your visualization. Before going into that, let’s discuss the API and how it works.

Application Programming Interface is a server we can use to retrieve and send data using a code, or we can also say it’s a set of programming code that enables data transmission between one software product and another. An API can either be Private or Public(Open). When an API is private, it is only accessible to Developers and users within an organization. Also, when an API is public, external Developers or users have easy access to information available. Good examples of public APIs are;

The Google Map API, News API, Weather Forecast API, and lots more.

Also, it is excellent to know that API servers send in their responses in JSON format which means you must know to handle your data and extract them neatly.

HTTP Methods

In Knowing what actions to perform on the Web Service’s resources, the REST APIs usually listen for HTTP methods which are GET, POST, and DELETE. This HTTP method instructs the API on how to handle the resource.

Now that we have a good knowledge of what an API is, we can go move further to discuss how we can connect to API with Power BI.

Connect to API with Power BI

We will use the Open Air Quality (OpenAQ) Api in our Power BI. This API can be found here, and we will use the countries endpoint.

Let’s follow the steps below to see how to connect with API in Power BI.

Step 1

Open your Power BI Desktop and click Get Data → Web as shown below;

Step 2:

After you’ve selected the Web, a new window will come up. Here you will see two radio buttons (Basic and Advanced Option). You will select the Advanced button and configure the task.

In the URL parts, put the complete URL of the API you want to work with. You can also add a command timeout and a Request header. These are both optional, though. After you’ve done that, click on OK.

Step 3

After you’ve selected OK, it opens a new page which is more of an anonymous authentication for connecting. Select the endpoint you want, and click on Connect to bring your data in.

Step 4

Your data should be in tabular form, as shown below. For cases whereby your data isn’t in the proper format, you can use Power BI Query Editor to transform your data into your desired format.

And that’s how to read data from API with Power BI. Hope you found it helpful; let’s know in the comments section below. Thanks for reading.