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-type distribution can be visualized, the raw crash records — typically exported from a state crash repository, a police records management system, or a national fatality database — must be brought into the Power BI data model in a form that is complete, correctly typed, and consistently keyed. Power Query (the M engine inside Power BI Desktop) is the tool that performs this work. This section walks through the three ingestion paths you will encounter most often in crash analytics: flat CSV extracts, Excel workbooks maintained by analysts, and a live SQL Server crash database, and it shows how to shape each into a clean staging query.

Know your source before you connect

Crash data has a recognizable anatomy. A typical crash table contains a crash identifier, a date and time stamp, geographic fields (route, milepost, latitude/longitude, jurisdiction), environmental conditions (weather, light condition, road surface), and a crash-level severity classification. A separate person-level table carries occupant age, sex, seating position, restraint use, and injury severity (often coded on the KABCO scale: K = fatal, A = suspected serious injury, B = suspected minor, C = possible injury, O = property damage only). A vehicle-level table carries body type, model year, and maneuver. These three tables are related by the crash identifier, and that relationship is the backbone of the entire model.

Before connecting, confirm three things about each source: the grain of the table (one row per crash, per person, or per vehicle), the encoding of missing values (blank, NULL, UNKNOWN, 9, or 99), and whether the extract is a full snapshot or an incremental delta. Getting the grain wrong is the single most common cause of inflated crash counts later in the report.

Path 1 — CSV extracts

CSV is the most common delivery format from government crash repositories. Use Get Data → Text/CSV, then in the preview dialog set File Origin to the correct code page (crash extracts frequently arrive as Windows-1252 or UTF-8 with a byte-order mark) and verify the delimiter. Do not accept the automatic type detection blindly: a crash identifier such as 2023-001-0457 may be inferred as a date, and a milepost of 012.4 may lose its leading zero. Choose Data Type Detection: Based on Entire Dataset only after inspecting a sample, or use Do Not Detect Data Types and assign types explicitly in a subsequent step.

A robust CSV staging query performs these transformations in order:

1. Promote the first row to headers, then rename columns to stable, space-free names (for example CrashID, CrashDate, SeverityCode).
2. Set explicit types: CrashID as Text, CrashDate as Date, Milepost as Decimal Number, Latitude/Longitude as Decimal Number.
3. Replace sentinel missing codes with null using Replace Values scoped to the affected columns only.
4. Trim and clean text columns with Format → Trim and Clean to remove stray whitespace and non-printing characters that break joins.
5. Filter out header-repeat rows or footer totals that some exports append.

Keep this query as a staging query named stg_Crashes_CSV and reference it from a downstream query rather than loading it directly. This preserves the raw shape for auditing and keeps the model clean.

Path 2 — Excel workbooks

Excel sources are common when a safety office maintains a working crash log. Connect with Get Data → Excel workbook, then select the specific worksheet or, better, a named table or Excel Table object. Preferring a named table insulates the query from rows being inserted above the header. In the Navigator, use Transform Data rather than Load so you can shape the sheet before it enters the model.

Excel introduces two characteristic problems. First, mixed-type columns: a severity column containing both "K" and numeric codes will be typed as Any, and downstream measures will fail. Resolve this by explicitly converting the column to Text and then applying a conditional column that maps codes to a canonical severity label. Second, merged header cells and multi-row titles push the real header down; use Remove Top Rows until the header row is first, then Use First Row as Headers. If the workbook contains one sheet per month, combine them with Get Data → Folder and the Combine Files transformation, which generates a reusable function and appends a Source.Name column you can retain as a load-batch identifier.

Path 3 — SQL Server crash database

When crash records live in a relational database, connect with Get Data → SQL Server database and supply the server and database names. Choose Import mode for a scheduled refresh of a moderate-volume dataset, or DirectQuery when the crash table is very large and must remain live. For most crash dashboards, Import mode with an incremental refresh policy is the pragmatic choice.

Prefer a view or a parameterized query over selecting entire tables. A native query such as the following pulls only the columns the dashboard needs and filters to the analysis window:

SELECT
    c.CrashID,
    c.CrashDate,
    c.SeverityCode,
    c.Route,
    c.Milepost,
    c.Latitude,
    c.Longitude,
    c.WeatherCondition,
    c.LightCondition
FROM dbo.Crash c
WHERE c.CrashDate >= '2019-01-01'
  AND c.CrashDate <  '2025-01-01';

If you use a native query, Power BI cannot fold subsequent steps back to the server, so keep the query lean and do the remaining shaping in M. Also confirm that the SQL login has read-only permission and that the server's collation matches the text comparisons you intend to perform; a case-sensitive collation will silently drop rows when you filter on 'Fatal' versus 'FATAL'.

Harmonizing the three sources into one staging layer

The real design decision is not how to connect but how to reconcile. Each source uses different column names and different severity encodings. Build a single conformed query, stg_Crashes, that appends the three shaped sources after aligning column names and types, and add a SourceSystem column (CSV, Excel, SQL) so that data-quality issues can be traced back to their origin. The diagram below shows the ingestion topology and the point at which the sources converge.

Validation before you load

Do not close Power Query until the staging query passes a small set of checks. Confirm the row count against the source system's own count for the same date window. Confirm that CrashID is unique in the crash-grain query — a duplicate count greater than zero means the grain assumption is wrong or a join fanned out. Confirm that CrashDate has no nulls and falls within the expected range. Confirm that the severity distribution is plausible: a sudden spike in UNKNOWN usually indicates a sentinel value that was not mapped. These checks are cheap in Power Query and expensive to discover after the dashboard has been published.

With the conformed staging query in place, the crash, person, and vehicle tables share a consistent key and consistent severity vocabulary. That foundation is what allows the next stage — building the star schema and the DAX measures — to produce crash counts and injury rates that reconcile with the source system rather than merely looking plausible on a chart.