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

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.

Power BI

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.

Power BI

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

Power BI

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

Power BI

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.

Power BI

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

Power BI

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.

Power BI

Select the highlighted column mark given below.

Power BI

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’.

Power BI

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

Power BI

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.

Power BI

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

Power BI

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.

Power BI

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

Power BI

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

Power BI

Generate Business Intelligence Reports

Head back to the report designer by clicking the icon.

Power BI

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

Power BI

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.

Power BI

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

Power BI

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

Power BI

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.

Power BI

This will publish the report to Power BI Online.

Power BI

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

Power BI

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.