Power QueryExercises

Finance expense cleanup

Halden & Pike Consulting (fictional)

Turn a messy UK expense export with resubmitted claims and three currencies into one clean claim per row in pounds, then report approved spend by department.

Level
intermediate
Time
~60 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 finance controller a refreshable list of current claims converted to GBP and a monthly view of approved spend by department and category.

Halden & Pike's expense tool exports every submission, including corrections. When a consultant fixes a claim, the tool writes a new row with the same ClaimID and a later SubmittedAt; only the latest submission counts. Claims can be in GBP, EUR or USD; finance keeps a monthly table of conversion rates to GBP. The controller asked for current claims in pounds and approved spend by department.

Graded deliverables

  1. 1. Current Claims

    60 points

    One row per claim (its latest submission), rejected claims removed, amounts converted to pounds.

    • Columns: ClaimID, EmployeeID, ExpenseDate, Category, Currency, Amount, Status, AmountGBP
    • Exactly one row per ClaimID, taken from its latest submission
    • No Rejected claims; Pending claims stay
    • AmountGBP rounded to 2 decimals; GBP claims keep their amount
    Expected output schema for Current Claims
    ColumnType
    ClaimIDtext
    EmployeeIDtext
    ExpenseDatedate
    Categorytext
    Currencytext
    Amountdecimal
    Statustext (Approved or Pending)
    AmountGBPdecimal (2 dp)
    Grain:
    One row per ClaimID that is not currently rejected.
    Order:
    Not graded.
    Nulls:
    No nulls: every non-GBP claim has a rate for its month.
  2. 2. Approved Spend

    40 points

    Approved claims only, totalled in GBP by department and category.

    Builds on: Current Claims

    • Columns: Department, Category, Claims, TotalGBP
    • Approved claims only (Pending excluded)
    • Unknown employees grouped as Unlisted
    • Sorted by Department, then Category
    Expected output schema for Approved Spend
    ColumnType
    Departmenttext
    Categorytext
    Claimswhole number
    TotalGBPdecimal
    Grain:
    One row per department and category with at least one approved claim.
    Order:
    Graded: Department ascending, then Category ascending.
    Nulls:
    No nulls. Unknown employees are reported as Department "Unlisted".
Checking your access…