Lesson 1 of 15 · Concept
What is data preparation?
Data preparation turns the files you were given into a table that can answer a stated question, using rules someone else can check. It usually means fixing values, choosing rows, combining tables and reshaping the result. It's what you do to an export before a PivotTable or a chart, written down.
The usual steps
- Inspect. What does one row stand for, and what's in each column?
- Correct values. Parse numbers and dates, trim text and decide about blanks.
- Choose rows. Remove duplicates and keep the rows the question covers.
- Combine and reshape. Add columns from other tables, then total or pivot.
- Check. Compare row counts and totals with what you expected.
Take a sales export with four invoices of 120, 80, 160 and a pending 40. A report on booked sales keeps three rows and totals 360, while a pipeline report keeps all four and totals 400. The same file gives two right answers, so the question comes first.
In Excel, this work usually happens in Power Query or in helper columns. In SQL, it's a chain of SELECT statements or views using WHERE, JOIN and GROUP BY. Do it whenever a number will be reused or questioned, and when a file will arrive again next month. For a one-off look at a clean table, you may not need it.
Three mistakes are common. People start cleaning before they've stated the question. They edit the source by hand, so nobody can repeat the work. And they trust a matching total, when the wrong rows can add up to the right number.
Worked example: two reports that disagree
Fictional worked example. Vandelay Coffee Supply, its people and its figures are fictional.
It's your first week at Vandelay, a small supplier of coffee equipment and beans to hospitality businesses. The founder, Max Thrust, has budget for one more account manager and must choose North or South by Friday. Two Q3 reports reached him this morning.
| Don Tremendous, Sales | Sydney Swell, Finance | |
|---|---|---|
| Q3 total | €15,850 | €10,168 |
| North | €7,720 | €5,578 |
| South | €8,130 | €4,590 |
| Says | The hire goes South | The hire goes North |
Max forwards both to you. "One of these is wrong. Tell me which."
Start from the raw files
Both reports began with the same two invoice exports. Sydney added the customer register, and Don his old territory map. Here are five invoice rows exactly as exported. In these files, 1.200 means €1,200.
| invoice_id | invoice_date | status | amount |
|---|---|---|---|
| INV-1001 | 30/06/2026 | booked | 1.200 |
| INV-1003 | 06/07/2026 | Booked | 860 |
| INV-1008 | 28/08/2026 | pending | 540 |
| INV-1008 | 28/08/2026 | booked | 540 |
| INV-1014 | 16/09/2026 | booked | TBC |
How many of these rows would change a Q3 total?
All five, for different reasons. INV-1001 is dated June, in a Q3 report. INV-1003 has a capital letter, so an exact match on booked misses it. INV-1008 appears twice and the copies disagree. INV-1014 has no price. And software may read 1.200 as 1.2.
Follow along in Strata, Oplema's browser app for data prep, or any tool with the raw July-August invoices, September invoices and customer register.
Say what the table is for
Max's question sets the target, so write that down before you clean anything.
One row per booked invoice dated in Q3, with its price, customer and region.
That line puts INV-1001 out, puts pending invoices out, says INV-1008 belongs once and tells you the register is needed. It doesn't say what to do about INV-1014. That's a decision you still owe someone. A cash report would want different rows from the same files, which is why no file is clean in general, only ready for a particular question.
Check more than the total
Later in this course, one step keeps the row count exactly right while losing two invoices and doubling two others. How each step changes a table shows the habit that catches it.
In Strata, each rule is a block on a canvas, so the work stays visible and you can change it. The course adds one rule per lesson, and each lesson opens with its data in place, in your browser and with no account.