📎 This article includes 3 downloadable practice files ↓
Each branch sends a CSV with three junk lines on top (branch name, report date, blank line). “From Folder” combine can’t cope: it expects every file to start with headers. Write the cleaning once as a function.
fnClean = (path as text) as table =>
let
Raw = Csv.Document(File.Contents(path), [Delimiter = ",", Columns = 4, Encoding = 65001]),
Skipped = Table.Skip(Raw, 3),
Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Qty", Int64.Type}, {"Rate", type number}})
in Typed,
Files = Table.SelectRows(Folder.Files(Folder), each [Extension] = ".csv"),
Added = Table.AddColumn(Files, "Data", each fnClean([Folder Path] & [Name]))Columns = 4 matters. Without it, Csv.Document takes the column count from the first line, which is the one-column “Branch: Pune” title, so the Qty column simply doesn’t exist. That’s the error we hit when first running this lesson.Turn a query into a function the easy way
Build the steps on one file as a normal query, then in Advanced Editor wrap it: (path as text) => let ... in ... and replace the file path with path. Or right-click a query > Create Function.
[Name]) before expanding, or you lose track of which rows came from which branch.Practice
Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 8 KB)
- 🗂️Practice source files (zip)All messy CSVs for the course: invoices, payments, typed customer names, credit history, contacts, branch files, a two-row-header report and an ERP register.⬇ ZIP · 3 KB
- 📄Lesson 6 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 1 KB
- 📗Expected resultWhat the query returned when Excel ran it, to compare with yours.⬇ XLSX · 5 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.