How do you load JSON data into PowerBI?

JSON data source were recently added to Power BI Data sources.

{
I have taken this JSON file from the following site;

 https://www.kodingmadesimple.com

and I will be using in this post.

Here is an abbreviated JSON file (with *.json extension) I am using.
--------------------------------------
{ 
  "data": [
    {
      "name": "Garrett Winters",
      "designation": "Accountant",
      "salary": "$170,750",
      "joining_date": "2011/07/25",
      "office": "Tokyo",
      "extension": "8422"
    },
    {
      "name": "Brielle Williamson",
      "designation": "Integration Specialist",
      "salary": "$372,000",
      "joining_date": "2012/12/02",
      "office": "New York",
      "extension": "4804"
    },
     ,
     ,
    {
      "name": "Quinn Flynn",
      "designation": "Support Lead",
      "salary": "$342,000",
      "joining_date": "2013/03/03",
      "office": "Edinburgh",
      "extension": "9497"
    }
  ]
}
-----------
Note that each Json element has six attributes.

Step 1:
You first need to save it to a location of your choice. You could also have it on an URL, but here it is assumed to be in one of the folders.

Launch PowerBI; Click GetData; Click More...

Click JSON

You need to browse and locate your JSON file (it will have  a.json extension).

My Koding.json file is now in Data View as shown.

 I go back to Edit Queries as I did not edit earlier.

 Now I have a query as shown in the left pane.

I had 15 elements in the JSOn file and they have become 16 records after getting it into PowerBI. Now go back to query using Edit Query as before. Now you see an extra control in Column 1 for splitting into its components.

I click the split control. The Query now appears as shown.

Now I convert this into table as shown above. Now what I see in DataView is the following:

The six elements are resolved into six fields as shown above. We will use this later. Click File | Save to save this file in the .pbix format. Presently it has the JSON data only.

That is all folks!

How do you bring in XML data into PowerBI?

Here is how to do it.

Launch PowerBI. Click Get Data.

Click XML. Open dialog is displayed. Previously I saved a CD.XML file from W3C site here.

I highlight CD.xml and click Open. Power BI processes my input, and brings up the CD.xml in the Navigator as shown.

I place a check mark for CD.XML. The file loads into the Navigator.

I click Load. My action Load gets processed.

Now CD.XML is loaded into PowerBI. I still don't see it.

 Change it to DataView on the Left. Now you see the data in CD.xml.

That's all folks. Now you can do what you want to do in PowerBI.

How do you load Excel data into PowerBI?

Get ready and keep an Excel file you want to load in an accessible location.

Launch PowerBI and click Get Data handle. The first submenu item is EXCEL.

Click Excel. In the Open dialog browse and locate you EXCEL file (*.xlsx). 

Click Open and the Navigator is displayed with the SampleExcel.xlsx file. It has only one sheet with a default name. In a real case you may have multiple sheets.

Click Sheet1. The data gets loaded on to the right pane as shown. There is an Edit button we will take it up later. The Navigator has all the columns on the original EXCEL sheet as shown.

 Click Load and the data gets loaded into PowerBI. Click the 'table' on the left pane (the data is in the center pane). The data is shown in a tabulated fashion as shown.

That's all! It's like 1-2-3

What's new in August update of PowerBI?

Believe me it is a very powerful data visualization tool. If you do not have PowerBI do not worry. You can sign up for free desktop PowerBI.  Follow the link at the end of the post.

Image from Microsoft site

If you are new to PowerBI get a head start using this 'from scratch' post here.

PowerBI got updated in August 2016 and the following categories got new stuff or improvements:

Report View

• Drill on line chart
  Continuous axis for Date axis
  General availability of inline hierarchies
  Predefined matrix styles
  Reorder fields in charts and tooltips
  Color formatting for KPI visual

Analytics

• Analytics pane
  Dynamic reference lines

Data Connectors

• Impala DirectQuery support
  Snowflake connector
  Improved Web connector – Web page previews
  General availability of SAP BW connector

Query editing improvements

• Option to Merge/Append as New Query

Desktop improvement

• Auto-recover Desktop files

Watch the video here:



Get more details in the form of a video for each of the new items or improvements here.