Webbo3 Power BI · Cohort 1 · Day 4 of 16 · Lab
Day 4 Lab: Cleaning Sales_2023 Together
One query, built as a class from the raw sheet to the clean table, with a checkpoint after every step.
By the end of today you can
- Build the complete Sales_2023 cleaning query without notes
- Check your work step by step against row counts and distinct counts
- Recognise the seven mistakes this class makes most, from their symptoms
- Read Applied Steps to find which step went wrong
- Start Assignment 1 knowing exactly what is expected
A lab day is a working day. Your tutor drives on the projector, you build the same query on your own laptop, and the class stops at every checkpoint until every laptop agrees.
Start from nothing: a new Power BI file, Get Data, Excel workbook, the Webbo Oil workbook, tick Sales_2023 only, Transform Data. Delete Power BI's Changed Type step. By the end, your Applied Steps will match the checkpoint table below exactly.
1. How the Lab Works
Definition
A checkpoint is a number you can see on your own screen that proves a step worked: the row count at the bottom left of the preview, or the distinct count in Column profile. If your number differs from the class number, you stop and fix it before the next step, because every later step builds on this one.
Real-Life Example
A driving instructor who checks your mirrors before you move, your signal before you turn, and your speed before the bend. Catching a mistake at the step it happens is easy. Finding it three steps later is not.
Types: The Three Numbers to Watch
| Number | Where you see it | What it proves |
|---|---|---|
| Row count | Bottom left of the preview: "9 COLUMNS, 3,189 ROWS" | Rows were (or were not) removed |
| Distinct count | Column profile pane, with profiling on the entire data set | Spellings were (or were not) merged |
| Column quality | Under each header: Valid, Error, Empty | Types converted with no errors |
View tab: tick Column quality, Column distribution and Column profile. Bottom of the window: switch profiling to "based on entire data set". Without this the counts are for the first 1,000 rows only and will not match the class.
2. The Build, With Checkpoints
Step by Step
The class build
Reading Applied Steps
Applied Steps is the history of your query. Click any step and the preview shows the data exactly as it was at that moment, with its row count at the bottom. That is how you find a mistake: click down the steps one at a time and watch for the first number that differs from the class.
Click the first step, then each step below it, and write down the row count each time. Your list should match the Rows column above, number for number.
Picture the driving instructor: mirrors, signal, speed, one at a time, before you move on.
3. Fixing Mistakes as a Class
Definition
A symptom is the wrong number you notice; the cause is the step that made it. The skill today is working backwards from one to the other. Each mistake below was made on the real sheet, and the symptom shown is exactly what you would see.
Real-Life Example
A doctor does not start with "which organ is broken?" She starts with "where does it hurt?" and works back. A row count that is too high is where it hurts.
Types: The Seven Mistakes
| Symptom on your screen | Cause | Fix |
|---|---|---|
| StationName shows 21 values, not 7 | Capitalize Each Word was skipped | Add it straight after Trim |
| StationName shows 7 values, but one is "Gra Filling Station" | The five Replace Values steps were skipped | Add them after Capitalize Each Word. On Day 5 "Gra" would match nothing in Station_Master |
| Only 2,608 rows after the Date step | Date typed with English (United States), then "Remove Errors" used to hide the errors | Delete both steps; Change Type, Using Locale, English (United Kingdom) |
| Only 7 rows after Remove Duplicates | Only StationName was selected when you clicked Remove Duplicates | Delete the step; Ctrl+A to select every column; do it again |
| 3,163 rows at the end, not 3,116 | Remove Duplicates was skipped | Add it before the Quantity filter |
| 3,124 rows at the end | The blank dates were kept | Date dropdown, untick (null) |
| 320 empty attendant cells at the end | null was not replaced with Unmanned | AttendantName, Replace Values, null, Unmanned |
Keeping the duplicates overstates revenue by ₦608,367. That is money the business never received, reported as if it had.
Remove Errors deletes whole rows. On a Date column read with the wrong locale it deletes 581 real sales, and the rows it keeps still include 326 dates with day and month swapped. An error is information: find out why it happened.
312 clean 2023 sales, ₦5,745,847 of revenue, came from unmanned pumps. Delete those rows and every station looks poorer than it is.
Picture the doctor: start with where it hurts (the number), work back to the cause (the step).
4. Assignment 1 Is Released
You have now built the query live. Assignment 1 asks you to do the same to Sales_2024, alone, by Day 6. The sheet has the same columns and the same kinds of mess, and different rows.
The full brief, the hand-in and the marking are on the Assignment 1 page. It is the next lesson in this course.
Do This Now
- Build the query from the first step to the Quantity filter, stopping at every checkpoint.
- Click down Applied Steps and check each row count against the table.
- Break it on purpose: delete the five Replace Values steps and read the StationName values. Undo it.
- Rename the query Sales_2023_Clean and save your .pbix.
- Read the Assignment 1 brief.
Checkpoint
Q1. Your final row count is 3,163. Which step is missing?
Remove Duplicates. The zero and negative quantities are gone, but the rows submitted twice are not.
Q2. Your stations look right but one reads "Gra Filling Station". Does it matter?
Yes. On Day 5 you merge on StationName, and "Gra" matches nothing in Station_Master, so every GRA sale loses its city and region.
Q3. Why not just use Remove Errors on the Date column?
It deletes real sales and hides the actual problem, the locale. Fix the locale and there are no errors to remove.
Q4. Why replace empty attendants instead of filtering them out?
They are real sales from unmanned pumps. Removing them would understate revenue.
Tick Before You Move On
- ☐ My Applied Steps match the class checkpoints, step by step
- ☐ I finish with 3,116 rows, 7 stations, 5 products and 3 payment methods
- ☐ There are no empty attendant cells: they say Unmanned
- ☐ I can name the cause of each of the seven symptoms
- ☐ I have read the Assignment 1 brief
Quick Recap
Check a number after every step: row count, distinct count, column quality. Symptom first, then cause: a wrong count points at one step. Remove Errors hides problems and deletes real rows. Capitalize Each Word needs the five abbreviation fixes after it. Assignment 1 is the same recipe on Sales_2024, alone.
Final Scenario
At the end of the lab a classmate's total is higher than everyone else's. She is sure her query is right, because it has no errors.
You ask for her row count. It is 3,163. In ten seconds you both know Remove Duplicates is missing, and that her report would have shown Webbo Oil ₦608,367 richer than it is.
No errors is not the same as right. Checkpoints are how you know.
Next: the Assignment 1 brief, then Day 5, appending and merging queries.