Power QueryExercises

Monthly sales consolidation

Larkspur Outdoor Supply (fictional)

Combine a folder of monthly till exports whose columns drift from month to month, then build the monthly revenue summary by region.

Level
intermediate
Time
~50 min
Deliverables
2
Rubric
v1

Access

Free. You can work through this project without an account; your queries stay in this browser. Sign in to save them to your account, resume on another device and record your score.

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 manager one refreshable table of every sales line and a monthly revenue-by-region summary that keeps working when next month's file lands in the folder.

Larkspur runs five outdoor-gear stores. Each month the till system drops a CSV into a shared folder. The exports were changed twice this year: in February the column order changed, and from March a Channel column (Store or Online) was added. Regional managers want monthly revenue, units and line counts by region. New stores sometimes open before the store list is updated.

Graded deliverables

  1. 1. Sales Lines

    50 points

    One typed row per sales line from every CSV in the folder, with the source file name.

    • Columns: SourceFile, OrderID, OrderDate, StoreCode, SKU, Qty, UnitPrice, Channel
    • Every .csv file in the folder is included, whatever its column order
    • No empty rows; returns (negative Qty) are kept
    • StoreCode trimmed and upper case
    Expected output schema for Sales Lines
    ColumnType
    SourceFiletext
    OrderIDtext
    OrderDatedate
    StoreCodetext
    SKUtext
    Qtywhole number
    UnitPricedecimal
    Channeltext (null before March)
    Grain:
    One row per sales line in any CSV file in the folder.
    Order:
    Not graded.
    Nulls:
    Channel is null for lines from files that have no Channel column. No other nulls are expected.
  2. 2. Monthly Region Summary

    50 points

    Revenue, units and lines by month and region, built on your Sales Lines query.

    Builds on: Sales Lines

    • Columns: Month, Region, Revenue, Units, Lines
    • One row per month and region; returns reduce Revenue and Units
    • Lines from stores missing in the store list are grouped under Unassigned
    • Sorted by Month, then Region
    Expected output schema for Monthly Region Summary
    ColumnType
    Monthdate (first of month)
    Regiontext
    Revenuedecimal
    Unitswhole number
    Lineswhole number
    Grain:
    One row per month and region that has at least one sales line.
    Order:
    Graded: Month ascending, then Region ascending.
    Nulls:
    No nulls. Unknown stores are reported as Region "Unassigned".
Loading the project workspace…