3. Data ingestion from crash databases

A crash safety dashboard is only as trustworthy as the ingestion layer beneath it. Before any measure of crash frequency, injury severity, or vehicle-level exposure can be visualized, the raw records held in state DOT extracts, police crash repositories, hospital linkage files, and legacy Access or SQL Server systems must be brought into Power BI in a form that is consistent, typed, and refreshable. Power Query (the M engine inside Power BI Desktop) is the correct place to do this work: it records every transformation as a repeatable script, so that when next quarter's crash extract arrives, the same cleaning logic runs without manual rework. This section walks through the three ingestion paths you will encounter most often — delimited text (CSV), Excel workbooks, and SQL Server tables — and the transformation patterns that make crash records usable.

Establishing the ingestion contract before touching the ribbon

Crash data arrives with predictable pathologies. A typical state crash file contains one row per crash event, with a separate person-level or vehicle-level table joined by a crash identifier. Common defects include: crash dates stored as text in mixed formats (2023-04-17, 04/17/2023, 17-APR-23); latitude and longitude delivered as text with directional suffixes (34.0522 N); severity codes as integers whose meaning lives in a separate lookup table; and a CrashID column that is numeric in one extract and alphanumeric in the next. Decide up front which columns are keys, which are measures, and which are descriptive attributes, and document the expected data types. This contract drives every subsequent step and prevents the classic failure where a text-typed CrashID silently fails to match its lookup table.

Importing CSV crash extracts

Use Get Data → Text/CSV. In the preview dialog, set File Origin to the encoding actually used by the source — many DOT extracts are Windows-1252 rather than UTF-8, and choosing wrongly produces mojibake in street names and county labels. Set the delimiter explicitly (comma for most extracts, but pipe-delimited files are common in older mainframe exports) and confirm the first-row-as-header setting. Click Transform Data rather than Load so that type correction happens before the table reaches the model.

Inside the Power Query Editor, the first action is to promote headers if they were not detected, then rename columns to stable, space-free names such as CrashID, CrashDate, SeverityCode, Latitude, Longitude, CountyName. Renaming at this layer insulates every downstream visual and DAX measure from source-column churn. Next, apply explicit types: CrashID as Text (never let Power Query infer it as Whole Number), CrashDate as Date, SeverityCode as Whole Number, and coordinates as Decimal Number after stripping directional suffixes.

A robust date-cleaning pattern uses a conditional column or try ... otherwise logic rather than a single locale-dependent conversion:

// Custom column: normalize mixed-format crash dates
let
    raw = [CrashDateRaw],
    parsed =
        try Date.FromText(raw, [Format="yyyy-MM-dd", Culture="en-US"])
        otherwise try Date.FromText(raw, [Format="MM/dd/yyyy", Culture="en-US"])
        otherwise try Date.FromText(raw, [Format="dd-MMM-yy", Culture="en-US"])
        otherwise null
in
    parsed

Rows that resolve to null are not silently dropped; instead, add a DateParseStatus column flagging them, and filter them into a separate query named Rejected_CrashDates for review. This preserves an audit trail — a requirement in any safety reporting context where record counts must reconcile against the source system.

Importing Excel crash workbooks

Excel sources introduce their own hazards: merged header cells spanning two rows, a title block above the actual table, and multiple sheets where only one holds the current year's records. Use Get Data → Excel workbook, select the sheet, and in the Navigator preview confirm the table region. If the header occupies two rows, choose Transform Data, then use Remove Top Rows to discard the title block and Use First Row as Headers once the true header row is on top. For merged headers, the reliable approach is to fill down the upper-level category labels before merging the two header rows into single composite names such as Vehicle_Type and Vehicle_ModelYear.

When the workbook contains one sheet per year (a very common crash-repository layout), do not import twelve queries by hand. Point a single query at the workbook, then use Combine Files or, more controllably, filter the Name column of the sheet-navigation table to the sheets matching a pattern, add a Data column via Excel.Workbook, expand the tables, and append a SourceSheet column so each row retains its year of origin. This makes annual refreshes a matter of dropping in a new workbook rather than editing the model.

Importing SQL Server crash records

For enterprise crash repositories, use Get Data → SQL Server database and supply the server and database names. Choose Import mode for dashboard-scale data (crash tables typically run to a few million rows, well within the capacity of Import mode and far faster for visuals than DirectQuery). In the Navigator, select the crash, vehicle, and person tables plus the severity and county lookup tables. Prefer selecting views or writing a native query that already filters to the reporting period and required columns, rather than importing entire multi-year tables and filtering afterwards:

SELECT c.CrashID, c.CrashDate, c.SeverityCode, c.CountyCode,
       c.Latitude, c.Longitude, c.RoadwayType
FROM dbo.CrashEvent AS c
WHERE c.CrashDate >= '2020-01-01'
  AND c.ReportStatus = 'FINAL';

Native queries must be foldable where possible, and any parameter used in them should be defined as a Power Query parameter so the date window can be changed without editing the query. After loading, set the CrashID column type to Text to match the CSV path, and confirm that the lookup tables' key columns share the same type — mismatched key types are the single most common cause of blank rows in a crash-to-severity relationship.

Harmonizing the three sources into one model

When the dashboard must combine a historical CSV archive with a live SQL Server feed, build a staging query per source, apply the same renaming and typing rules to each, and then append them into a single Fact_Crashes query. Add a SourceSystem column (CSV_Archive, SQL_Live) so that data-quality issues can be traced back to their origin. The diagram below shows the resulting ingestion topology and the transformation applied at each stage.

Verifying the load before building visuals

Ingestion is complete only when the row counts reconcile. After loading, compare the Fact_Crashes row count against the source system's count for the same date window; a shortfall usually indicates rows dropped by a type conversion (for example, a text value in a numeric severity column becoming Error and being filtered out). Check the Rejected_CrashDates query for unexpected volume — a handful of malformed dates is normal, but a large block suggests a locale mismatch in the Date.FromText format list. Finally, confirm that the relationship between Fact_Crashes and Dim_Severity shows no blank matches; if it does, inspect the key column types on both sides. Once these three checks pass, the model is ready for the measures and visuals that follow.