Practice and Answer Key: Data Cleaning in Power Query

Suppose we are cleaning a Blinkit Pune export before the March review. Here are five raw rows (fictional practice data). The blank City cell is a real empty cell (it shows null in Power Query).

About this data: This table uses columns: Order ID, City, Order Date, Amount. This table uses columns: What you see, Why, Fix. Values shown are the lesson examples already on this page (not a new dataset).

(␣ = an extra space.)

Exercise (one under the other):

1. Type the rows in Excel, save, and load with Get data › Excel workbook › Transform Data.
2. Order ID: Transform › Format › Trim, then UPPERCASE.
3. City: Transform › Format › Trim, then Capitalize Each Word.
4. City: Transform › Fill › Down.
5. Amount: Replace Values ₹ → nothing, then , → nothing. Change type to Fixed decimal number.
6. Order Date: right-click › Change Type › Using Locale… › Date, English (India).
7. Select Order ID › Home › Remove Rows › Remove Duplicates.

Expected result (check your numbers):

4 rows. Total = 1,250 + 980 + 400 + 2,100 = ₹4,730 (Pune ₹2,630, Nashik ₹2,100). Before cleaning, the same data would add up to ₹5,980 because BLK-301 is counted twice.

let
    Source   = Excel.Workbook(File.Contents("D:\Data\pune_raw.xlsx"), null, true),
    Sheet    = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    Headers  = Table.PromoteHeaders(Sheet, [PromoteAllScalars = true]),
    Texts    = Table.TransformColumns(Headers, {
                 {"Order ID", each Text.Upper(Text.Trim(_)), type text},
                 {"City",     each Text.Proper(Text.Trim(_)), type text}}),
    Filled   = Table.FillDown(Texts, {"City"}),
    NoRupee  = Table.ReplaceValue(Filled, "₹", "", Replacer.ReplaceText, {"Amount"}),
    NoComma  = Table.ReplaceValue(NoRupee, ",", "", Replacer.ReplaceText, {"Amount"}),
    Typed    = Table.TransformColumnTypes(NoComma, {{"Amount", Currency.Type}}),
    Dated    = Table.TransformColumnTypes(Typed, {{"Order Date", type date}}, "en-IN"),
    Distinct = Table.Distinct(Dated, {"Order ID"})
in
    Distinct

Common mistakes:

Samajla ka? Clean the key first, then remove duplicates. The order of Applied Steps is part of the answer.