Power QueryExercises

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 .pq

Paid 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 .pq

Revenue 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

  1. Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
  2. In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
  3. In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
  4. Change the PackFolder line to the folder you extracted to, then choose Done.
  5. 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

  1. Download the pack and extract the zip.
  2. In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
  3. Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
  4. Choose Done, check the preview against the expected rows in the query header, then Close & Apply.

Then try this

  1. Load Clean transactions, then open till-export.csv in a text editor, change one Paid line to Void and refresh: that line disappears.
  2. 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.