Creating a report using JSON formatted data with Power BI - Part 1

Power BI supports retrieving data from a variety of data sources that includes data in JSON format.

In the post you will learn how to retrieve JSON data from SQL Server 2016. You then retrieve this data into Power BI to create a report.

Creating a report requires massaging the JSON data retrieved using the Power BI user interface.

Creating JSON formatted data:

If you want to know how to retrieve JSON formatted data from your database in SQL Server go here:
http://hodentekhelp.blogspot.com/2016/11/how-do-you-retrieve-json-formatted-data.html

Here is the data that is going to be used in this post. The data is from a query against the pubs database (original compatibility 100) whose compatibility is changed to work with SQL Server 2016.

JsonPowerBI_00

You can run the query and save it as a file. Make sure there are neither extraneous characters in the file (like some sql message) nor does it have white spaces in side the JSON data.

Getting JSON Data into Power BI:

It is very easy to get the JSON data into Power BI. Just use the Get Data menu item in Power BI.

If you want to know details of how to retrieve JSON formatted data into Power Bi go here:
http://hodentekmsss.blogspot.com/2016/11/retrieving-json-data-in-power-bi.html

The data that was created in SQL Server is now retrieved into Power BI.

This is the first step. The data comes into Power BI as a list.
============

JsonPowerBI_01

 This is the formula behind this.
============
Json.Document(File.Contents("C:\Users\Jayaram\Documents\Blog2016\HodentekMSSS2016\Pubs11_20.json"))
==========
You can convert this list into a table using the menu ribbon item To Table in Convert.
After this the UI appears as shown with the list converted to a table.

JsonPowerBI_02.PNG

This is the formula behind this conversion:
=======
= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
=========
When you click on a single  record this is what you see.
======

JsonPowerBI_03.PNG

The first record has three items shown at the bottom.

Now you can expand the record by clicking on the icon shown on the column 1 to split it into the items by continuously clicking it.

First cancel the dialogue that showed up earlier. Next click on Column1 and then click the icon to expand the column.

This is what happens the first click.
=======

JsonPowerBI_04.PNG
====

After clicking OK, this is what you will see in the Power BI UI.

JsonPowerBI_05
The formula behind the above is as shown here:
========
= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"qty", "title_id", "titles"}, {"Column1.qty", "Column1.title_id", "Column1.titles"})
============
As you can see that Column1.titles is still a list (and therefore can be expanded).
 ======
This can be further split by clicking on the icon in Column1.titles.

When you click this icon, you will see the following change.
You will observe that the list goes over into Record and the following is displayed.

JsonPowerBI_06

Click OK.
The display changes to this.

JsonPowerBI_07

Observe that there are no more columns ot be expanded.
The formula now appears as shown:
=============
= Table.ExpandRecordColumn(#"Expanded Column1.titles2", "Column1.titles", {"Amount", "ytd_sales"}, {"Column1.titles.Amount", "Column1.titles.ytd_sales"})
=============

Renaming the columns

You can right click on a column and from the drop-down menu rename it. The renamed columns appear like this as shown below. while renaming a column, you may get a message Insert Step. Just click Insert for the message and go to the next column.

JsonPowerBI_08

The formula behind the above is this:
==============
= Table.RenameColumns(#"Expanded Column1.titles3",{{"Column1.qty", "qty"}, {"Column1.title_id", "title_id"}, {"Column1.titles.Amount", "titles.Amount"}, {"Column1.titles.ytd_sales", "titles.ytd_sales"}})
============
The various steps to get to this stage is shown here:

JsonPowerBI_09

Now we have prepared the query and ready to create a report.

Continued in Part 2 here:
Creating a report using JSON formatted data with Power BI - Part 2