Import, Transform and Visualize Data from REST API in Excel


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


  • 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.
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.

 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"})

  • 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).
Figure 2. Applied Steps panel
Figure 3. Result table
  • Click Close & Load to set table into the sheet.
  • Now, data is loaded and transformed to the table.


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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s