Create Power BI Reports From REST Service JSON Data

Power BI is a Cloud based Service that provides data visualization options, based on the source data. Using Power BI, we can create dashboards based on the data. In order to create the reports, make sure that you have a Power BI account. If you don’t have an account, you can sign up for it here .You can either work with Power BI online or download the Power BI Desktop version here.

Power BI has the flexibility to consume numerous data sources and generate business intelligence reports from the source data. In this article, we will see how to consume JSON data from REST Web Service within Power BI and create reports.

Consume JSON data from REST Web Service within Power BI

We will be using REST Web service to pump JSON data into Power BI which will be used to create reports. If we have a fully functional REST Service hosted in our environment, we can use it with Power BI; else we can create a mock REST Service using JSON Server that will help us work with a REST service for testing. As a bonus to the readers, we will be exploring a neat hack that helps us set up a mock REST Service using JSON Server for testing with Power BI.

JSON Server

JSON Server is built on top of Node.js and is an npm package using which we can create mock REST Services. We will be making use of a JSON file that acts as a back end database to power the REST Service. We can install and setup Node.js and npm by installing Node JS LTS version from here.

Once we have installed Node JS, we can install JSON Server by running the below command:

 npm install -g json-server

This will install JSON Server in our development environment. We can check the JSON Server version using the command:

json-server –v

Now, we can attach a JSON file that acts as the database to the JSON Server using the command -

json-server <location of the json file>

We will be making use of Product.json that has the Q1-Q4 sales data of cars.

Thus, the JSON Server has been setup up and will be running on server port 3000 and the home URL using which we can access the REST service is “http://localhost:3000”.

On accessing the home URL ,we can see the available resources which we can access as a REST endpoint. We have the ‘ProductDetails’ resource which will be accessible as “http://localhost:3000/ProductDetails”.

We can see that a successful GET will fetch us the below JSON response in the browser.

Create Power BI Reports from REST Service

Once we have a working REST Service (or a mock REST Service using JSON Server), we can consume it from Power BI and create business intelligence reports.

Head over to Power BI Desktop and select ‘Get Data’ option.

From the options, select ‘Web’ and click on Connect.

In the URL field, enter REST Service URL; in our case, we will make use of the JSON Server REST Service URL. Click on OK.

Upon clicking on OK, the JSON data will be loaded to the Query Editor window. The loaded data will be present as a single row which represents the JSON root node. Click on ‘List’ to drill down to individual records.

Convert JSON to table data

Thus, we have a record for each item in the JSON file. We can now convert this to the Power BI table by selecting the ‘To Table’ option.

Click on OK.

Select the below highlighted column mark.

It will give us the option to select which column should be part of the table. Ensure that you uncheck the check box ‘Use original column name as prefix’. If it is checked, the generated column will have the naming convention ‘Column1.NewColumnName’.

Upon clicking on OK, the table has been generated from the JSON file.

We can now save the table by clicking on ‘Close and Apply’ in the Query Editor. This will enable us to generate reports from the table using the available templates.

The fields from which we can generate the Power BI report has become available in Power BI Desktop Designer.

Format the Data

Before we can create reports, we should format the data by changing the data type of Q1-Q4 Sales data from Text to Whole number. From designer, select the table icon and select the column ‘Q1Sales’. By default it is of the data type ‘Text’.

Let’s convert it to Whole Number so that we can use it to plot a chart. 

Click on Yes to start the data type change. Repeat the data type change process for Q1-Q4 Sales column.

Generate Business Intelligence Reports

Head back to the report designer by clicking on the below icon.

Now, we can select from multiple Visualization options. Let's go with a line chart for time being.

Drag and drop ‘Product’ field to the ‘Axis’ section which will form the X axis of the chart. Drag and drop the fields Q1-Q4 Sales to Values section so that it will come up in the Y Axis of the chart.

This will result in the Power BI line chart, as shown below. It will help us derive meaningful business intelligence conclusions as it helps us to compare the values in a single frame.

We can click on the ‘Focus Mode’ button available at the top right corner to maximize the report pane for better visibility.

Summary

Thus, we saw how to  consume REST Services from Power BI and use the JSON data to create business intelligence reports.