How to Import Data from JSON Files and Rest APIs into Power BI

by Ben Richardson

In this article, you will see how to import data from JSON files and Power BI Rest API into the Power BI environment.

Power BI is a Microsoft application for data analytics and visualization. With Power BI, you can create interactive data visualizations without writing a single line of code. To create a visualization in Power BI, you need a data source that can be in the form of a flat-file such as CSV, TSV, or a database like MS SQL Server. Data can also be retrieved from online sources via Power BI Rest API calls.

In the first section of the article, we will analyze data imported via a local JSON file. In the second section, we will import data from a remote location via REST API calls and explore it with the help of Power BI visualizations.

Importing data from JSON files

JSON stands for JavaScript Object Notation and is one of the most commonly used formats for data exchange between different platforms and applications. With Power BI, you can export data from JSON files to create different types of visualization.

To import JSON files, go to the Power BI dashboard and click the “Get Data” tab from the top menu. From the list of data sources, select “JSON” as shown in the following screenshot.

Connecting to a JSON data source

Locate the JSON file that contains your dataset and import it into the Power BI desktop. The JSON file that we are going to import contains 100 fictional records of different persons. The first five records in our JSON file look like this:

You can see that each person has four properties, i.e., “Person_Id”, “Person_Name”, “Person_Age”, and “Person_Gender”. With Power BI, these properties will be converted into columns.

Once the above JSON file is imported, you should see a list with 100 records, as shown in the following screenshot.

Screenshot once data in the JSON file has been imported.

If you click on any record, you will see the details of one person, as shown below. You can see the values for the “Person_Id”, “Person_Name”, “Person_Age”, and “Person_Gender” columns.

Screenshot showing the data imported for each record.

To revert to the previous window, click the “X Navigation” button.

Screenshot showing how to navigate to previous screen by clicking 'X'

To convert the List into the table, click the “To Table” button from the top menu, as shown in the following screenshot.

Screenshot showing where the 'To Table' button is to create a table.

Your list will be converted into a table with 1 column. The column contains one record per person, and each record contains 4 values.

Image of how the data will look when converted into a one column table.

To create 4 columns, click on the “” button at the top right corner of the column name as shown in the following figure and click the “OK” button.

Image showing how to convert the one column table into a four column table.

You JSON will be converted into a table, as shown below. You can see the four column names that correspond to the properties in JSON files.

Image of the data in a four column format after conversion.

To create a visualization in the form of a report, you will need to save this table.

To do so, click the “Close & Apply” button, as shown below. Once you have done that, you will be taken to the Reports View.

Image showing where the 'Close & Apply' button is.

From the reports view, select the “Clustered column chart”, as shown in the “Visualization” field below. Add the “Person Gender” column to the Axis and “Person Age” column to the Value field.

Setting up the data visualisation in the reports view.

A clustered column chart, such as the one shown below, will be created. The chart shows that the number of persons with gender id 0 is higher than the ones with gender id 1.

A simple graph showing distribution of men and women in the data.

Importing with Power BI Rest API Calls

With Power BI Rest API calls, you can import data from websites that provide restful APIs for data access. Once the data is imported, you can treat it like any other data file such as JSON or CSV, etc.

In this article, we will be importing information about the 8 different houses from the TV Show Game of Thrones. The data is hosted here, https://anapioficeandfire.com/. The website provides an API to access books, characters, and houses from the novel series: A Song Of Ice And Fire.

If you click this link, the below will appear in your browser window.

Screenshot showing data available when clicking the link to the power bi rest api at  anapioficeandfire.com

To get data via Power BI Rest API calls, you have to specify “Web” as the data source in the “Get Data” menu, as shown below. Click the “Connect” button.

Image of the correct settings to download data from the website of the power bi rest api

In the dialogue box that appears, specify https://anapioficeandfire.com/api/ in the first part and “houses/” in the second part. The final URL will be https://anapioficeandfire.com/api/houses/. Click the OK button.

Image showing the final setting required to down load the dataset using the power bi rest api

The dataset will be loaded in Power BI Query editor in the form of a JSON file having 10 records.

Each record contains information about one of the houses in the novel series, as shown below:

The data as it appears when first uploaded.

The rest of the process is similar to what you did in order to expand data imported via JSON files above.

Click on the “To Table” option from the menu bar. The dataset will be converted to a table.

Again, extend the column by clicking the “<->” symbol at the top of the column name as shown below:

Clicking the <-> symbol to extend the column of data

You will see all the column names, as shown below. Click the OK button.

The screen when all column names are visible.

The column names will be extended. If you look at the “titles” and “seats” columns, you will see that the columns are lists. Expand the columns in the same way that you expanded the original list column by clicking the “<->” button at the top right of the column name as shown below:

Expanding the columns again using <->

Once you expand both the “title” and the “seats” column, the dataset should look like this:

The dataset once the 'title' and 'seats' columns have been expanded.

You can see that duplicate records have been added when you expand the “title” and “seats” column. This is because one house can have multiple titles and seats. Therefore, for each title and seat, a new row is added, resulting in duplicate rows for house names.

Click on the “Close & Apply” button from the top menu to open this dataset in the Query window.

The final step is to create a simple visualization using this data. We will create a simple pie chart that will show the distribution of ancestral weapons by region.

To do this, start by clicking on the “pie” chart option from visualizations, as shown below. In the “Legend” field, add “region” and in the values, field add “Count of ancestral Weapons”, as shown in the following screenshot.

Setting up the simple pie chart.

The following Pie chart will be created as a result. The pie chart shows that almost 50% of the ancestral weapons belong to the region “The Vale”. Similarly, 11.1% of weapons belong to each of the “The Reach” and “The North” region.

Image of the final pie chart.

In the same way, you can try and add other visualizations to further explore the data.

Conclusion

Power BI can import data from a variety of data sources such as CSV, TSV, JSON files, online flat files, databases, and via power BI REST API calls. In this article, we looked at how to import data via the JSON files and how to use Power BI Rest API calls to import data from websites that provide support for REST APIs. With JSON files, data is imported in the form of records, which you have to first convert to a table. Then you have to expand the table containing JSON records to create one column for each property in a record. With power BI REST APIs, you can import data from a remote location in the form of a JSON file and then explore the data just as you would a local JSON file.

Ref: