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
SortedResult
| Region | Month | Sales | Orders | Products |
|---|---|---|---|---|
| North | 2026-10-01 | 25.5 | 2 | Kite, Reel |
| South | 2026-10-01 | 39 | 1 | Kite |
| South | 2026-11-01 | 3.5 | 1 | Line |
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
| Check | Value |
|---|---|
| Completed lines | 4 |
| Completed amount | 68 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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).
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.
Csv.DocumentTable.TransformColumnTypesTable.NestedJoinTable.GroupDate.StartOfMonthNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Debugging and interview quiz