---
title: "How to represent data in Power BI from REST APIs?"
canonical: "https://onesaitplatform.refined.site/space/DOCT/2220785678/How%20to%20represent%20data%20in%20Power%20BI%20from%20REST%20APIs%3F"
format: markdown
---
> Macro (toc)

# **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/.](https://powerbi.microsoft.com/es-es/desktop/)

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

# **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](https://lab.onesaitplatform.com/).

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

** **

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)


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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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.

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

** **

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

Which is this one:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

**Step 3: Import data from the REST API**

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

** **

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

As HTTP header, we will write

**accept: application/json**

**X-OP-APIKey: <mytoken>**

and we will have:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

# **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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

We will select **Accept**:

** **

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)


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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

We will select all of them:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

# **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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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:

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)

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

![image](media://4cdbefa7-08a9-4c74-bec1-9a4ebb8f5546)