Module 8
Profiling, errors and validation
Find and handle errors, missing fields and bad values with try … otherwise.
You'll be able to
- Locate and replace errors
- Use try … otherwise
- Validate data before reporting
Lessons
- 1.Find, remove and replace errorsHandle cell errors deliberately instead of letting them reach reports.
- 2.try … otherwiseCatch conversion errors inside an expression with a sensible fallback.
- 3.Profile columns before you trust themBuild a quick profile of a table's columns and read what it says about data quality.
- 4.Validate data and fail loudlyStop a refresh with a clear message when data breaks a business rule.
Take the profiling, errors and validation quiz (8 questions)
Exercises
- beginner~7 minPro
Remove rows whose quantity isn't a number (kite shop)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- beginner~7 minPro
Remove rows whose quantity isn't a number (bakery)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- beginner~7 minPro
Remove rows whose quantity isn't a number (bike rental)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- intermediate~8 minPro
Replace bad quantities with null and flag them (kite shop)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Replace bad quantities with null and flag them (bakery)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Replace bad quantities with null and flag them (bike rental)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
- intermediate~8 minPro
Parse sensor readings safely with try … otherwise (greenhouse)
Add a numeric Reading column that is null whenever Raw isn't a number.
- intermediate~8 minPro
Parse sensor readings safely with try … otherwise (cold store)
Add a numeric Reading column that is null whenever Raw isn't a number.
- intermediate~7 minPro
List the rows whose amount can't be converted (travel)
Before cleaning, finance wants an audit list of claims whose Amount isn't a number, with the original text.
- intermediate~7 minPro
List the rows whose amount can't be converted (grants)
Before cleaning, finance wants an audit list of claims whose Amount isn't a number, with the original text.
- advanced~10 minPro
Classify quantities as OK, Missing, Invalid or Negative (warehouse)
Give every stock count a validation status so the warehouse knows which rows to recount.
- advanced~10 minPro
Classify quantities as OK, Missing, Invalid or Negative (pantry)
Give every stock count a validation status so the warehouse knows which rows to recount.
- intermediate~8 minPro
Replace conversion errors with a different value per column (garden centre)
After typing, bad Discount values should become 0 and bad Price values null. One step can do both.
- intermediate~8 minPro
Replace conversion errors with a different value per column (bike parts)
After typing, bad Discount values should become 0 and bad Price values null. One step can do both.
- advanced~12 minPro
List every validation issue per lead (events)
Check three rules on each sales lead and report the failed ones in one Issues column, keeping only rows with problems.
- advanced~12 minPro
List every validation issue per lead (solar)
Check three rules on each sales lead and report the failed ones in one Issues column, keeping only rows with problems.
- advanced~10 minPro
Parse dates that arrive in two different formats (salon)
Bookings from the website use 2026-03-04; bookings from the phone team use 04/03/2026 (day first). Parse both correctly.
- advanced~10 minPro
Parse dates that arrive in two different formats (boat hire)
Bookings from the website use 2026-03-04; bookings from the phone team use 04/03/2026 (day first). Parse both correctly.