Cleanse addresses in Excel with Power Query

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

(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:

= 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#

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:

[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 instead.
• Check your usage under Usage. The limits are explained in Limits, quotas and 429.

Test the call without Excel#

The same call the function makes, from a terminal:

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
• Clean a CRM export as CSV