Power QueryExercises

Power Query data cleaning recipes for messy exports

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

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
    Clean

Result

NameEmail
Ana Ruizana@kites.example
Ben Okaforben@reels.example

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

Names in proper case; emails lowercased so they compare and deduplicate reliably.

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

AB
x1
null2

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
    Defaulted

Result

OrderIDChChannel
A-1WWeb
A-2SStore
A-3XUnknown

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

StepRows
Raw4
Exact duplicates removed3
One per email2

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

Which of Ana's two remaining rows survives isn't guaranteed; when it matters, choose by a rule (see the lesson on survivor rules).

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.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

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.