Introduction

Microsoft Power BI is a data visualization and analysis tool that allows you to create visualizations in a friendly way, allows you to use data sources such as CSV, databases and even Rest APIs.

We will next explain:

• How to use a REST API as a data source to import JSON data into Power BI.

• How to transform JSON data into a tabular format.

• How to make a visualization using the imported data.

Step 1: Install Power BI

The first step will be to install Power BI. In the example we will use Power BI Desktop, which is free and allows us to create visualizations in a desktop environment (without publishing to the web).

I can download it from the Microsoft Store or from this url: https://powerbi.microsoft.com/es-es/desktop/.

Open

Step 2: Identify the REST API from which I will fetch the data

In the example, we will use a public API from the Onesait Platform CloudLab Environment.

If we don't have an account, we can create a user from https://lab.onesaitplatform.com/ in the SIGN UP option:

Open

Once logged, we can access our environment where, if we have not created any platform element, we will see an initial screen like this:

What we want to is to search for a REST API with information that we can paint in PowerBI. To do that, we access the DEVELOPMENT> My REST APIs option:

There we will select some of the APIs to which we have access. For example we can search the API with information about Restaurants in New York:

The first thing we will do is call the API to check what data it returns and its structure. To do that, we first go to the User Tokens option and copy our Token.

And then we go to the Swagger option of the selected API:

We see that the API only has a GET method that returns all the restaurants:

We go to the method and select Try it out. We copy our Token and press Execute:

When executing it, we will be able to see the structure of each Restaurant in the received array:

Which is this one:

Step 3: Import data from the REST API

Now we will open Power BI, and go to the option Get Data> Web

In the URL part, we will copy the URL of our REST API( https://lab.onesaitplatform.com/api-manager/server/api/v1/Restaurants%20API ), specifying Advanced Use:

As HTTP header, we will write

accept: application/json

X-OP-APIKey: <mytoken>

and we will have:

We will select Accept and Connect on the next screen.

After this, Power BI will open the Query Editor, presenting us the imported JSON from the REST API call in a table format, as we can see in the next image:

If we navigate through the slide, we can see all the fields of each JSON element:

And if we click on an element, it will give us the complete information on it:

We can see that the Restaurant.grades attribute is a List. If we click on it in the table, we access its elements:

And if we click on the Record element, the detail:

To return to the original table in the APPLIED STEPS window, we remove the last steps:

Step 4: Convert imported data to a Pivot Table

To create a visualization, it is beter to convert the imported data to a Table.

To do that, we go to APPLIED STEPS and we place ourselves in the first Origin element:

The Convert to Table option will appear, and we will select it:

We will select Accept:

Now we want our table to have several columns, for the JSON fields. To do that, we will use the button that appears on the right:

We will select all of them:

And then we'll do the same thing again on the Restaurant complex attribute:

We'll do the same for Column1.Restaurante.address and Column1.Restaurants.grades:

Now we are already seeing all the attributes of our interest... So we can click on Close And Apply:

After this, we will have the Table loaded in our model and we can see our columns:

Step 5: Create a visualization in Visualization View

Once we have transformed the data into a table with the Power BI Query Editor, we can create a visualization.

We are going to create different types of visualizations:

Let's start with the grouped column chart:

When selecting it, a visualization will be included in our palette, and we will be able to configure the fields to be visualized by dragging them to the different components. For example, in Legend, we can drag Column1.Restaurant.cuisine and in Legend, when dragging the _id field, a Count appear:

Now we'll select another component like a pie chart, in this case with ratings: