Webbo3 Power BI · Cohort 1 · Assignment 1 · Released Day 4 · Due Day 6
Assignment 1: Clean Sales_2024 on Your Own
The steps you built with the class on Sales_2023, done alone on a sheet you have not seen cleaned.
By the end of today you can
- Bring Sales_2024 in from the Webbo Oil workbook as its own query
- Read three date formats correctly, and clean four name columns
- Replace empty attendants, and remove blank dates, duplicates and impossible quantities
- Prove each fix with a row count or a distinct count
- Hand in a .pbix and a short step log that a tutor can check
On Day 4 the class built the cleaning together. This is the same recipe, on the 2024 sheet, with nobody driving the projector.
Sales_2024 has the same nine columns and the same kinds of mess as Sales_2023, but different rows and different amounts of each problem. Copying last week's numbers will not work; repeating last week's steps will.
The Brief
Clean Sales_2024 on your own, using the steps built in class on Sales_2023: read the dates with the right locale, clean the station, product, attendant and payment names so they match the master sheets, mark unmanned sales, and remove blank dates, duplicates and zero or negative quantities.
The file
Download all as one zip file (webbo-oil-stations.zip)
- PowerBI_WebboOil_Stations.xlsx : the Sales_2024 sheet is inside it
What You Must Do
| # | Task | Done when |
|---|---|---|
| 1 | In your Day 4 file: Get Data, Excel workbook, tick Sales_2024, Transform Data. Rename the query Sales_2024_Clean | Sales_2023_Clean and Sales_2024_Clean are both in the Queries pane |
| 2 | Delete Power BI's Changed Type. Set Date with Using Locale, English (United Kingdom); the three amount columns to Decimal Number | Date shows the DATE badge and 0% errors |
| 3 | Trim and Capitalize Each Word on StationName, ProductName, AttendantName and PaymentMethod | StationName shows 7 values, ProductName 5, PaymentMethod 3 |
| 4 | The five Replace Values fixes: GRA, PMS, AGO, DPK and POS | Every name is spelled as on the master sheets |
| 5 | Replace null in AttendantName with Unmanned | No empty attendant cells remain |
| 6 | Remove rows with a blank Date | Date shows 0% empty |
| 7 | Remove duplicates across all columns | The row count drops, and you know by how much |
| 8 | Keep only Quantity greater than 0 | Quantity's minimum in Column profile is above 0 |
Do not add 2024's rows to Sales_2023_Clean. On Day 5 you append the two clean queries properly, and that only works if each one is clean on its own.
What to Hand In
- Your .pbix file (name it YourName_Assignment1.pbix), uploaded to Google Drive, link set to "Anyone with the link can view".
- A screenshot of Applied Steps for Sales_2024_Clean, with the final step selected so the row count shows at the bottom of the preview.
- A step log: a short table in a Google Doc, one line per step and the rows left after it. Example below.
Paste all three links into the Assignment 1 hand-in box. You can edit your hand-in until a tutor marks it.
How It Is Marked
| Criterion | Marks | What the tutor checks |
|---|---|---|
| Dates read correctly | 20 | English (United Kingdom), no errors, no rows lost to Remove Errors |
| Names cleaned | 25 | 7 stations, 5 products, 3 payment methods, spelled as on the master sheets |
| Unmanned and blank dates | 15 | No empty attendants; no blank dates |
| Duplicates and quantities | 20 | Final row count 3,065 |
| Step log and screenshots | 20 | Every step listed, counts match the .pbix |
A step log whose numbers do not match your own file loses the step log marks even if the cleaning is right: the log is how you prove your work.
Questions You Will Have
My StationName column shows 21 values. What did I miss?
Capitalize Each Word. Trim fixes the spaces but not the capitals.
Some dates show Error after I changed the type.
The type was changed with your laptop's locale, not English (United Kingdom). Delete that step and use Change Type, Using Locale. Do not use Remove Errors.
Should I delete the rows with no attendant?
No. They are real sales from unmanned pumps. Replace null with Unmanned.
Can I copy my 2023 query and point it at the 2024 sheet?
You can (right-click, Duplicate, then in the Navigation step choose Sales_2024), but then check every step's numbers against the 2024 sheet. The grade is for a clean 2024 table, not a copied recipe.
Tick Before You Move On
- ☐ Sales_2024_Clean is its own query
- ☐ StationName shows 7 values, ProductName 5, PaymentMethod 3, and no empty attendants
- ☐ Date has the DATE badge, no errors and no blanks
- ☐ My final row count is 3,065
- ☐ I have the .pbix link, the Applied Steps screenshot and the step log ready
Quick Recap
Same recipe as Day 4, different sheet, done alone. Prove every fix with a count. Keep 2024 as its own query; Day 5 appends them. Hand in the .pbix, a screenshot and a step log, as links.
Final Scenario
On Day 5 your tutor appends everybody's Sales_2024_Clean to Sales_2023_Clean to build one two-year table.
If your 2024 query is clean, the append works first time and every chart after it is right. If it is not, every chart for the rest of the course carries your mistake.
Assignment 1 is small, and everything after it stands on it.
Next, Day 5: appending and merging queries.