Power QueryExercises

Inventory supplier merge

Quarry Lane Hardware (fictional)

Consolidate a multi-sheet stock count workbook, compare it with the reorder policy and pick the best supplier for everything that needs ordering.

Level
advanced
Time
~70 min
Deliverables
2
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 purchasing lead a reorder list that says how many of each item to buy and from whom, refreshed every time the warehouse saves its count workbook.

Quarry Lane's warehouse team counts stock into a workbook with one sheet per area. Each sheet ends with a total row. Last year's counts are kept on a hidden sheet. Purchasing keeps a reorder policy (reorder point and target stock per item) and a catalogue of supplier offers, where several suppliers can sell the same item. Purchasing wants every item below its reorder point, the quantity to bring it back to target, and the cheapest supplier (ties go to the shortest lead time, then supplier name).

Graded deliverables

  1. 1. Stock On Hand

    40 points

    Total counted stock per SKU across every visible sheet of the workbook.

    • Columns: SKU, OnHand, Bins
    • One row per SKU; hidden archive sheet excluded
    • No Total or empty rows
    • OnHand is a whole number
    Expected output schema for Stock On Hand
    ColumnType
    SKUtext
    OnHandwhole number
    Binswhole number
    Grain:
    One row per SKU counted on any visible sheet.
    Order:
    Not graded.
    Nulls:
    No nulls.
  2. 2. Reorder List

    60 points

    Every policy item below its reorder point, the quantity to order and the best supplier.

    Builds on: Stock On Hand

    • Columns: SKU, OnHand, ReorderPoint, ReorderQty, Supplier, UnitCost, LeadTimeDays, OrderValue
    • Includes policy items that were not counted (OnHand 0)
    • Exactly one supplier per item, chosen by cost, then lead time, then name
    • Sorted by SKU
    Expected output schema for Reorder List
    ColumnType
    SKUtext
    OnHandwhole number
    ReorderPointwhole number
    ReorderQtywhole number
    Suppliertext
    UnitCostdecimal or null
    LeadTimeDayswhole number or null
    OrderValuedecimal or null
    Grain:
    One row per policy item whose stock is below its reorder point.
    Order:
    Graded: SKU ascending.
    Nulls:
    UnitCost, LeadTimeDays and OrderValue are null only when Supplier is NO SUPPLIER.
Checking your access…