Data cleaning in Power Query

01The problem

Kolanut's customer list exported from its old system has stray spaces, random capitals, 23 spellings of six regions, three date formats, money stored as text and 12 customers listed twice. In Excel you'd clean it with formulas. In Power Query you clean it with steps, and when next month's export arrives with the same problems, one refresh cleans it again.

02The concept

Text cleaning (select column → Transform → Format):

Replace Values (Transform → Replace Values) swaps one value for another in a column: SW → South West. For many variants, it's cleaner to lower-case and trim first, which collapses Lagos, LAGOS and lagos into one, then replace what's left.

Types with a locale. Right-click a column → Change Type → Using Locale… Choose the type and the locale the data was written in. English (United Kingdom) reads 01/09/2022 as 1 September. The same step handles ISO dates like 2023-07-11 and text like 5-Mar-2024.

Errors. When a type change fails on some rows, those cells show Error. Right-click the column → Replace Errors, or better, find out why first with Keep Rows → Keep Errors.

Removing duplicates is case-sensitive in Power Query. Ada Superstore and ADA SUPERSTORE are different to it (Excel would treat them as the same). So fix case and spaces before Home → Remove Rows → Remove Duplicates.

03Example

Credit limits like ₦2,050,000, 1,200,000, 850000 and 1,400,000.00:

1. Replace Values: ₦ → (nothing).
2. Replace Values: , → (nothing).
3. Change Type → Decimal Number (it reads 1400000.00 fine), or Whole Number after removing .00.
4. Blanks become null, which is correct: the limit is unknown.

04Walkthrough

1. Get data → Text/CSV → customer_list_raw.csv → Transform Data.

2. Rename the query customers_clean.

3. Customer Name: Transform → Format → Trim, then Clean, then Capitalize Each Word.

4. Region: Format → Trim, then lowercase. Now Replace Values for each remaining variant, lower-case to proper name:

   • lagos → Lagos
   • south west, south-west, sw → South West
   • and the same for South East (se), South South (ss), North Central (nc), North West (nw).

   Check with the column's filter drop-down: exactly six values should remain.

5. City: Trim, Capitalize Each Word.

6. Date Joined: right-click → Change Type → Using Locale → Date, English (United Kingdom).

7. Credit Limit: Replace ₦ and , with nothing, then Change Type → Decimal Number.

8. Select Customer Name → Home → Remove Rows → Remove Duplicates.

9. Check the row count at the bottom of the editor, then Close & Apply.

Look at Applied Steps: that list is your cleaning log.

05Practice

06Check your understanding