Retrieving JSON data in Power BI

JSON is another source of data that can be used in Power BI in its October Update. This post shows how you may do it.

I need to construct some JSON data. I will use the SQL Server 2016 to create data in JSON formatted form using the FOR JSON AUTO clause in a SQL Query as shown.
-----------
SELECT        Person.Person.BusinessEntityID, Person.Person.FirstName, Person.Person.MiddleName, Person.PersonPhone.PhoneNumber
FROM            Person.EmailAddress INNER JOIN
                         Person.Person ON Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN
                         Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
       Where PhoneNumber like '808%'
For JSON Auto
----------
In returning the response, I will return it to a file using the Query | Results To | Results to File item in SSMS.

I will save the file to a location of my choice as AdvWrks.json

I launch POWER BI and in the Get Data menu items, pick JSON as shown here:

GetJson.png
This action takes me to File Open dialog and I pick up the saved file AdvWrks.json.
This opens the Poweer BI's Query Editor as shown

GetJSON2.PNG

This is a list and you can use the List Ribbon items.

Each of the 17 rows returned by the SQL Query is represented in a Record in the List. It is a hyperlink to the items in the record as we will presently see.

You can also convert it to a table by clicking on the 'To Table' ribbon item. Which brings up the window.

GetJSON3.PNG

Just accept default and click OK.This produces a table as shown.

GetJSON4.PNG

If you liked this, you may also want to read these:

http://hodentekhelp.blogspot.com/2016/11/how-do-you-create-power-bi-report-using.html

http://hodentekhelp.blogspot.com/2016/11/how-do-you-create-power-bi-report-using_22.htmlhttp://hodentekhelp.blogspot.com/2016/11/how-do-you-create-power-bi-report-using_22.html