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
The data as it arrives. Click the steps in order to clean it.
MSource = Csv.Document(File.Contents("till-export.csv"))
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
- Which step fixes “Queen Street”, “QUEEN STREET” and “queen street ” so they group together? (Two steps.)
- A column of postal codes became numbers and lost letters. What went wrong and how do you fix it?
- When should you use Remove duplicates, and when is it dangerous?
Answers
- Trim, then Capitalize Each Word.
- The type was guessed wrongly (or set to number). Remove that Changed Type step or set the column to Text.
- 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
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.