CVR lookups in Excel with Power Query

With Power Query, Excel can fetch data from CVR-API without macros or VBA. You can look up a single company or enrich a whole list of CVR numbers with name, city and industry.

Fetch one company

  1. Go to Data > Get Data > From Other Sources > Blank Query.
  2. Open the Advanced Editor and paste the code below.
  3. Click Close & Load. The company lands as a table in your sheet.
powerquery
let
    Source = Json.Document(Web.Contents("https://apicvr.dk/api/v1/24256790")),
    Company = Record.SelectFields(Source,
        {"vat", "name", "address", "zipcode", "city", "industrydesc", "companydesc", "status"}),
    Table = Table.FromRecords({Company})
in
    Table

Enrich a list of CVR numbers

Create a new blank query with the function below and name it GetCVR. It takes a CVR number as text and returns name, city, industry and marketing protection.

powerquery
(cvr as text) =>
let
    Source = Json.Document(Web.Contents("https://apicvr.dk/api/v1/", [RelativePath = cvr])),
    Result = [
        name = Source[name],
        city = Source[city],
        industry = Source[industrydesc],
        protected = Source[protected]
    ]
in
    Result
  1. Load your table of CVR numbers into Power Query and make sure the column type is Text.
  2. Choose Add Column > Invoke Custom Function and pick GetCVR.
  3. Click the expand icon on the new column and pick the fields you want.
Excel may ask how to access https://apicvr.dk the first time. Choose Anonymous. The API needs no login.

Fetch the owners as a table

powerquery
let
    Source = Json.Document(Web.Contents("https://apicvr.dk/api/v1/24256790")),
    Owners = Table.FromRecords(Source[ejere]),
    Selected = Table.SelectColumns(Owners, {"name", "type", "ownership_range"})
in
    Selected

Refreshing and large lists

Click Refresh All to fetch fresh data. Each row is one request, so keep lists under a few thousand rows per refresh.

Always check protected before using the data for marketing. When it's true, the company has opted out of direct marketing.

Frequently asked questions

Does it work in Excel for Mac?

Yes, Power Query is included in recent versions of Excel for Mac (Microsoft 365). The menus have the same names.

Does it need VBA or macros?

No. Power Query is built into Excel, and the file can be saved as a regular .xlsx.

Why does the CVR column have to be text?

The function builds the URL from the text. If the column is a number, convert it with Text.From or change the type to Text.