dev

Import, Transform and Visualize Data from REST API in Excel

Overview

This is just a short manual how to import, transform and visualize data from REST API in Excel via Power Query add-on.

How-to

  • Download and install Excel Power Query.
  • In Power Query tab select From Web.
  • Fill-in resource URL.
  • After the data is loaded, a new Query Editor window will open (see Figure 1). A list of JSON objects (records) was loaded.
queryeditor1
Figure 1. Query Edtior
  • Now, we have to transform record to table row. Go to View tab and select Advance Editor.
  • In this example, a record consists of sensorId, date, temperature and humidity properties. We want to have a column for each of these properties.

let
 Source = Json.Document(Web.Contents("URL_WITH_DATA")),
 // Transform the List Result into a Table
 DataTable= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 // Expand the Result Column: Column1 record from DataTable is transformed to sensorId, date, temperature and humidity columns as ResultTable
 ResultTable= Table.ExpandRecordColumn(DataTable, "Column1", {"sensorId", "date", "temperature", "humidity"})
in
ResultTable

  • There should be three steps in the Applied Steps panel on the right (Figure 2). First is the loaded source. Second is source transformed to DataTable with one column Column1 that is the the record. Third step represents transformed DataTable to final ResultTable with transformed columns from record properties (Figure 3).
queryeditor2
Figure 2. Applied Steps panel
queryeditor3
Figure 3. Result table
  • Click Close & Load to set table into the sheet.
  • Now, data is loaded and transformed to the table.

Conclusion

A simple how-to manual how to work with data from REST resources in Excel.