Webbo3 Power BI · Cohort 1 · Day 2 of 16
Day 2: The Power Query Editor and Data Cleaning
Seven station managers typed this sheet seven different ways. Today you make it one clean table.
By the end of today you can
- Find your way round the Power Query Editor and read Applied Steps
- Use column profiling to see problems before fixing them
- Set data types yourself, and read three date formats with the right locale
- Clean names with Trim, Capitalize Each Word and Replace Values
- Fill blanks, remove duplicates and filter out impossible rows
Yesterday you saw the mess. Today you clean Sales_2023 step by step, and every step is a click, not a line of code.
Open your Day 1 file and click Transform Data. Everything today happens in the Power Query Editor, on the Sales_2023 query.
1. The Power Query Editor
Definition
The Power Query Editor is the kitchen of Power BI: a separate window where you clean and shape data before it reaches the report. Nothing you do here changes the workbook. Every change is recorded as a step, and the steps run again, in order, every time you refresh.
Real-Life Example
A recipe card. You do not wash the same vegetables by hand every day; you write the method down once, and anyone can follow it tomorrow with fresh vegetables. Applied Steps is that recipe card.
Types: The Parts of the Window
| Part | Where | What it is for |
|---|---|---|
| Queries pane | Left | Every table you brought in, one query each |
| Preview | Middle | The first 1,000 rows of the selected query, after the selected step |
| Applied Steps | Right, under Query Settings | The recipe: every change, in order. Click one to see the data as it was then |
| Formula bar | Above the preview | The selected step written out. You can ignore it in this course |
| Ribbon | Top | Home, Transform and Add Column hold every button you need |
Click the X beside a step to delete it. Deleting a step in the middle can break the steps after it, so Power BI asks first. Delete from the bottom up when you can.
Column Profiling: Seeing Problems Before Fixing Them
Click path
Column distribution shows, under each header, how many distinct values the column has. For a column like StationName, which should hold seven stations, that number is your first warning.
Picture the recipe card pinned above the sink: the method written once, followed every time.
2. Data Types, and a Date Column Typed Three Ways
Definition
A column's data type tells Power BI what kind of value it holds, which decides what it can do with it. Text can be searched but not added. A number can be added. A date can be sorted by time and split into years and months. The badge on each column header shows its current type.
Real-Life Example
A bank teller sorts what you hand over: notes go in the counter, cheques to clearing, forms to the desk. Hand them a cheque as if it were cash and nothing works. A date stored as text is that cheque in the note counter.
Types: The Ones You Will Use
| Type | Badge | Use it for | In Sales_2023 |
|---|---|---|---|
| Text | ABC | Names, codes, IDs | TransactionID, StationName, ProductName, AttendantName, PaymentMethod |
| Decimal Number | 1.2 | Anything that can have a fraction | Quantity (9.7 litres), UnitPrice_NGN, TotalAmount_NGN |
| Whole Number | 1²3 | Counts with no fraction | Not needed today |
| Date | DATE | Calendar days | Date |
| Date/Time | D/T | A day and a time | Not in this dataset |
The Date Problem
Each station manager typed the date the way their own phone or laptop does. The Date column holds three formats, and some rows have no date at all:
Year first and in-words dates can only mean one thing. Day first is the dangerous one, because 05/06/2023 is 5 June to a Nigerian or British reader and May 6 to an American one. Power BI reads dates using your laptop's region unless you tell it otherwise.
The errors are the good news: you can see them. The 326 silently wrong dates are the disaster, because nothing looks broken. A June sale lands in May and every monthly total is wrong. The fix is to tell Power BI which country's rules to use.
Setting the Types Yourself
Changed Type with Locale
English (United Kingdom) reads day first, and it also understands the year-first and in-words formats. Check the column quality bar under Date: it should show 0% Error and 0.3% Empty, which is the 8 rows with no date.
Click one Error cell. The panel at the bottom shows the text that failed. Almost always the step was set to English (United States) by mistake. Delete the step and do it again with the right locale; never just remove the error rows.
Picture the teller who asks where a cheque is from before she reads the date on it.
3. Cleaning Names: Trim, Capitalize Each Word and Replace Values
Definition
Three buttons fix almost every badly typed name. Trim removes spaces at the start and end of each cell. Capitalize Each Word makes the first letter of every word capital and the rest lower case. Replace Values swaps one exact value for another.
Real-Life Example
Name tags at an event, written by seven different people. One wrote in capitals, one in small letters, one left a gap before the name. Before you can sort them into a register, someone has to rewrite them the same way.
Types: The Three Fixes
| Fix | Where | What it changes | Example |
|---|---|---|---|
| Trim | Transform, Format, Trim | Spaces at the start and end only | " Wuse Filling Station " becomes "Wuse Filling Station" |
| Capitalize Each Word | Transform, Format | Every word: capital first letter, rest lower case | "WUSE FILLING STATION" becomes "Wuse Filling Station" |
| Replace Values | Transform, Replace Values | One exact value into another | "Pos" becomes "POS" |
Step 1: Trim
Click path
Step 2: Capitalize Each Word
Click path
The counts are right, but look at the actual values. Capitalize Each Word does not know which words are abbreviations:
Step 3: Replace Values, Five Times
GRA, PMS, AGO, DPK and POS are abbreviations, so they should stay in capitals. One Replace Values step fixes each one. Tick Match entire cell contents, or "Pos" would also be replaced inside any longer word that happened to contain it.
Click path
| Column | Value To Find | Replace With |
|---|---|---|
| StationName | Gra Filling Station | GRA Filling Station |
| ProductName | Pms (Petrol) | PMS (Petrol) |
| ProductName | Ago (Diesel) | AGO (Diesel) |
| ProductName | Dpk (Kerosene) | DPK (Kerosene) |
| PaymentMethod | Pos | POS |
After the five steps, every name is spelled exactly as it is on the master sheets. That matters more than it looks: on Day 5 you join these names to Station_Master and Product_Master, and a join only matches names that are identical, letter for letter.
Picture the seven name tags rewritten in one hand, with the abbreviations still in capitals.
4. Blank Attendants: Replacing null
Definition
An empty cell in Power Query is null: no value at all, which is different from a space or a zero. Replace Values can put a word in its place.
Real-Life Example
320 sales in 2023 have no attendant name. They are not mistakes: some pumps run unmanned or self-service. Leaving the cell empty makes those sales disappear from any "by attendant" chart; giving them a name keeps them counted.
Click path
Type the word null exactly, in lower case. It is Power Query's name for an empty cell.
5. Removing Rows That Cannot Be Used
Definition
Some rows have to go: rows with no date cannot be placed in any month, rows entered twice would be counted twice, and a quantity of zero or less is not a sale. Each gets its own step.
Real-Life Example
Counting the day's receipts. A receipt with no date goes to the manager, a photocopy of a receipt already counted goes in the bin, and a refund slip is not a sale.
Types: Three Ways Rows Are Removed
| Problem | Button | Rows removed from Sales_2023 |
|---|---|---|
| No date | Date header dropdown, untick (null) | 8 |
| The same log submitted twice | Home, Remove Rows, Remove Duplicates (with every column selected) | 47 |
| Quantity zero or negative | Quantity dropdown, Number Filters, Greater Than 0 | 18 |
Duplicates
Select every column first (Ctrl+A in the preview) so Remove Duplicates compares whole rows. A duplicate here means the same log was submitted twice, not two real sales that happened to look alike, because every sale has its own TransactionID.
Impossible Quantities
Nobody sells minus 26 litres. These are refunds or typing slips, and a sales report leaves them out. Write the number removed in your notes, because the Managing Director may ask what happened to them.
Picture the three piles on the desk: undated to the manager, copies in the bin, refunds kept apart.
6. The Whole Recipe
Here is every step, in order, with the rows left after each one. Your Applied Steps list should read the same, and every row count should match.
In Power BI each Replace Values is its own step, so your list will show "Replaced Value" five times, then "Replaced Value1", "Replaced Value2" and so on. That is fine. The recipe above groups them on one line to keep it short.
Do This Now
- Turn on column quality, distribution and profile, based on the entire data set.
- Delete Power BI's Changed Type step and set every type yourself, Date with English (United Kingdom).
- Trim and Capitalize Each Word on the four name columns.
- Add the five Replace Values steps for GRA, PMS, AGO, DPK and POS.
- Replace null in AttendantName with Unmanned.
- Remove blank dates, duplicates and quantities of zero or less.
- Check you have 3,116 rows, and save.
Checkpoint
Q1. Why set the Date type with a locale instead of just choosing Date?
Because day-first dates are read with the laptop's region by default. With United States rules, 326 dates would be silently wrong. English (United Kingdom) reads them correctly.
Q2. Trim fixed the spaces. Why was Capitalize Each Word still needed?
Trim only removes spaces at the ends. It does nothing about "WUSE" against "Wuse".
Q3. Why do GRA, PMS, AGO, DPK and POS need Replace Values afterwards?
They are abbreviations. Capitalize Each Word turns them into Gra, Pms, Ago, Dpk and Pos, which no longer match the master sheets.
Q4. Why replace empty attendants with Unmanned instead of removing the rows?
Because they are real sales from unmanned pumps. Removing them would make revenue too low.
Tick Before You Move On
- ☐ Date has 0% errors and was read with English (United Kingdom)
- ☐ StationName has 7 values, ProductName 5, PaymentMethod 3, AttendantName 18 with Unmanned
- ☐ GRA, PMS, AGO, DPK and POS are in capitals
- ☐ Blank dates, duplicates and zero or negative quantities are gone
- ☐ Sales_2023 has 3,116 rows
Quick Recap
The Power Query Editor records every change as a step that runs again on refresh. Column profiling shows the problems before you fix them. Set types yourself; read dates with the locale they were written in. Trim, then Capitalize Each Word, then Replace Values for the abbreviations. Replace null with a word when the empty cell means something; remove rows only when they are not real sales.
Final Scenario
Alhaji Musa Bello's accountant says the June 2023 figures look far too low compared with his own.
You open Applied Steps and show him the Date step, set to English (United Kingdom). Without it, 326 sales would have moved to the wrong month and June would have lost some of its days to May. He goes back and checks his own spreadsheet instead.
A clean query is one you can defend, step by step.
Next, Day 3: transforming data: custom and conditional columns, dates broken into parts, Split Column, Group By and Unpivot.