Using Excel Power Query to Clean and Transform Email Data

What Power Query Does for Email Data

Power Query is a data transformation tool built into Excel (Windows and Mac, Microsoft 365) and Google Sheets (via Apps Script or add-ons). It lets you connect to data sources, clean and reshape the data, and load the results into a spreadsheet. For email list management, Power Query handles tasks that are tedious or error-prone with formulas:

Why Power Query instead of formulas

Getting Started with Power Query for Email Data

Opening Power Query

Loading email data into Power Query

Common Email Cleaning Transformations

Step 1: Trim and lowercase

Whitespace and case inconsistencies are the most common data quality issues:

Power Query M formula (Add Custom Column):

Text.Lower(Text.Trim([Email]))

Step 2: Validate email format

Filter out rows that are not valid email addresses:

Keep only rows containing @ with text on both sides:

// Custom column: IsValidEmail
Text.Contains([Email], "@") and
Text.Length(Text.BeforeDelimiter([Email], "@")) > 0 and
Text.Length(Text.AfterDelimiter([Email], "@")) > 0 and
Text.Contains(Text.AfterDelimiter([Email], "@"), ".")

Then filter to keep only rows where IsValidEmail is TRUE.

Step 3: Extract domain from email

Add Custom Column:

Text.AfterDelimiter([Email], "@")

This creates a Domain column useful for analysis, deduplication by company or filtering.

Step 4: Remove duplicates

Step 5: Fix common domain typos

Replace Values (Home > Replace Values) for each common typo:

For many replacements, create a custom function:

// Create a function query called FixDomain
(domain as text) as text =>
let
    fixes = [
        gmial.com = "gmail.com",
        gmal.com = "gmail.com",
        gamil.com = "gmail.com",
        yaho.com = "yahoo.com",
        hotmal.com = "hotmail.com",
        outlok.com = "outlook.com"
    ],
    result = try Record.Field(fixes, domain)
             otherwise domain
in
    result

Step 6: Filter out unwanted addresses

Filter role-based addresses (Custom Column + Filter):

// Add custom column: IsRoleBased
let
    local = Text.BeforeDelimiter(Text.Lower([Email]), "@"),
    rolePrefixes = {"info", "admin", "support", "sales",
                    "contact", "help", "billing",
                    "noreply", "no-reply", "webmaster",
                    "postmaster", "abuse"},
    isRole = List.AnyTrue(
        List.Transform(
            rolePrefixes,
            each Text.StartsWith(local, _)
        )
    )
in
    isRole

Then filter to keep only rows where IsRoleBased is FALSE.

Advanced Transformations

Splitting "Name <email>" format

Some data sources combine name and email in a single field:

John Smith <john.smith@example.com>
Jane Doe <jane.doe@example.org>

Steps:

1. Split Column > By Delimiter > < > At each occurrence
2. The second column contains the email with a trailing >
3. Replace Values: find >, replace with empty string
4. Rename columns to Name and Email
5. Trim both columns

Merging multiple email lists

Adding a source identifier before appending:

Add a Custom Column to each query before appending:

// In Query 1:
"conference-list" as source column

// In Query 2:
"website-scrape" as source column

Merging email list with company data

Use Merge Queries to join an email list with a company lookup table:

Deduplicating by company (keep one email per domain)

Working with Large Email Datasets

Performance tips

Handling common issues

Automating Recurring Workflows

Setting up automatic refresh

Building a reusable cleaning template

Pre-Processing with Email Extractor

Before loading email data into Power Query, you may need to extract email addresses from unstructured sources. Upload files to Email Extractor to extract email addresses from PDF, HTML, DOCX, XLSX, CSV and other formats. Download the results as CSV, which loads directly into Power Query for further cleaning and transformation. This two-step workflow handles extraction (Email Extractor) and transformation (Power Query) separately, using each tool for what it does best.

Quick Reference: Common M Functions for Email Data

Related Guides

• Spreadsheet Data Cleaning Techniques
• Normalizing Email Formats
• Removing Duplicates at Scale
• Data Normalization Workflows
• Automating Data Cleaning Workflows