Café till export
A small coffee shop's till export with voided transactions, staff names typed three ways and timestamps to split into dates.
beginner · Skills: Promote headers, Data types with a culture, Trim and case cleanup, Filter rows, Group by · Updated 2026-11-04
Data files
- till-export.csv — 26 transactions from one week: TxnID, Timestamp, Item, Category, Qty, UnitPrice, Status (Paid/Void), Staff.Download CSV (1.6 KB)
Queries
Validated in the Power Query Exercises simulator against the files above. Paste them into Excel or Power BI yourself; the header comments explain each source binding.
Clean transactions clean-transactions.pq
Download .pqPaid lines only, typed, with a tidy Staff column and a LineTotal. Returns 24 rows: TxnID, Timestamp, Item, Category, Qty, UnitPrice, Staff, LineTotal.
// Clean transactions
// Pack: Café till export (Power Query Exercises, https://powerqueryexercises.com/downloads/cafe-till-export)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// TillExport the pack file till-export.csv -> File.Contents(PackFolder & "till-export.csv")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 24 rows with columns TxnID, Timestamp, Item, Category, Qty, UnitPrice, Staff, LineTotal.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\cafe-till-export\",
TillExport = File.Contents(PackFolder & "till-export.csv"),
Source = Csv.Document(TillExport, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Timestamp", type datetime}, {"Qty", Int64.Type}, {"UnitPrice", type number}}, "en-US"),
#"Paid only" = Table.SelectRows(#"Changed Type", each [Status] = "Paid"),
#"Clean staff" = Table.TransformColumns(#"Paid only", {{"Staff", each Text.Proper(Text.Trim(_)), type text}}),
#"Line total" = Table.AddColumn(#"Clean staff", "LineTotal", each [Qty] * [UnitPrice], type number),
#"Removed Status" = Table.RemoveColumns(#"Line total", {"Status"})
in
#"Removed Status"
Daily totals by category daily-category-totals.pq
Download .pqRevenue and items sold per day and category, sorted. Returns 14 rows: Day, Category, Items, Revenue.
// Daily totals by category
// Pack: Café till export (Power Query Exercises, https://powerqueryexercises.com/downloads/cafe-till-export)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// TillExport the pack file till-export.csv -> File.Contents(PackFolder & "till-export.csv")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 14 rows with columns Day, Category, Items, Revenue.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\cafe-till-export\",
TillExport = File.Contents(PackFolder & "till-export.csv"),
Source = Table.PromoteHeaders(Csv.Document(TillExport, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Timestamp", type datetime}, {"Qty", Int64.Type}, {"UnitPrice", type number}}, "en-US"),
#"Paid only" = Table.SelectRows(#"Changed Type", each [Status] = "Paid"),
#"Day" = Table.AddColumn(#"Paid only", "Day", each DateTime.Date([Timestamp]), type date),
#"Grouped" = Table.Group(#"Day", {"Day", "Category"}, {{"Items", each List.Sum([Qty]), Int64.Type}, {"Revenue", each List.Sum(List.Transform(Table.ToRecords(_), (r) => r[Qty] * r[UnitPrice])), type number}}),
#"Sorted" = Table.Sort(#"Grouped", {{"Day", Order.Ascending}, {"Category", Order.Ascending}})
in
#"Sorted"
Follow along in a host
Follow along in Excel
- Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
- In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
- In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
- Change the PackFolder line to the folder you extracted to, then choose Done.
- If Excel asks about privacy levels or file access, allow access to that folder. Then choose Home → Close & Load.
Follow along in Power BI Desktop
- Download the pack and extract the zip.
- In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
- Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
- Choose Done, check the preview against the expected rows in the query header, then Close & Apply.
Then try this
- Load Clean transactions, then open till-export.csv in a text editor, change one Paid line to Void and refresh: that line disappears.
- Add Daily totals by category as a second query and build a PivotTable or matrix on it.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.