Power QueryExercises

End-to-end project: from raw export to monthly report

Combine import, types, cleaning, filtering, merging and grouping into one reliable query.

~15 min read · advanced · Module 20: Projects, debugging and interviews

By Tahir Nas · Examples run in our simulator · Updated

Before this: Remove report banners before promoting headers, Convert dates and numbers with a locale, Trim, clean and capitalise text, Merge queries: choosing a join kind, Group by several keys and sort the output

Kite & Reel (a fictional retailer) exports sales from a German system: a banner line, semicolons, dd.MM.yyyy dates, decimal commas, and region names typed inconsistently. The sales team wants completed sales by region and month, with the products sold. Here's the whole job as one query, then a walk through each decision.

Input file SalesExport

Sales export (Kite & Reel GmbH, fictional)
OrderID;Date;Region;SKU;Amount;Status
S-1;03.10.2026; north;K-01;19,50;Complete
S-2;15.10.2026;North;R-02;6,00;Cancelled
S-3;28.10.2026;SOUTH;K-01;39,00;Complete
S-4;02.11.2026;south ;L-03;3,50;Complete
S-5;30.10.2026;North;R-02;6,00;Complete
let
    // 1. Import: skip the banner, semicolon delimiter, promote headers
    Raw = Csv.Document(SalesExport, [Delimiter = ";", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Table.Skip(Raw, 1)),
    // 2. Types with the source culture (German dates and decimals)
    Typed = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Amount", type number}}, "de-DE"),
    // 3. Clean text before anything compares it
    Clean = Table.TransformColumns(Typed, {{"Region", each Text.Proper(Text.Trim(_)), type text}}),
    // 4. Keep completed sales only
    Complete = Table.SelectRows(Clean, each [Status] = "Complete"),
    // 5. Look up product names (left outer: keep sales with unknown SKUs)
    Products = #table({"SKU", "Product"}, {{"K-01", "Kite"}, {"R-02", "Reel"}, {"L-03", "Line"}}),
    WithProduct = Table.ExpandTableColumn(Table.NestedJoin(Complete, {"SKU"}, Products, {"SKU"}, "P", JoinKind.LeftOuter), "P", {"Product"}),
    // 6. Period, then summary at region × month grain
    WithMonth = Table.AddColumn(WithProduct, "Month", each Date.StartOfMonth([Date]), type date),
    Summary = Table.Group(WithMonth, {"Region", "Month"}, {
        {"Sales", each List.Sum([Amount]), type number},
        {"Orders", each Table.RowCount(_), Int64.Type},
        {"Products", each Text.Combine(List.Sort(List.Distinct([Product])), ", "), type text}
    }),
    // 7. Explicit order for the reader
    Sorted = Table.Sort(Summary, {{"Region", Order.Ascending}, {"Month", Order.Ascending}})
in
    Sorted

Result

RegionMonthSalesOrdersProducts
North2026-10-0125.52Kite, Reel
South2026-10-01391Kite
South2026-11-013.51Line

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Decisions worth explaining

  • Skip, then promote: the banner line would otherwise become the header.
  • de-DE on the whole type step: "03.10.2026" and "19,50" are only unambiguous with the source's culture.
  • Clean before filtering and grouping: " north", "North" and "SOUTH" must compare equal before Group By sees them.
  • Left outer lookup: a sale with an unknown SKU should still count; it would show a blank product, which is a data-quality signal.
  • Month as a date: the first of the month sorts and joins correctly; format it for display in the report.
  • Sort last: Group By doesn't promise an order.

Verify before you publish

Check the output against the input with a control total. Completed sales in the raw file are 19.50 + 39.00 + 3.50 + 6.00 = 68.00, and the summary's Sales column must add up to the same:

Input file SalesExport

Sales export (Kite & Reel GmbH, fictional)
OrderID;Date;Region;SKU;Amount;Status
S-1;03.10.2026; north;K-01;19,50;Complete
S-2;15.10.2026;North;R-02;6,00;Cancelled
S-3;28.10.2026;SOUTH;K-01;39,00;Complete
S-4;02.11.2026;south ;L-03;3,50;Complete
S-5;30.10.2026;North;R-02;6,00;Complete
let
    Raw = Csv.Document(SalesExport, [Delimiter = ";"]),
    Promoted = Table.PromoteHeaders(Table.Skip(Raw, 1)),
    Typed = Table.TransformColumnTypes(Promoted, {{"Amount", type number}}, "de-DE"),
    Complete = Table.SelectRows(Typed, each [Status] = "Complete")
in
    #table({"Check", "Value"}, {{"Completed lines", Table.RowCount(Complete)}, {"Completed amount", List.Sum(Complete[Amount])}})

Result

CheckValue
Completed lines4
Completed amount68

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

25.5 + 39 + 3.5 = 68 and 2 + 1 + 1 = 4 orders: the summary balances.

Where to find this in Excel

Excel for Windows
Data > Get Data to import, then Home > Close & Load in the editor
What you see
Close & Load puts the finished query on a worksheet; Close & Load To lets you choose another destination.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Create, load, or edit a query in Excel (Power Query) (Microsoft Support).

Your first Power Query in Excel, from table to refresh

Common mistakes

  • Typing dates and amounts with your own machine's culture instead of the file's.
  • Grouping before cleaning region names, splitting regions.
  • Using an inner join for the product lookup and silently dropping sales with new SKUs.

Excel, Power BI and the simulator

  • In Excel or Power BI Desktop the same query would start from Folder.Files or File.Contents; everything after the source step is identical.
  • In Power BI, you might load the cleaned line-level table and let DAX measures do the summary, so it responds to slicers. The cleaning steps stay in Power Query either way.
  • The simulator runs this query on the fixture file shown above; graded projects also run your query against held-out files.
Functions in this lesson:Csv.DocumentTable.TransformColumnTypesTable.NestedJoinTable.GroupDate.StartOfMonth

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Debugging and interview quiz