❌
"ERROR" values
Text sitting inside number columns, so totals and averages break.
← Back to all projects
Excel · Power Query · Data cleaning
Cleaning Messy Cafe Data
A cafe sales file full of "ERROR", "UNKNOWN" and blanks. I fixed it logically, recovering values instead of deleting rows.
The problem
Data you can't calculate with
❓
"UNKNOWN" values
A placeholder pretending to be data in item, payment and location columns.
⬜
Blank cells
Missing items, quantities, prices and totals.
See it
Before and after
Real rows from the file. Switch between the messy and cleaned versions.
| Transaction | Item | Qty | Price | Total | Payment | Location |
|---|---|---|---|---|---|---|
| TXN_4271903 | Cookie | 4 | 1 | ERROR | Credit Card | In-store |
| TXN_7034554 | Salad | 2 | 5 | 10 | UNKNOWN | UNKNOWN |
| TXN_4433211 | UNKNOWN | 3 | 3 | 9 | ERROR | Takeaway |
| TXN_8915701 | ERROR | 2 | 1.5 | 3 | (blank) | In-store |
| TXN_3522028 | Smoothie | ERROR | 4 | 20 | Cash | In-store |
| Transaction | Item | Qty | Price | Total | Payment | Location |
|---|---|---|---|---|---|---|
| TXN_4271903 | Cookie | 4 | 1 | 4 | Credit Card | In-store |
| TXN_7034554 | Salad | 2 | 5 | 10 | (blank) | (blank) |
| TXN_4433211 | Cake/Juice | 3 | 3 | 9 | (blank) | Takeaway |
| TXN_8915701 | Tea | 2 | 1.5 | 3 | (blank) | In-store |
| TXN_3522028 | Smoothie | 5 | 4 | 20 | Cash | In-store |
Why "Cake/Juice"? Both cost $3, so the price alone can't tell them apart. I labelled it honestly instead of guessing.
Walkthrough
8 steps in Power Query
The order matters: first standardise what "missing" means, then recover values with simple logic.
Replace "UNKNOWN" with blanks
Done across all columns.
Why: UNKNOWN isn't real data, so it's treated as missing.
Replace "ERROR" with blanks
Error values can't be used in calculations.
Why: they have to be cleaned before anything else.
Convert empty text to real blanks
Empty strings become true null values.
Why: missing values are now consistent everywhere.
Fix data types
Numbers stored as text become numbers.
Why: so calculations actually work.
Fill missing price
Price = Total ÷ Quantity, or the item's standard price.
Why: recovers values intelligently.
Calculate missing total
Total = Quantity × Price.
Why: basic maths restores the missing totals.
Recover missing item from price
Each item has a typical price, so price points to the item.
Why: where two items share a price, the label shows both (for example Cake/Juice).
Calculate missing quantity
Quantity = Total ÷ Price.
Why: the final step that completes the dataset.
Clean→ Standardise→ Recalculate→ Recover
No rows deleted. Everything fixed logically.
Results
Column quality: before vs after
Reduction in problem cells per column.
Items333 → 6 · 98.2% fewer issues
Quantity479 → 23 · 95.2% fewer issues
Price per unit533 → 6 · 98.9% fewer issues
Total spent502 → 23 · 95.4% fewer issues
Where the problems were
| Column | Blank | Unknown | Error |
|---|---|---|---|
| Quantity | 138 | 171 | 170 |
| Price per unit | 179 | 164 | 190 |
| Total spent | 173 | 165 | 164 |
Because quantity, price and total depend on each other, one known value can often rebuild another.
Project files