Power QueryExercises

How to clean a messy CRM export in Power Query

Use case · By Tahir Nas · Examples run in our simulator · Updated

13 min read

To clean a messy CRM export in Power Query, build one query that does the same six things every time: turn empty text into null, tidy the text columns, map inconsistent values through a lookup table, convert text dates with the right culture, remove duplicate contacts on a normalised key, and list the rows that still break your rules. Because the steps are saved, next month's export gets the same treatment when you press Refresh, instead of another afternoon of Find and Replace.

What a typical CRM export gets wrong

Contact exports from sales and marketing systems tend to share the same faults, whatever the vendor. People type names in any case, paste addresses with trailing spaces, and leave fields empty. Imports from spreadsheets or web forms add a second copy of a contact with a slightly different email. Country is a free-text field, so the same country arrives three ways.

Here is a small export with all of those faults. It runs as written, so you can paste it into a blank query and follow along.

let
    Source = #table(
        {"Contact ID", "Full Name", "Email", "Company", "Country", "Created"},
        {
            {"C-001", "  ada LOVELACE ", "Ada.Lovelace@Example.com ", "Analytical Engines Ltd", "UK", "03/02/2026"},
            {"C-002", "Grace Hopper", "grace@navy.example", "", "United States", "14/02/2026"},
            {"C-003", "ADA LOVELACE", "ada.lovelace@example.com", "Analytical Engines Ltd.", "United Kingdom", "20/02/2026"},
            {"C-004", "Alan  Turing", "alan@bletchley.example", "Bletchley Park", "GB", "01/03/2026"},
            {"C-005", "", "", "", "", ""}
        }
    )
in
    Source

Result

Contact IDFull NameEmailCompanyCountryCreated
C-001 ada LOVELACE Ada.Lovelace@Example.com Analytical Engines LtdUK03/02/2026
C-002Grace Hoppergrace@navy.exampleUnited States14/02/2026
C-003ADA LOVELACEada.lovelace@example.comAnalytical Engines Ltd.United Kingdom20/02/2026
C-004Alan Turingalan@bletchley.exampleBletchley ParkGB01/03/2026
C-005

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Read the problems off the table before changing anything:

ColumnProblemFix
Full NameLeading and trailing spaces, a double space, mixed caseTrim, collapse spaces, proper case
EmailTrailing space, capital lettersTrim, lower case
CompanyEmpty text, "Ltd" and "Ltd."Empty text to null, trim a trailing full stop
Country"UK", "GB" and "United Kingdom" for one countryLookup table
CreatedDay-first dates stored as textConvert with the en-GB culture
Whole rowC-005 has nothing but an IDRemove rows that are empty apart from the key

Power Query's data profiling tools (Microsoft Learn) help you find these faults on a real export: column quality shows empty and error counts, and column distribution shows distinct and unique values. By default the profile covers only the first 1,000 rows, so switch it to the entire data set before you trust it. The lesson on profiling columns before you trust them walks through the same habit.

The cleaning query, step by step

The full query below fixes every column in the table above. Each step is described after the code.

let
    Source = #table(
        {"Contact ID", "Full Name", "Email", "Company", "Country", "Created"},
        {
            {"C-001", "  ada LOVELACE ", "Ada.Lovelace@Example.com ", "Analytical Engines Ltd", "UK", "03/02/2026"},
            {"C-002", "Grace Hopper", "grace@navy.example", "", "United States", "14/02/2026"},
            {"C-003", "ADA LOVELACE", "ada.lovelace@example.com", "Analytical Engines Ltd.", "United Kingdom", "20/02/2026"},
            {"C-004", "Alan  Turing", "alan@bletchley.example", "Bletchley Park", "GB", "01/03/2026"},
            {"C-005", "", "", "", "", ""}
        }
    ),
    Blanks = Table.ReplaceValue(Source, "", null, Replacer.ReplaceValue, Table.ColumnNames(Source)),
    NoEmptyRows = Table.SelectRows(Blanks, each List.NonNullCount(List.RemoveFirstN(Record.FieldValues(_), 1)) > 0),
    Tidy = Table.TransformColumns(NoEmptyRows, {
        {"Full Name", each if _ = null then null else Text.Proper(Text.Combine(List.Select(Text.Split(Text.Trim(Text.Clean(_)), " "), each _ <> ""), " ")), type nullable text},
        {"Email", each Text.Lower(Text.Trim(Text.Clean(_))), type nullable text},
        {"Company", each Text.TrimEnd(Text.Trim(_), "."), type nullable text}
    }),
    Countries = #table({"Raw", "Country"}, {
        {"UK", "United Kingdom"}, {"GB", "United Kingdom"}, {"United Kingdom", "United Kingdom"},
        {"US", "United States"}, {"United States", "United States"}
    }),
    Joined = Table.NestedJoin(Tidy, {"Country"}, Countries, {"Raw"}, "Map", JoinKind.LeftOuter),
    Mapped = Table.ExpandTableColumn(Table.RemoveColumns(Joined, {"Country"}), "Map", {"Country"}),
    Typed = Table.TransformColumnTypes(Mapped, {{"Created", type date}}, "en-GB")
in
    Typed

Result

Contact IDFull NameEmailCompanyCreatedCountry
C-001Ada Lovelaceada.lovelace@example.comAnalytical Engines Ltd2026-02-03United Kingdom
C-002Grace Hoppergrace@navy.examplenull2026-02-14United States
C-003Ada Lovelaceada.lovelace@example.comAnalytical Engines Ltd2026-02-20United Kingdom
C-004Alan Turingalan@bletchley.exampleBletchley Park2026-03-01United Kingdom

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result has four rows. Both Ada Lovelace rows now read "Ada Lovelace" with the email ada.lovelace@example.com, both companies read "Analytical Engines Ltd", Grace Hopper's company is null, every country is either "United Kingdom" or "United States", and Created holds real dates (3 February 2026 for C-001).

Turn empty text into null first

Table.ReplaceValue with Replacer.ReplaceValue swaps whole cells that equal "" for null across every column. Do this before anything else. Empty text and null are different values in M, so a filter on [Company] = null would miss the empty ones, and a count of missing values would be wrong. The lesson null, empty text and errors are different things explains why this matters.

The next step removes rows that are empty apart from their ID. Record.FieldValues(_) turns the row into a list, List.RemoveFirstN drops the ID, and List.NonNullCount counts what is left. A row with nothing but an ID has a count of zero and goes.

Tidy the text columns in one step

Table.TransformColumns applies a function to each named column, and the third item in each list sets the column type at the same time (Table.TransformColumns, Microsoft Learn).

  • Names. Text.Clean removes control characters such as line feeds (Text.Clean, Microsoft Learn). Text.Trim removes leading and trailing whitespace (Text.Trim, Microsoft Learn). Neither touches the double space inside "Alan Turing", so the name is split on spaces, the empty pieces are dropped, and the rest are joined with one space. The post on splitting full names into first and last names takes the same tidy step further. Text.Proper then capitalises the first letter of each word and lower-cases the rest (Text.Proper, Microsoft Learn).
  • Emails. Clean, trim, then lower-case. Email addresses are used as keys, so they need one canonical form.
  • Company. Text.TrimEnd(_, ".") removes a trailing full stop, which is enough to make "Ltd" and "Ltd." agree. It deliberately does nothing cleverer. Anything beyond small, predictable differences belongs in a lookup table or a fuzzy merge, covered in Fuzzy matching in Power Query.

Text.Trim, Text.Clean, Text.Lower and Text.Proper accept a null value and return null. Text.Split expects text, so the name rule checks for null first. That way a contact with an email but no name keeps a null name instead of getting an error. The lesson on trimming, cleaning and capitalising text covers each function in more depth.

Text.Proper is a blunt tool for names: "McDonald" becomes "Mcdonald". If your CRM already holds correctly capitalised names, trim them and leave the case alone. Use proper case only where the source is clearly wrong, such as all capitals.

Map inconsistent spellings with a lookup table

A nested if for countries works until someone types "Great Britain". A two-column table of raw spelling and clean value is easier to read and to extend: add a row, and the query picks it up. The guide on mapping tables versus nested if compares the two approaches.

In a real workbook the Countries table would be its own query, loaded from a small Excel table you maintain. Here it is inline so the example runs anywhere. The merge is a left outer join, so contacts whose country isn't in the table keep their row with a null country, which the problem-rows query below catches.

Convert text dates with the right culture

The Created column holds day-first dates such as "03/02/2026". Passing "en-GB" as the culture to Table.TransformColumnTypes reads them as 3 February, not 2 March. Leave the culture out and the result depends on the machine's regional settings, which is how a query works on one laptop and fails on another. The guide change type using locale has more examples.

Remove duplicate contacts on a normalised key

Duplicates in a CRM export are rarely exact copies. The same person appears twice with a different contact ID, a different created date and an email that differs only in case. Two things decide whether you catch them: the key, and which copy you keep.

Microsoft's article on working with duplicate values (Microsoft Learn) warns that Power Query is case-sensitive when it compares text, and the same rule silently drops matches in merges and filters. This is easy to see:

let
    Source = #table({"Email"}, {{"Ada.Lovelace@Example.com"}, {"ada.lovelace@example.com"}, {"grace@navy.example"}}),
    Distinct = Table.Distinct(Source)
in
    Distinct

Result

Email
Ada.Lovelace@Example.com
ada.lovelace@example.com
grace@navy.example

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

All three rows survive, because the two Ada addresses differ in case. You can pass a comparer to compare without case:

let
    Source = #table({"Email"}, {{"Ada.Lovelace@Example.com"}, {"ada.lovelace@example.com"}, {"grace@navy.example"}}),
    Distinct = Table.Distinct(Source, {"Email", Comparer.OrdinalIgnoreCase})
in
    Distinct

Result

Email
Ada.Lovelace@Example.com
grace@navy.example

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

That returns two rows; here the first spelling is the one kept, but, as the next section explains, no particular copy is guaranteed. Lower-casing the email in the cleaning step, as the main query does, gets the same result and also gives you a clean key for merges later.

Choose which copy to keep with a rule

The Table.Distinct reference (Microsoft Learn) says there is no guarantee which duplicate is preserved, so "sort, then remove duplicates" is not a rule you can rely on. State the rule instead. This version groups by email and keeps the most recently created row, using Table.Max, plus a count of how many records were merged:

let
    Clean = #table(
        type table [#"Contact ID" = text, Full Name = text, Email = nullable text, Company = nullable text, Created = date, Country = text],
        {
            {"C-001", "Ada Lovelace", "ada.lovelace@example.com", "Analytical Engines Ltd", #date(2026, 2, 3), "United Kingdom"},
            {"C-002", "Grace Hopper", "grace@navy.example", null, #date(2026, 2, 14), "United States"},
            {"C-003", "Ada Lovelace", "ada.lovelace@example.com", "Analytical Engines Ltd", #date(2026, 2, 20), "United Kingdom"},
            {"C-004", "Alan Turing", "alan@bletchley.example", "Bletchley Park", #date(2026, 3, 1), "United Kingdom"}
        }
    ),
    Grouped = Table.Group(Clean, {"Email"}, {{"Latest", each Table.Max(_, "Created"), type record}, {"Records", each Table.RowCount(_), Int64.Type}}),
    Latest = Table.FromRecords(Grouped[Latest]),
    WithCount = Table.NestedJoin(Latest, {"Email"}, Table.SelectColumns(Grouped, {"Email", "Records"}), {"Email"}, "G", JoinKind.LeftOuter),
    Out = Table.ExpandTableColumn(WithCount, "G", {"Records"})
in
    Out

Result

Contact IDFull NameEmailCompanyCreatedCountryRecords
C-003Ada Lovelaceada.lovelace@example.comAnalytical Engines Ltd2026-02-20United Kingdom2
C-002Grace Hoppergrace@navy.examplenull2026-02-14United States1
C-004Alan Turingalan@bletchley.exampleBletchley Park2026-03-01United Kingdom1

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Three contacts remain. Ada Lovelace is kept as C-003, created on 20 February 2026, with Records = 2. The other two have Records = 1.

Two cautions. First, contacts with no email all share the key null, so grouping on email would merge them into one. Filter them out before grouping and append them back afterwards, or deduplicate them on a different key. Second, "most recent" is only one possible rule. "Most complete row" or "the one with an account owner" may suit your team better. The lessons on removing duplicates on the right key and choosing which duplicate to keep cover both decisions.

List the rows that still need a human

Some faults can't be fixed by a rule: an email with no @ sign, a contact with no email at all, a country nobody has mapped yet. Deleting those rows hides the problem. Listing them gives the CRM owner something to fix at source.

let
    Clean = #table({"Contact ID", "Email", "Country"}, {
        {"C-001", "ada.lovelace@example.com", "United Kingdom"},
        {"C-002", "grace(at)navy.example", "United States"},
        {"C-006", null, null}
    }),
    Issues = Table.AddColumn(Clean, "Issues", each Text.Combine(List.RemoveNulls({
        if [Email] = null then "no email" else if not Text.Contains([Email], "@") then "email has no @" else null,
        if [Country] = null then "country not mapped" else null
    }), "; "), type text),
    Problems = Table.SelectRows(Issues, each [Issues] <> "")
in
    Problems

Result

Contact IDEmailCountryIssues
C-002grace(at)navy.exampleUnited Statesemail has no @
C-006nullnullno email; country not mapped

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Two rows come back: C-002 with "email has no @", and C-006 with "no email; country not mapped". Load this query to its own sheet, next to the clean contacts, so the count of problem rows is visible every time the report refreshes.

To find spellings that the country lookup doesn't know yet, use a left anti join, which keeps only the rows with no match:

let
    Source = #table({"Country"}, {{"UK"}, {"Deutschland"}}),
    Countries = #table({"Raw", "Country"}, {{"UK", "United Kingdom"}}),
    Joined = Table.NestedJoin(Source, {"Country"}, Countries, {"Raw"}, "Map", JoinKind.LeftAnti)
in
    Table.RemoveColumns(Joined, {"Map"})

Result

Country
Deutschland

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Only "Deutschland" is returned. Add it to the lookup table and the next refresh maps it. The lesson on validating data and failing loudly shows how to stop a refresh outright when a check like this fails, for cases where a wrong report is worse than no report.

Keep the query working on next month's export

A cleaning query is only useful if it survives the next file. A few habits help:

  • Refer to columns by name, never by position. CRM admins add and reorder fields. Steps that name the columns they need keep working. When a field is renamed rather than added, handling changing column headers shows how to absorb it.
  • Keep the lookup tables outside the query. A country or status table on a worksheet can be updated by anyone, without opening the editor.
  • Clean before you compare. Every merge and every duplicate check should use the cleaned columns, never the raw ones.
  • Check the row counts. Note the rows in, the empty rows dropped, the duplicates merged and the rows out. Rows in minus the two removals should equal rows out. Watch the problem-row count separately: if it jumps, something in the source has changed.

The data cleaning recipes guide has short versions of the individual fixes used here. Phone numbers and postcodes, which many contact exports also carry, need rules of their own, covered in cleaning phone numbers and postcodes.

Practise on a CRM deduplication project

These steps are easier to remember once you have done them on data that fights back. The CRM deduplication project works through a contact list end to end, and the exercise count each email once, whatever its case or spacing practises the normalised key on its own. The exercise to list every validation issue per lead builds the problem-rows query. Every example on Power Query Exercises runs in the browser, so you can check your answer against the expected output straight away.

Frequently asked questions

Should I clean CRM data in Power Query or in the CRM itself?

Both. Fix what you can at source, because every report that reads the CRM benefits. Use Power Query for the fixes your report needs today and for the problem-rows list, which tells the CRM owner exactly which records to correct.

Why does Remove Duplicates keep both copies of a contact?

Usually because the values differ in a way you can't see: case, a trailing space or a non-printing character. Trim, clean and lower-case the key column first, then remove duplicates on that column only, not on every column.

Can Power Query merge contacts whose names are spelled slightly differently?

Yes, with a fuzzy merge, which matches text by similarity rather than equality. It needs careful thresholds and a review of the matches, so treat it as a suggestion list rather than an automatic fix.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.