Clean your customer master data with Excel 2013 and Public data

The open data in Denmark has started to evolve – and now a public marketplace of data and apps is available as well via –

One of the apps in the App market is actually very nice and can then be used in Excel to wash/check your master about Danish companies. The app is a mirror of data from The Central Business Register – and can be accessed via, and is not associated with

Using the new Excel functions WEBSERVICE and FILTERXML can then be used to lookup values via the API.

First I found a list of all the companies in Denmark where the name Microsoft was included.

A simple search via the API – for the first VAT number gives me the result but also the syntax to call the API –

The documentation reveals that the result can be returned either as JSON or XML via a Format parameter

So by calling we get an XML result.

Then we use the new WEBSERVICE function in Excel to call the API


This returns the full XML text in the cell.

Then we use FILTERXML function to retrieve the different xml node values – so to get the company name


I use the headers of the table to specify the different xml node values – that I want to retrieve

Easy and fast I can retrieve meta data for all the companies in the list – including addresses, number of employees, industry and more via the API.

Then add some Conditional formatting and you can quickly identify differences between your data and the public data.

The use of the API could also be used in Power Query to create a function to add meta data to existing items in other tables.

You can download the sample file from here – Link

3 thoughts on “Clean your customer master data with Excel 2013 and Public data

  1. Pingback: Excel Roundup 20141020 « Contextures Blog

  2. Pingback: Using Power BI to extract data from Office 365 Reporting web services #powerbi #office365 | Business Intelligence Info

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 )

Facebook photo

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

Connecting to %s