4. Data cleaning and transformation for crash safety

Crash safety datasets rarely arrive analysis-ready. A typical extract from a state crash database, a police collision report system, or an OEM telematics feed contains missing severity fields, inconsistent code vocabularies, and timestamps recorded in different time zones or formats. Before any measure — fatality rate, injury severity distribution, or time-of-day crash frequency — can be trusted, these three defects must be resolved inside Power Query. This chapter works through each defect with concrete transformation logic, using a representative crash table as the running example.

Assume the source table CrashRecords contains the following columns: CrashID, CrashDate (text, mixed formats), CrashTime (text, 24-hour), SeverityCode (text, mixed vocabularies), Injuries (integer, nullable), Fatalities (integer, nullable), State, and VehicleCount. The goal is a clean query that downstream DAX measures can consume without defensive logic.

Handling Missing Values

Missing values in crash data are not all equivalent, and treating them uniformly corrupts the analysis. Three categories must be distinguished:

• Structurally missing — a field that does not apply. A property-damage-only crash has no injury count because no injury occurred; the correct value is 0, not null.
• Unknown — the field applies but was not recorded. An injury crash with a blank Injuries cell means the count is unknown, and imputing 0 would understate harm.
• Invalid — a value that cannot be true, such as a negative fatality count or a crash date in the future.

In Power Query, the practical rule is: replace structurally missing values with explicit zeros, flag unknowns rather than imputing them, and reject invalid rows to a quarantine query. For Injuries and Fatalities, use Replace Values only after confirming the null semantics with the data owner. A safer pattern is a conditional column that preserves the distinction:

// Custom column: InjuryStatus
if [Injuries] = null and [SeverityCode] = "PDO" then "None"
else if [Injuries] = null then "Unknown"
else if [Injuries] < 0 then "Invalid"
else "Recorded"

This produces an auditable status column. Downstream measures can then compute injury rates over InjuryStatus = "Recorded" only, and report coverage as a data-quality KPI. Silently filling nulls with zero is the single most common cause of understated injury rates in crash dashboards, so make the decision explicit and visible.

Standardizing Severity Codes

Severity vocabularies differ by source. A police report may use K, A, B, C, O (the KABCO scale), while a hospital feed uses Fatal, Severe, Moderate, Minor, None, and a legacy extract may contain numeric codes 1–5. The dashboard needs one canonical scale. Define it explicitly, for example a five-level ordinal scale aligned to KABCO:

Implement the mapping as a reference query rather than a nested if chain. Create a two-column table SeverityMap (SourceValue, CanonicalLevel, Ordinal), then merge it into CrashRecords on the trimmed, upper-cased severity field. Trimming and case-folding before the merge is essential: " a " and "A" must resolve to the same key. After the merge, expand CanonicalLevel and Ordinal, then verify that no row has a null canonical level — those are unmapped codes and must be surfaced, not dropped.

// Before merge: normalize the join key
Text.Upper(Text.Trim([SeverityCode]))

// After merge: audit unmapped codes
Table.SelectRows(Merged, each [CanonicalLevel] = null)

Keeping Ordinal as an integer enables correct sorting in visuals; a text-only severity column sorts alphabetically and will place "Fatal" between "B" and "Minor" in a chart axis. Always carry the ordinal alongside the label.

Normalizing Timestamps

Crash timestamps arrive as separate date and time text fields, often in local time with no offset. Normalization has three steps: parse to a datetime, attach the correct time zone, and convert to a single reference zone (UTC is the safe default for storage; local time can be re-derived for display).

Parsing mixed formats is best done with DateTime.FromText combined with a locale argument, or by splitting on known delimiters. For the example table, CrashDate appears as 2023-07-14, 07/14/2023, and 14-Jul-2023. Rather than three conditional branches, use a locale-stable parse and validate the result:

// Combine date and time, parse with explicit culture
let
    Combined = [CrashDate] & " " & [CrashTime],
    Parsed   = DateTime.FromText(Combined, [Culture = "en-US", Format = "yyyy-MM-dd HH:mm"])
in
    Parsed

Where formats genuinely vary, add a ParseStatus column that records success or failure, and route failures to the quarantine query. A parsed null that is silently coerced to #datetime(1899,12,30,0,0,0) will appear as a cluster of crashes in 1899 on any time-series visual — a classic and confusing symptom.

Next, attach the time zone. If the source is a single-state database, a fixed offset is defensible; if it spans states or daylight-saving transitions, use DateTimeZone.SwitchZone with the source zone, then convert to UTC:

DateTimeZone.SwitchZone(
    DateTimeZone.From(Parsed, [Zone = "America/Chicago"]),
    0
)

Finally, derive analysis columns from the UTC value: CrashHour (local hour, re-derived), DayOfWeek, and IsWeekend. Deriving these in Power Query rather than DAX keeps the model lean and ensures consistent grouping across visuals.

Validating the Transformation

Cleaning is only complete when it is verified. Add a final validation step that checks four conditions and fails loudly if any is violated: the row count of the clean query equals the source count minus quarantined rows; no nulls remain in CanonicalLevel, CrashUTC, or InjuryStatus; no CrashUTC exceeds the current date; and the distribution of CanonicalLevel is plausible against the source (a sudden shift toward "Fatal" usually indicates a mapping error, not a safety crisis).

A useful diagnostic is a small summary query that groups by InjuryStatus and CanonicalLevel and counts rows. If "Unknown" exceeds roughly 10–15% of injury crashes, escalate to the data owner before publishing the dashboard — the coverage gap will otherwise be invisible to viewers. Similarly, plot CrashHour as a quick histogram; a spike at hour 0 combined with a spike in 1899 dates points to a parse failure that slipped past the status flag.

With these three transformations in place, the crash table carries explicit semantics for every field: missing values are classified rather than erased, severity is expressed on one ordinal scale that sorts correctly, and every timestamp is anchored to UTC with derived local analysis columns. The measures built in later chapters can then assume clean inputs and focus on the safety questions the dashboard exists to answer.