There are many business intelligence solutions large and small for knowledge workers to choose from. But due to its ubiquity, I assume many (myself included) just use Microsoft Excel to interactively analyze data.
A lot of data on the web is available via RESTful APIs returning JSON, e.g. the REST countries service I’ll use in this example.
In the 2016 release Microsoft has vastly expanded the data analysis capabilities of Excel compared to previous versions. And with the free PowerQuery plugin, these capabilities are available to Excel 2013 users as well.
Getting JSON into Excel
One of these capabilities is to retrieve JSON data from the web and turn it into an Excel table. Just go to Data > From Web and enter the URL, e.g.
Excel will figure out whether there is a regular web page at the given URL (and offer to extract HTML tables from it) or JSON. In the latter case, a list of records is displayed in the Query Editor.
There is a Convert To Table button conveniently placed at the top left. But with every JSON document I’ve come across, this has required several additional steps to create a proper table.
Making a table
Instead I recommend going to View > Advanced Editor and add a manual conversion step by changing the query to this.
Source = Json.Document(Web.Contents("http://restcountries.eu/rest/v2/all")),
Table = Table.FromRecords(Source)
The same can be achieved by adding a manual step in the Query Settings sidebar and adding the bold text as the function.
Voilà, you now have that JSON as a table with one record per country.
You may want to add some more steps such as right-click the topLevelDomain column and Extract Values… to get the list of domains comma-separated in a single cell.
Great usability for non-programmers
This is just one of the many conversion and transformations available via easy to use (but numerous) commands in the context-menu and ribbon.
The great thing about Excel’s handling of all of these conversion steps is that they are applied non-destructively. In the Query Settings sidebar, you can see each one and click it to see the intermediate results it produces.
Not so great: M language
There is one, thing, however, that is a bit disappointing: the programming language behind all of this is the M language inspired by F#. This is unfortunate as it means any previous skills you’ve had working with data in Excel macros is useless, as even basic things such as if statements are syntactically so different I needed to look it up.