How to extract and normalize addresses with Copilot in Fabric

João Barros 16 de September de 2026 4 min read

This tutorial shows how to use Copilot in Fabric to extract and normalize addresses from free text — useful for transforming contact or record data into structured columns (street, number, postal_code, city, country) ready for analysis and geocoding.

Prerequisites

Account with access to Microsoft Fabric and permissions to use Copilot in the workspace.
A table or file with a text column containing mixed addresses (e.g., CSV in OneLake or table in the Warehouse).
Basic knowledge of SQL or Dataflow Gen2 (optional for context).

Step 1: Prepare the data source

Open the file or table that contains the text column with the addresses. It’s important to have a representative sample (50–500 rows) for Copilot to learn the pattern. If you’re on a CSV in OneLake, upload it to a Dataset in the Workspace.

Step 2: Start Copilot in the context of the file/table

In the data file (it can be a Dataset, Dataflow Gen2 or a table in the Warehouse), open the Copilot pane. Clearly explain to Copilot the objective: extract address fields and normalize the format. A clear prompt reduces common errors.

Step 3: Initial prompt for extraction

Ask Copilot to identify and extract the basic components. Use examples in the prompt (3–5 lines) to show variations. Example of a minimal prompt:

Tenho uma coluna "raw_address" com texto livre. Para cada linha, crie colunas: street, number, postal_code, city, country. Aqui vão exemplos:
"Rua da Liberdade 23, 1200-456 Lisboa, Portugal" -> street: Rua da Liberdade; number: 23; postal_code: 1200-456; city: Lisboa; country: Portugal
"Av. da República, 45, 4000 Porto" -> street: Av. da República; number: 45; postal_code: 4000; city: Porto; country: Portugal
Gere uma transformação que produza as colunas pedidas e trate casos sem número ou sem país.

Step 4: Review and adapt the suggested transformation

Copilot will usually suggest code or steps (for example, a transformation in Dataflow Gen2 or SQL in the Warehouse). Review whether it uses appropriate regex and covers variations (abbreviations: Av., R., nº). Ask Copilot to add rules for abbreviations and to fill empty fields with NULL or default values.

Step 5: Implement the transformation (SQL example in the Warehouse)

If you choose to generate SQL, Copilot may suggest something like the minimal example below. Insert and run it in the Warehouse Query Editor or in a view.

SELECT
  raw_address,
  -- extrai número no fim ou após vírgula
  NULLIF(REGEXP_EXTRACT(raw_address, '\\b(\\d{1,5})(?:[ -]?[A-Z0-9]+)?\\b'), '') AS number,
  -- extrai código postal português (ex.: 1200-456 ou 4000)
  NULLIF(REGEXP_EXTRACT(raw_address, '(\\d{4}-\\d{3}|\\d{4})'), '') AS postal_code,
  -- tenta extrair cidade antes da vírgula ou após postal code
  NULLIF(TRIM(REGEXP_EXTRACT(raw_address, '(?:\\d{4}-\\d{3}|\\d{4})[, ]*([^,]+)')), '') AS city,
  -- detecta país por palavra-chave comum; pode ser NULL
  CASE WHEN raw_address LIKE '%Portugal%' THEN 'Portugal' ELSE NULL END AS country,
  -- street: remove número, postal e cidade para isolar rua
  TRIM(REGEXP_REPLACE(raw_address, '(\\d{4}-\\d{3}|\\d{4})|\\b(Portugal|Portugal)\\b|,?\\s*[^,]+$', '')) AS street
FROM my_dataset.my_table;

Step 6: Handle exceptions and iterate with Copilot

Run a query on a sample and analyze common errors: failures with abbreviations, atypical numbers, multiple addresses in one line. Ask Copilot to generate alternative regex or create a column with a quality tag (ok / review) for rows that don’t match patterns. Example prompts: "Mark as review if postal_code or number is not found."

Verify the result

Confirm that the columns street, number, postal_code, city and country are correctly populated on a representative sample. Look for rows with unexpected NULL values and check the quality column (if created). Also test with different formats (abbreviations, without postal code, foreign addresses).

Conclusion

After extracting and normalizing addresses with Copilot in Fabric, you can use the data for deduplication, geocoding or location analysis. Next steps: create automated rules to flag and send rows in review for human review, or integrate a geocoding service. Tip: keep prompts with real examples from your dataset to reduce errors.