Data Cleaning Techniques Before Feeding Data into Dashboards

The number one support request we receive: "The dashboard numbers don't match my spreadsheet." 90% of the time, the culprit is dirty input data. Here's how we scrub it before a single pixel is drawn.

1. Standardise Date Formats Immediately

Logistics exports love dates. But some are MM/DD/YYYY, others are YYYY-MM-DD, and some even include timestamps. Our first processing step converts every date column to ISO 8601 using a strict parsing function that rejects ambiguous formats. If a value can't be parsed, we flag the entire row for human review.

2. Deduplicate Rows Aggressively

A scheduling export may contain the same shift twice—once as a "Planned" row and once as an "Actual" row. Without deduplication, your "total shifts" KPI will be wildly inflated. We use a composite key of Associate ID + Date + Route Type to identify duplicates and keep only the most recent entry.

3. Handle Missing Values Explicitly

An empty cell in a "Hours Worked" column doesn't mean zero—it means unknown. Our dashboards show missing data as a distinct grey indicator rather than silently treating it as 0. This transparency has prevented more payroll disputes than I can count.

4. Validate Numeric Ranges

If a driver's logged hours jump from 40 to 140 in one week, something is wrong. We apply sanity checks: hours must be between 0 and 80, delivery counts cannot exceed the vehicle's physical capacity. Outliers are quarantined and reported to the data provider.

5. Normalize String Categories

"Standard Route", "Std Route", and "STD" should all map to the same category. Our Variable Invoice Converter uses fuzzy matching to collapse a dozen vendor‑specific terms into five standard categories. This makes downstream aggregation possible.