# Cleanse addresses in Excel with Power Query

> Look up a column of addresses against the API from Excel or Power BI with Power Query, get postcode, municipality and coordinates back, and keep usage down.
>
> https://danadresse.dk/en/guides/excel-power-query · verified 2026-11-02
If you have a list of addresses in Excel, Power Query can look up each row and write the cleansed address, postcode, municipality and coordinates into new columns. You do not need to program, but you do need to paste in a small function. The guide is written for Excel for Windows (Microsoft 365) and works the same way in Power BI Desktop.

## 1. Prepare the key

Create a key under [Keys](/dashboard/keys). In Excel: **Data → Get Data → Launch Power Query Editor**. Choose **Manage Parameters → New Parameter**, name it `DanadresseNoegle`, set the type to *Text* and paste the key as the value. The key then lives in one place rather than inside the formulas.

## 2. Load your table

Select your data in the sheet and choose **Data → From Table/Range**. The table needs a column with the full address, for example `Adresse` holding "Strandvejen 100, 2900 Hellerup".

## 3. Create the function

Choose **New Source → Blank Query**, open **Advanced Editor** and paste the code. Rename the query to `fnVask`.

```m id=fn-vask run=no
(betegnelse as text) as record =>
let
    Kald = (sti as text, q as record) => Web.Contents(
        "https://api.danadresse.dk",
        [
            RelativePath = sti,
            Query = q,
            Headers = [#"X-Api-Key" = DanadresseNoegle],
            ManualStatusHandling = {400, 401, 403, 404, 429, 500, 503}
        ]
    ),
    // 1) Cleanse: category and the official match
    Svar = Kald("datavask/adgangsadresser", [betegnelse = betegnelse]),
    Status = Value.Metadata(Svar)[Response.Status],
    Json = if Status = 200 then Json.Document(Svar) else null,
    Top = if Json <> null and List.Count(Json[resultater]) > 0
          then Json[resultater]{0}[adresse] else null,
    // 2) Municipality and coordinates of the matched access address
    Detalje = if Top <> null then Kald("adgangsadresser/" & Top[id], []) else null,
    Adg = if Detalje <> null and Value.Metadata(Detalje)[Response.Status] = 200
          then Json.Document(Detalje) else null,
    Punkt = if Adg <> null then Adg[adgangspunkt][koordinater] else null,
    Felt = (r, navn as text) => if r <> null then Record.FieldOrDefault(r, navn) else null
in
    [
        Status = Status,
        Kategori = if Json <> null then Json[kategori] else null,
        AdgangsadresseId = Felt(Top, "id"),
        Vejnavn = Felt(Top, "vejnavn"),
        Husnr = Felt(Top, "husnr"),
        Postnr = Felt(Top, "postnr"),
        By = Felt(Top, "postnrnavn"),
        Kommune = if Adg <> null then Adg[kommune][navn] else null,
        Laengdegrad = if Punkt <> null then Punkt{0} else null,
        Breddegrad = if Punkt <> null then Punkt{1} else null
    ]
```

Three things in the code are worth understanding:

- `RelativePath` and `Query` keep the base URL fixed. The Power BI service needs that for scheduled refresh, and Power Query encodes æ, ø and å correctly by itself.
- `ManualStatusHandling` makes an error on one row produce a `Status` value instead of stopping the whole refresh.
- The cleanse answer holds the official street, house number, postcode and city, but no municipality or coordinates. The function therefore makes a second call to `/adgangsadresser/{id}` for the matched address. That is two calls per row; drop the second call if you only need the cleansed text.
- `/datavask/adgangsadresser` matches the entrance (street, house number, postcode). For the exact flat, see step 6.

## 4. Call the function for each row

Go back to your table, choose **Add Column → Custom Column**, and enter:

```m id=add-column run=no
= Table.ExpandRecordColumn(
    Table.AddColumn(Kilde, "Vask", each fnVask([Adresse])),
    "Vask",
    {"Status", "Kategori", "AdgangsadresseId", "Vejnavn", "Husnr", "Postnr", "By", "Kommune", "Laengdegrad", "Breddegrad"}
)
```

Replace `Kilde` with the name of the previous step (often `Source`), and `Adresse` with the name of your column. Click **Close & Load**.

The first time, Excel asks how to sign in to `https://api.danadresse.dk`. Choose **Anonymous**: the key is already sent in the header. If you get a `Formula.Firewall` error, go to **File → Options → Query Options → Privacy** and set both sources to the same level, for example *Organizational*.

## 5. Read the result

| Kategori | Meaning | What to do |
|---|---|---|
| A | Exact match (case and punctuation aside) | Use the result |
| B | One safe match with small spelling differences | Use it, and store the official text from the result |
| C | Uncertain: several candidates or larger differences | Check the row by hand. Never accept it automatically |

`Status` is 200 for rows that were looked up. 429 means you hit a limit (see step 7). 401 or 403 means the key is wrong, blocked or domain-locked. A domain-locked browser key does not work in Excel, because Excel sends no `Origin`.

## 6. Floor and door

Change `RelativePath` in the first call to `"datavask/adresser"`. The parser reads floor and door straight from the text (`Strandvejen 100, 2. tv, 2900 Hellerup`, also `st. th` and `1.mf`), and the exact flat is matched. `AdgangsadresseId` then holds the address (flat) id. Add `Felt(Top, "adgangsadresseid")`, `Felt(Top, "etage")` and `Felt(Top, "dør")` if you need them. The second call still works, because `/adgangsadresser/{id}` with an address id answers with its entrance.

If you keep floor and door in separate columns, you can send each field on its own instead:

```m id=fn-vask-enhed run=no
[vejnavn = [Vej], husnr = Text.From([Husnr]), etage = [Etage], #"dør" = [Doer], postnr = Text.From([Postnr])]
```

Pass the record to `Kald` in place of `[betegnelse = betegnelse]`, with the function taking the row instead of a text. Remove empty fields from the record, or an empty parameter is sent.

## 7. Watch your usage

Each row is one call (two with the municipality lookup) **every time** you refresh. The preview in the editor also makes calls. On the free plan the account has 2,000 calls per month and 360 calls per minute, and Power Query sends several calls at once.

`Status` 429 has two causes. `rate_limit_per_minute` passes within a minute: refresh again. `quota_exceeded` means the free plan's monthly quota plus its 20 % grace is used up, until the 1st of next month; the answer says so in plain text and links to the upgrade page. Refused calls are not counted.

- Work on a small sample (**Keep Rows → Keep Top Rows**) until the formula works.
- Turn off automatic refresh under **Query Properties** once the list is cleansed.
- For larger lists, upload a CSV under [Bulk cleansing](/dashboard/bulk) instead.
- Check your usage under [Usage](/dashboard/usage). The limits are explained in [Limits, quotas and 429](/en/guides/graenser-og-429).

## Test the call without Excel

The same call the function makes, from a terminal:

```bash id=test-call run=live expect="kategori"
curl -s "https://api.danadresse.dk/datavask/adgangsadresser?betegnelse=Strandvejen%20100%2C%202900%20Hellerup" \
  -H "X-Api-Key: $DANADRESSE_API_KEY"
```

## Next steps

- [Cleanse addresses in Google Sheets](/en/guides/google-sheets)
- [Clean a CRM export as CSV](/en/guides/rens-crm-csv)