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.
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.
Next select JSON as the data source.
Finally, you select the file you want to work with: Get Quotes.JSON in our example.
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.
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.
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.
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.
Clicking on the Convert to Table button, we need to define if any delimiters exist and specify how to handle extra columns.
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.
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.
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.
At last, we are able to use the JSON data to create a neat visualization with our imported and transformed data!
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
- Check out these additional resources:
