Import JSON file using Power BI Desktop

Yesterday at the .NET user group session I was asked a question about importing JSON data using Power BI Desktop. Unfortunately I didn’t have a sample file to show it, so here is the blog post to compensate.
To get sample data, as long as you have SQL Server 2016 installed you can now take the contents of a table and output in JSON format just with T-SQL. I ran the following SQL on my AdventureWorksDW2014 database to get the JSON output and stored it in a file.
SELECT TOP (1000)
      [EnglishProductName]
      ,[Color]
      ,[SafetyStockLevel]
      ,[ReorderPoint]
      ,[EnglishDescription]
      ,[StartDate]
      ,[EndDate]
      ,[Status]
  FROM [AdventureWorksDW2014].[dbo].[DimProduct]  FOR JSON AUTO;
Now the JSON file is ready and now let’s see how to import it using Power BI Desktop.
1. Open Power BI Desktop and click on Get Data from the Tool bar and click More to see list of available Data source options and select JSON

2. Click on Connect to select the JSON file you created. [JSON sample to download] Once imported you can see the following screenshot.

3. Click on “To Table” from the menu to convert the data to Table.

4. Click on Split to split the data in the table to individual columns

5. You can rename the column by clicking on each column name and typing the new name you want.

6. All the columns have been imported as Text Data type. Change the data type of SafetyStockLevel and ReorderPoint to Whole Number.

7. Now you have successfully imported the JSON file in Power BI Desktop and go ahead a build your Report. [Download .pbix file]

Technorati Tags: Power BI,JSON