Power QueryExercises

Refresh-resilient multi-file analyst case

Ostrander Cold Chain (fictional)

Standardise depot files with drifting layouts from a column map and settings file, separate valid shipments from data-quality problems, resolve resends, and build a status matrix that grows with new statuses.

Level
advanced
Time
~90 min
Deliverables
4
Rubric
v1

Access

Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.

Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).

Business goal

Give the operations analyst a pipeline that survives whatever the depots send next: every valid shipment once, a log of rows that need fixing, and a depot-by-status matrix for the Monday call.

Ostrander moves chilled freight between depots. Each depot exports its week's shipments from a different system, so the files arrive with different column names, column orders and extra columns. The data team keeps a column map (raw name → standard field) and a small JSON settings file listing depots to exclude, such as the training depot. Depots sometimes resend a corrected file; the most recently modified file is the truth. Bad dates and weights must not disappear silently: they go to a log the depots fix.

Graded deliverables

  1. 1. Standardized Rows

    25 points

    Every data row from every CSV, under the five standard column names, with file name and modified time.

    • Columns: SourceFile, Modified, ShipmentID, Depot, ShipDate, Status, WeightKg
    • Every row of every CSV, whatever its layout
    • Values unchanged (text as in the file)
    Expected output schema for Standardized Rows
    ColumnType
    SourceFiletext
    Modifieddatetime
    ShipmentIDtext
    Depottext
    ShipDatetext
    Statustext
    WeightKgtext
    Grain:
    One row per data row in any CSV file in the folder.
    Order:
    Not graded.
    Nulls:
    No nulls are expected; values stay as text.
  2. 2. Shipments

    30 points

    Each valid shipment once: excluded depots removed, resends resolved, values cleaned and typed.

    Builds on: Standardized Rows

    • Columns: ShipmentID, Depot, ShipDate, Status, WeightKg, SourceFile
    • One row per ShipmentID, from the latest file
    • No excluded depots; no invalid dates or weights
    Expected output schema for Shipments
    ColumnType
    ShipmentIDtext
    Depottext
    ShipDatedate
    Statustext
    WeightKgdecimal
    SourceFiletext
    Grain:
    One row per ShipmentID whose latest row is valid and not from an excluded depot.
    Order:
    Not graded.
    Nulls:
    No nulls.
  3. 3. Data Quality Log

    20 points

    Every row (outside excluded depots) whose date or weight is unusable, with the problem named.

    Builds on: Standardized Rows

    • Columns: SourceFile, ShipmentID, Problem
    • Rows from excluded depots are not logged
    • Both problems named when both fail
    • Sorted by SourceFile, then ShipmentID
    Expected output schema for Data Quality Log
    ColumnType
    SourceFiletext
    ShipmentIDtext
    Problemtext
    Grain:
    One row per staging row with at least one problem.
    Order:
    Graded: SourceFile ascending, then ShipmentID ascending.
    Nulls:
    No nulls.
  4. 4. Depot Status Matrix

    25 points

    Valid shipments per depot by status, one column per status that occurs, plus a total.

    Builds on: Standardized Rows, Shipments

    • Columns: Depot, one per status in the data, Total
    • 0 where a depot has no shipments with a status
    • Sorted by Depot
    Expected output schema for Depot Status Matrix
    ColumnType
    Depottext
    (one column per status)whole number
    Totalwhole number
    Grain:
    One row per depot with at least one valid shipment.
    Order:
    Graded: Depot ascending.
    Nulls:
    No nulls: missing combinations are 0.
Checking your access…