Data Preparation and Cleansing
What to know
Cleansing centers on the Data Cleansing tool (trim whitespace, remove nulls, replace nulls, modify case, remove punctuation) and the Formula tool for derived columns. Understand null handling: nulls propagate through most expressions, and functions like IsNull, Coalesce, and IFNULL-style patterns control that. Know string functions (Trim, Upper, Replace, RegEx), numeric rounding, and date parsing/formatting.
Traps
- Treating empty string and null as identical; they are not, and filters behave differently.
- Case-sensitivity in joins and filters; normalize case before comparing.
- Whitespace that looks identical but differs (tabs, non-breaking spaces) breaking matches.
- Applying cleansing to the wrong column or forgetting it runs before a join.
- Date formats: ambiguous day/month order silently mis-parses.
Rule of thumb
Cleanse early, before joins and aggregations. Normalize keys (trim, case-fold, strip punctuation) on both sides of any join.
Worked example: Two tables join on customer name but produce few matches. Inspection shows trailing spaces and mixed case. Fix: apply Trim and Upper to the name on both inputs before the join, then re-check the match count.