Power QueryExercises

Support-ticket operations reporting

Tidewater Telecom (fictional)

Clean a helpdesk export, measure resolution time against priority-based SLA targets and build a weekly SLA report per team.

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 support manager a weekly report of tickets, resolutions and SLA attainment per team, plus the ticket-level facts behind it, refreshed from each new export.

Tidewater's helpdesk exports tickets as CSV. Each ticket has a priority with a resolution target (P1 four hours up to P4 three days), the agent who owns it and, once closed, a resolution time. QA engineers sometimes create test tickets in production, marked [TEST] in the subject. Agents' teams come from the workforce list, which only knows lower-case logins.

Graded deliverables

  1. 1. Ticket Facts

    55 points

    One row per real ticket with clean priority, team, resolution hours and SLA result.

    • Columns: TicketID, Priority, Team, CreatedAt, ResolvedAt, ResolutionHours, SLAMet
    • No test tickets
    • ResolutionHours and SLAMet are null exactly for open tickets
    • Team never null (Unassigned)
    Expected output schema for Ticket Facts
    ColumnType
    TicketIDtext
    Prioritytext
    Teamtext
    CreatedAtdatetime
    ResolvedAtdatetime or null
    ResolutionHoursdecimal or null
    SLAMetlogical or null
    Grain:
    One row per ticket that isn't a test ticket.
    Order:
    Not graded.
    Nulls:
    ResolvedAt, ResolutionHours and SLAMet are null for open tickets only.
  2. 2. Weekly SLA Report

    45 points

    Tickets, resolutions and SLA attainment per Monday-based week and team.

    Builds on: Ticket Facts

    • Columns: WeekStart, Team, Tickets, Resolved, MetSLA, SLARate
    • Weeks start on Monday
    • SLARate null (not 0) when nothing was resolved
    • Sorted by WeekStart, then Team
    Expected output schema for Weekly SLA Report
    ColumnType
    WeekStartdate (Monday)
    Teamtext
    Ticketswhole number
    Resolvedwhole number
    MetSLAwhole number
    SLARatedecimal (0–1, 3 dp) or null
    Grain:
    One row per week and team with at least one ticket created that week.
    Order:
    Graded: WeekStart ascending, then Team ascending.
    Nulls:
    SLARate is null only when Resolved is 0.
Checking your access…