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.


API Versioning in ASP.NET (Core) with Swagger & Swashbuckle & Swagger UI


In previous article I have sum up API versioning. In this post I want to show how it can be provided in WebAPI [1] via Swagger and Swashbuckle [2, 3].

Swashbuckle is a NuGet package for both .NET and .NETCore that enables to generate Swagger documentation for ASP.NET projects. It can generate a rich documentation from various sources – operations, operation attributes or documentation elements. Next,it can be extended via various customization (for more details, see Swashbuckle documentation and samples).

API Versioning via Swagger

In case of API versioning, it provides ways how to provide this task in a simple way via publishing of multiple specification documents. And subsequently if you want to describe some breaking changes between two versions, e.g., property UserName was changed to Name,  Swagger model enables to specify description property. This property is rendered as Implementation Notes paragraph in Swagger UI [4] (see Figure 1). If you want to define this property in WebAPI, you have to specify remarks element in XML documentation:

/// <summary>
/// Gets value object by specified identifier.
/// </summary>
/// <remarks>
/// Breaking Change. A new, enrich model is returned.
/// </remarks>
/// <param name="id">The identifier.</param>
/// <returns>Value object</returns>

This, all together, will provide following doc:

Figure 1. Generated Swagger documentation


When using Swagger via Swashbuckle, API versioning change documentation is a simple task that can be provides via definition of remarks element in the operation XML comment.









API Versioning Overview


API versioning is a problem lasting for many years, but there is still no best way how to manage it. Most of the existing articles and post dealing with this topic mention why are particular solution bad from various purposes [1, 4]. Next, because I have gone through many discussions in last years, I wanted to sum up it for future discussions too.

Basically, it can be divided into these main parts:

  • Versioning of the API resources
  • Versioning of the code of API resources
  • Versioning semantic
  • Versioning changes description / migration guide


Versioning Of the API Resources

This is the mainly discuss topic – how to publish and manage an evolving API.  Basically, there are mentioned four possibilities and each of them has various pros and cons:

URL Versioning

  • It is straightforward
  • It breaks REST paradigm that resource should represent an entity
  • You are changing resource with the new version


Custom Request Header

api-version: 2
  • Resource is the same for all versions
  • It is hard to test


Accept Content Type

Accept: application/vnd.somepage.v2+json
  • Resource is the same for all versions
  • It is hard to test


Query Argument

  • Resource is the same for all versions
  • Every has to have query argument
  • Possible collision with resource query argument


Versioning of the code of API Resources

Except versioning of the resources there is another important task – how to version and maintain multiple versions of the API? Lets suppose, we are using Git as a source control.

Separate Branch for Each Version

  • Additional changes (e.g., bug fixes) that must be done in both branches must be merged or cherry-picked.
  • Switching between branches is evident – you delete obsolete branch and create a new one.
Figure 1. Separate branch for each version


One Codebase, Versions are in Separated Folders

  • Additional changes (e.g., bug fixes) can be done in one commit (or pull request)
  • Switching between branches is more evident – you delete obsolete version, add copy of the last version.
Figure 2. Versions in separated folders. Deployed as separate resources.
Figure 3. Versions in separated folders. Deployed as one resource.


One Codebase

  • In one code base, particular version is selected just via route. Particular logic (business layer, data layer) can be shared in some cases or a specific version is created when sharing is not possible.
  • Switching between branches is not so evident – renaming can be confusing.
Figure 4. Code for versions is shared. Deployed as separate resources.
Figure 5. Code for versions is shared. Deployed as one resource.


One Codebase, Versioning via Adapter

  • In this solution there is a single code for the last version (vX). The previous version using particular resources transforms request to the structure of vX and call it. When the vX returns, it transforms the response to the resource response structure. So the previous version stands an adapter which satisfy backward compatibility via transformation.
  • This solution is not possible to use in all cases, e.g., because of possible breaking changes.
  • Figures for are same as for One Codebase option. The difference in the way how the particular version is served.


Versioning Semantic

Semantic, e.g., the way how the version are named is an important issue too. Basically, it should follow common style, i.e, X.Y.Z or X.Y. For more details check [2].


Versioning Changes Description / Migration Guide

This is simply must. Just do it :).



This post was created just because I wanted to sum up a discussion which I have gone through many times last years.

Although there is no one best solution, there are many possibilities which can fit for particular needs or situation. And these will be the best for this purpose [3].