Power QueryExercises

Clean and summarize a sales export

Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.

Level
beginner
Time
~10 min
Task
Build the query
Access
Free — no account needed
Loading the practice workspace…

About this exercise

What you'll practise

Build a repeatable cleanup pipeline: explicit types, text cleanup, row filtering, deduplication, a calculated column and a grouped total.

Northwind Kites exports orders from its till system every week. The export has inconsistent region spelling, a duplicated order and an order with no quantity. The operations lead wants revenue by region from completed orders only.

Steps

  1. Use the first row as headers.
  2. Set types: OrderID stays text (keep leading zeros), Qty whole number, UnitPrice decimal number, using the en-US culture.
  3. Clean Region: trim spaces and convert to UPPERCASE.
  4. Keep rows where Status is Complete and Qty is not null.
  5. Remove exact duplicate rows.
  6. Add Revenue = Qty × UnitPrice.
  7. Group by Region with the sum of Revenue in a column named Revenue.

Your result must

  • Output columns: Region, Revenue
  • One row per region (NORTH, SOUTH)
  • The duplicated order is counted once
  • Works when new orders are added to the export

Input

  • SalesExport (file)
    • Text file; first line: OrderID,Region,Qty,UnitPrice,Status
    • Every field is text until you set types

Expected output columns

Region (text), Revenue (number)

First 2 of 2 expected rows
RegionRevenue
NORTH33
SOUTH10