Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Using Power BI with JSON Data Sources and Files


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

Can Power BI handle JSON data sources and files? Can you provide a step by step example?

Solution

One of the latest data sources now available in Power BI is JSON. JSON or Java Script Object Notation is a pretty cool way of transferring data and basically gives you the opportunity to move data from one place to another in a similar fashion to XML. As you begin to work with JSON, you will first realize that even though it is somewhat like XML, there are still some very big differences in the way that the data is actually housed and tagged within a file. In essence, JSON is focused on providing flexible, yet organized method of exchanging data. Like XML, it self describing and uses a tagging method "to tell" about the data. For a more detailed introduction to JSON, I recommend looking at the following MSSQLTips:

To get started using JSON in our Power BI Dashboard, we first need to download the latest edition of Power BI Desktop (PBID). Additionally, you need to do a few things to get everything setup for access including signing up. Note that you must use a work or school address to sign up for Power BI. Gmail, Yahoo, and similar accounts will not work. Once you have your account setup, you are ready to start exploring JSON data sources in Power BI.

JSON in Power BI

Before we jump right into the import process, let us take a look at the example file that we will be using for this tip. The JSON file, sourced from the NASDAQ stock exchange, is shown below. This file is an example of NASDAQ's data on Demand API file sets and can be downloaded at: http://www.nasdaqdod.com/Samples.aspx . They provide this data in XML, CSV, and JSON formats. As you can see in the below screen print, the file is shaped with tags for each data point, somewhat similar XML, but definitely not the same. I have highlighted some of the attributes and values; so for instance, StartTime of 06/15/2011 09:29:05.698 or an AskQuantity of 100 are some of the attribute value pairs in our sample JSON file. You will notice in a JSON file, the tagging which describes the data attribute stays near the values.

example file

In order to begin using JSON, you first have to have a location where a file is available for you to import into Power BI. The JSON file can be on a local file directory or it can actually be linked to via a URL. Step 1 of the process is to start Power BI Desktop. Next, as shown below, you will either use the Get Data Splash screen option or the Get Data button from the Ribbon to start the Get Data Wizard.

Get Data 1

Get Data 2

Next select JSON as the data source.

json source

Finally, you select the file you want to work with: Get Quotes.JSON in our example.

select file

Alternately, we can enter a URL in the address to pull in the file from the web. For the example below, I used a subway station JSON file which was hosted at the noted site.

select file URL

In order to appropriately import or use a JSON data file, you will need to link and transform the file using the tools available in the Power BI query editor. As an initial step, you need to review the default structure that Power BI used to import the file. Often it will be a set of "Record" links that can be drilled down from the top level of the JSON structure. As shown below, you see that the initial dataset imported by Power BI is just Record. You may be thinking.... wow that is very unhelpful. However, you will also notice when I hover over the Record value, it is actually a hyper link allowing us to drill down into the file.

json query level 1

Drilling from the top level of the record now shows us the highest level of detail values within the JSON file. Notice, we have the option to Convert this set into a table or we can drill further into the embedded set of records which fall under the Quotes tag. The List link, highlighted below, allows us to drill down into the Quotes detail values. When I highlight the Quotes row (do not click on List link quite yet), we can see the Quotes list contains several Record rows (see bottom half of screen). Now go ahead and click on the List link to drill down into those records.

json import level 2

So now we drilled into Quotes record and subsequently have a list that just says Record. We now need to convert the rows into a table.

Level

Clicking on the Convert to Table button, we need to define if any delimiters exist and specify how to handle extra columns.

query level 3a

Hang with me for just a few more steps. As you look at the below illustration, it appears we have the same column of "Record". However, you will notice that a Split into Columns button now exists in the upper right hand corner of the column header.

drill down level 4

We are close to getting our wanted Quote data columns and values. Clicking on the Split into Columns button, we see in the below illustration a list of potential columns. You can also check or uncheck the Use original column name as prefix to add the current column name as a prefix (Column1 in the below example). I unchecked this option for our example.

level 5 split into columns

Now we are finally getting somewhere. As seen below, we now have our detailed data rows and columns which show NASDAQ quote information. Clicking the Close and Apply button in the upper left corner of the ribbon returns the data to the main visualization window. You will also note that the applied steps area displays on the right side on the below figure. The applied steps areas allows you, if needed, to "x" or undo each step in your transformation process. That undo functionality makes it handy to experiment with the correct drill down path for your JSON file.

level 6

At last, we are able to use the JSON data to create a neat visualization with our imported and transformed data!

VISUAL

You may be wondering, how do I navigate through a JSON file?  The classic answer is that "it depends". You will need to review the raw data file to see what data, tagging and levels are included in the file. Knowing the data and structure will guide you in the correct direction and path for your drill down methods in Power BI.

Conclusion

JSON files can now be used to bring data into Power BI via the "Get Data" function. JSON files act a bit like XML files in that the text within is tagged and well formed with attribute names and values. The process of importing a JSON file includes drilling down and transforming from the upper most level of the file until you get to the desired set of records needed for your Power BI visualization. The drill down process may require several iterations to get to the appropriate level, and fortunately, Power BI lets you undo transformations during the query edit process.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools