Power Query data cleaning recipes for messy exports
Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated
Most cleaning is the same dozen moves in a different order. These recipes use a small, deliberately messy customer list. Apply them in roughly the order shown: structure (headers, junk rows) before values (spaces, case, codes).
1–3. Trim, collapse inner spaces, fix case
let
Customers = #table({"Name", "Email"}, {{" ana RUIZ ", "Ana@Kites.Example "}, {"ben okafor", " ben@reels.example"}}),
Collapse = (t as text) as text => Text.Combine(List.Select(Text.Split(Text.Trim(t), " "), each _ <> ""), " "),
Clean = Table.TransformColumns(Customers, {{"Name", each Text.Proper(Collapse(_)), type text}, {"Email", each Text.Lower(Text.Trim(_)), type text}})
in
CleanResult
| Name | |
|---|---|
| Ana Ruiz | ana@kites.example |
| Ben Okafor | ben@reels.example |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
4. Standardise 'missing' to null
Table.TransformColumns(
#table({"Phone"}, {{"0161 000 111"}, {""}, {"n/a"}, {null}, {" "}}),
{{"Phone", each if _ = null or List.Contains({"", "n/a", "-"}, Text.Lower(Text.Trim(_))) then null else _, type text}}
)Result
| Phone |
|---|
| 0161 000 111 |
| null |
| null |
| null |
| null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
5. Remove fully blank rows
Table.SelectRows(
#table({"A", "B"}, {{"x", 1}, {null, null}, {"", null}, {null, 2}}),
each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
)Result
| A | B |
|---|---|
| x | 1 |
| null | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
6. Map codes with a small table
let
Orders = #table({"OrderID", "Ch"}, {{"A-1", "W"}, {"A-2", "S"}, {"A-3", "X"}}),
Map = #table({"Ch", "Channel"}, {{"W", "Web"}, {"S", "Store"}}),
Joined = Table.ExpandTableColumn(Table.NestedJoin(Orders, {"Ch"}, Map, {"Ch"}, "M", JoinKind.LeftOuter), "M", {"Channel"}),
Defaulted = Table.TransformColumns(Joined, {{"Channel", each _ ?? "Unknown", type text}})
in
DefaultedResult
| OrderID | Ch | Channel |
|---|---|---|
| A-1 | W | Web |
| A-2 | S | Store |
| A-3 | X | Unknown |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
?? returns the right-hand value when the left is null. Unknown codes are visible, not dropped.7. Keep only digits (for phone numbers or IDs)
Text.Select("+44 (0)161-000-111", {"0".."9"})Result
440161000111
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
8. Remove exact duplicate rows, then duplicate keys
let
Signups = #table({"Email", "Source"}, {{"ana@kites.example", "web"}, {"ana@kites.example", "web"}, {"ana@kites.example", "event"}, {"ben@reels.example", "web"}}),
ExactDupes = Table.Distinct(Signups),
OnePerEmail = Table.Distinct(ExactDupes, {"Email"})
in
#table({"Step", "Rows"}, {{"Raw", Table.RowCount(Signups)}, {"Exact duplicates removed", Table.RowCount(ExactDupes)}, {"One per email", Table.RowCount(OnePerEmail)}})Result
| Step | Rows |
|---|---|
| Raw | 4 |
| Exact duplicates removed | 3 |
| One per email | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
A word on order. Fix structure first (banner rows, headers, blank rows), then values (spaces, case, codes), then types. Typing before cleaning turns fixable text like "1,200 " into errors, and deduplicating before trimming leaves near-duplicates behind.
9–10. Remove junk rows by content, then type
Filter out header repeats, totals and test records by what they contain (each [OrderID] <> "OrderID" and not Text.StartsWith([OrderID], "TEST")), then set types once at the end of the cleaning block, with the source culture.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.
- beginner~8 minPro
Clean names and build a full name (logistics)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- intermediate~7 minFree
Treat empty text and null the same (hardware)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- beginner~6 minPro
Keep one signup per email address (newsletter)
Build a mailing list with one row per email address from signups that contain repeats.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Replace values and errors (Concept, Microsoft Learn)
- Split columns by delimiter (Concept, Microsoft Learn)
- Working with duplicate values (Concept, Microsoft Learn)
- Using the data profiling tools (Concept, Microsoft Learn)
- Text.Combine (Function reference, Microsoft Learn)
- List.Select (Function reference, Microsoft Learn)
- Text.Split (Function reference, Microsoft Learn)
- Text.Trim (Function reference, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
- Text.Proper (Function reference, Microsoft Learn)
- Text.Lower (Function reference, Microsoft Learn)
- List.Contains (Function reference, Microsoft Learn)
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.