Coffee roaster monthly files
A folder of monthly sales CSVs from a coffee roaster. February added an Origin column. Combine by column name and practise a real refresh with the April file.
intermediate · Skills: Combine files from a folder, Schema drift, Keeping the file name · Updated 2026-11-04
Data files
- monthly/sales-2026-01.csv — January (no Origin column).Download CSV (220 B)
- monthly/sales-2026-02.csv — February (adds Origin).Download CSV (269 B)
- monthly/sales-2026-03.csv — March.Download CSV (212 B)
- refresh-drill/sales-2026-04.csv — April: copy it into monthly/ to practise a refresh.Download CSV (271 B)
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.
Combine every monthly file combine-monthly.pq
Download .pqAll CSVs in monthly/, matched by column name, with the source file. Returns 18 rows: SourceFile, Date, Blend, Bags, PricePerBag, Origin.
// Combine every monthly file
// Pack: Coffee roaster monthly files (Power Query Exercises, https://powerqueryexercises.com/downloads/roaster-monthly-folder)
//
// 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:
// MonthlyFolder every file in the pack's monthly/ folder -> Folder.Files(PackFolder & "monthly")
// 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
// 18 rows with columns SourceFile, Date, Blend, Bags, PricePerBag, Origin.
// 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\roaster-monthly-folder\",
MonthlyFolder = Folder.Files(PackFolder & "monthly"),
Source = Table.SelectRows(MonthlyFolder, each Text.Lower([Extension]) = ".csv"),
#"Parsed" = Table.AddColumn(Source, "Data", each Table.PromoteHeaders(Csv.Document([Content]), [PromoteAllScalars=true])),
#"Kept" = Table.RenameColumns(Table.SelectColumns(#"Parsed", {"Name", "Data"}), {{"Name", "SourceFile"}}),
#"Columns" = List.Union(List.Transform(#"Kept"[Data], Table.ColumnNames)),
#"Expanded" = Table.ExpandTableColumn(#"Kept", "Data", #"Columns"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded", {{"Date", type date}, {"Bags", Int64.Type}, {"PricePerBag", type number}}, "en-US")
in
#"Changed Type"
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 the query, then copy refresh-drill/sales-2026-04.csv into the monthly folder and refresh. April's rows appear with their SourceFile.
- January has no Origin column, so its Origin cells are null. That's correct: don't fill it with a guess.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.