Generate Power BI Reports From Data In JSON File

Power BI is a Cloud based Service, which provides data visualization options, which are based on the source data. Using Power BI, we can create dashboards, which are based on the data. While using Power BI, 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 Power BI desktop version here.

Power BI has the flexibility to consume numerous data sources and generate the business intelligence reports from the source data. In this article, we will see how to consume JSON data from a JSON file within Power BI and create the reports, using the data. JSON file, which we will be using contains the Q1-Q4 sales data of the cars, as shown below.

Connect to data source

In order to derive business intelligence reports, we will have to first connect to the data source. Click Get data to connect to JSON file, which has the source data.

This will open up the Get Data Window, select JSON option and click Connect.

Browse JSON file, which we will be used as the data source.

On clicking Open, the JSON data will be loaded to the Query Editor Window. The loaded data will be present as a single row, which represents JSON root node. Click Record to drill down to the list of records.

Further, drill down to the individual records by clicking the List.

Convert JSON to table data

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

Select the highlighted column mark given below.

It will give us the option to select which column should be a 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’.

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

Let’s save the table by clicking Close and Apply in the Query Editor. This will enable us to generate the reports from the table, using the available templates.

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

Format the data

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

Let’s convert it to a whole number, so that we can use it to plot a chart. 

Click 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 the icon.

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

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

This will result in the Power BI bar chart, as shown below. Hovering over the bars will show us their respective values.

We can also customize the existing chart by changing the color, legend and font size by selecting the option given below.

Publish to Power BI Online

We can now publish the report from Power BI Desktop to Power BI Online , provided we have a registered account with Power BI. Click Publish from Power BI Desktop designer.

This will publish the report to Power BI Online.

Heading over to Power BI online, we can view the report, as shown below.

JSON file was used to create the report given above, which is attached with this article. Feel free to download and work with it.

Summary

Thus, we saw how to generate Power BI reports, which are based on the data from JSON file.