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.