Sunday, November 20, 2016

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:

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.


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:

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.

 This is the formula behind this.
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.


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.


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.


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

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.


Click OK.
The display changes to this.


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.


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:


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