All lessons

Module 2: Power Query: get and clean data · Lesson 2.2 · about 16 min

Cleaning: types, blanks, trims and splits

The cleaning steps you'll use on almost every file, what each one does to the data, and the mistakes that make refreshes fail.

Module 2 · about 16 minutes · read first: 2.1. You’ll be able to pick the right cleaning step for each problem and avoid the ones that break refreshes.

1. Why this matters

Most of a BI developer’s time goes on getting data right, not on charts. These steps fix 90 % of what you’ll meet.

2. The steps in one picture

Try it: Cleaning a till export from one store

The data as it arrives. Click the steps in order to clean it.

MSource = Csv.Document(File.Contents("till-export.csv"))

Each step works on the result of the one before, and Power Query replays them all on every refresh, so next month's file gets the same cleaning. Highlighted: new or changed. Faded with a line through: removed by this step.

3. The cleaning toolkit

Problem Step (where to find it) What it does
First row holds the headers Home > Use first row as headers Promotes the row to column names
Spaces at the start or end Transform > Format > Trim " falls centre " → "falls centre"
Hidden characters (line breaks) Transform > Format > Clean Removes non-printing characters
Mixed case Format > Capitalize Each Word / UPPERCASE Makes spellings match
Text instead of numbers or dates Data type icon on the column header Converts; bad values become Error
Day/month order Data type > Using locale… Reads dates with the right country rules
“N/A”, ”-”, ”?” Transform > Replace values Replace with null
Error cells Right-click > Replace errors or Remove errors Deal with failed conversions
Empty rows Home > Remove rows > Remove blank rows Drops rows that are all blank
Totals, subtotals, notes Column filter arrow > untick, or Text filters Keeps only real data rows
Two facts in one column Transform > Split column > By delimiter "Desk lamp - Large" → two columns
Same row twice Home > Remove rows > Remove duplicates Keeps the first of each identical row (on the selected columns)
A new calculated value Add column > Custom column e.g. [Qty] * [Price] row by row

4. Types: the part that breaks refreshes

Power Query guesses types when it loads a file (“Changed Type” step). Guesses go wrong: product codes like 00123 become the number 123, and a column that was numbers last month gets “N/A” this month. Rules:

  • Set types yourself, after cleaning, in one step you’ve checked.
  • Keep codes and IDs that start with 0 as text.
  • Use Fixed decimal number (currency) for money: it avoids floating-point rounding surprises.
  • After a type change, check View > Column quality for Errors and Empty percentages.
Principle

Principle: clean data has one meaning per column, one type per column, one spelling per value and one row per event. Each step in the table above restores one of those four.

Try it: which of the four does “Remove duplicates” restore? (One row per event.) And “Capitalize Each Word”? (One spelling per value.)

5. Worked example

Northbay’s Rideau store sends a file with “Qty” values 2, 1, N/A, 3. Change type to Whole number: the N/A cell becomes Error. Replace errors with null. Now SUM of Qty is 6 and COUNT is 3, and a data quality card can show “1 line missing quantity”, which you report to the store instead of hiding it.

6. Practice

  1. Which step fixes “Queen Street”, “QUEEN STREET” and “queen street ” so they group together? (Two steps.)
  2. A column of postal codes became numbers and lost letters. What went wrong and how do you fix it?
  3. When should you use Remove duplicates, and when is it dangerous?
Answers
  1. Trim, then Capitalize Each Word.
  2. The type was guessed wrongly (or set to number). Remove that Changed Type step or set the column to Text.
  3. Use it when an export repeats identical rows by mistake. It is dangerous on a fact table where two identical order lines really happened (same product, same day, same quantity): you’d delete a real sale. Remove duplicates on a key column instead, if one exists.

Check yourself

1A Qty column holds "2", "1" and "N/A". What happens when you change its type to Whole number?
2"03/01/2025" should be 3 January. What controls how Power Query reads it?
3Why is null better than 0 for a missing quantity?

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.