Thursday, November 17, 2016

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:


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


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.


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


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

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...