Using Power BI with JSON Data Sources and Files

By:   |   Comments (9)   |   Related: > 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

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, January 19, 2021 - 4:41:42 PM - Anderson Back To Top (88068)
Hello!!!
Only 7 Steps for GET JSON:
let
Source = SharePoint.Files("https://Mysharepoint", [ApiVersion = 15]),
Step01 = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://Mysharepoint_SearchFolder/")),
Step02 = Table.SelectColumns(Step01, {"Content", "Name", "Extension", "Folder Path"}),
Step03 = Table.SelectRows(Step02, each [Extension] = ".json"),
Step04 = Table.SelectColumns(Step03, {"Content"}),
Step05 = Table.AddColumn(Step04, "Json", each Json.Document([Content], 65001)),
Step06 = Table.SelectColumns(Step05, {"Json"}),
Step07 = Table.ExpandListColumn(Step06, "Json")
in
Step07

Monday, January 6, 2020 - 4:35:30 AM - Mwalima Back To Top (83637)

Super tutorial. Helped me with Json string object to tables.


Friday, December 20, 2019 - 4:42:58 PM - sebastiaan Back To Top (83486)

Hi,

Unfortunately the sample file can no longer be found and downloaded: "can be downloaded at: http://www.nasdaqdod.com/Samples.aspx "

Are you able to provide the original JSON and CSV sample file?

Regards,

Sebastiaan


Wednesday, August 28, 2019 - 3:54:12 AM - sajid rana Back To Top (82165)

How to use json file as a datasource to import themes for whole pbix solution? thanks in advance


Wednesday, August 7, 2019 - 12:22:00 PM - JORGE W. ROSERO CASTILLO Back To Top (81993)

Best regards from Ecuador thank you for sharing your knowledge


Thursday, October 4, 2018 - 8:42:30 AM - Chriss pratt Back To Top (77840)

Really Informative Article...Thanks for sharing wonderful Information. I am sharing blog URL of information read rest API task, where the complete task is discussed step by step. Check out the following link : 

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/


Monday, June 25, 2018 - 3:54:18 PM - Joan Hauff Back To Top (76391)

 Scott,

Thank you for your post. The subway URL shown no longer appears to work. Could you advise another URL to use?

Thanks, Joan


Friday, September 8, 2017 - 7:05:38 AM - Scott Murray Back To Top (66046)

Howard, the data should update to the latest data if you refresh the data source and the file has been updated. 

 


Friday, September 8, 2017 - 2:14:17 AM - Howard Diesel Back To Top (66039)

Hi Scott

Thanks for the article, was very helpful!

One concern I have is when refreshing - the JSON Data Source is pointing to a local file. You however provided a HTTP URL. If you refresh, will it pull the latest data?

 

Thanks

Howard















get free sql tips
agree to terms