Combine CSV Files in Power Query When Headers Change: A Safe Schema Contract

Write down which changes are acceptable

This example targets Microsoft 365 Excel on Windows with local UTF-8 comma-delimited files. It is a schema-reconciliation task rather than a general introduction to the Combine Files button. The required columns are item_id and amount. note is optional. Names are exact and case-sensitive for this contract; Amount or item_id followed by a space is a different heading until deliberately mapped.

Column order may change without changing field meaning. Extra export_version is intentionally ignored because this report does not use it. Missing amount is a failure, while a present amount column containing an empty value is a separate row-level completeness issue. Keeping these rules separate prevents a missing whole field from silently becoming a column of blanks.

Prepare a folder that contains inputs only

Create a local folder such as C:\CSVInput and place only a.csv, b.csv, and c.csv inside it for the first test. Save expected output, query text, and audit notes elsewhere. Otherwise a broad CSV folder import can ingest its own expected result or a previously exported report and double-count records.

The supplied query uses Folder.Contents and filters .csv entries in that directory. It does not recursively combine nested folders. Edit FolderPath to your actual input location. If you use the graphical Folder connector instead, inspect which folders and extensions are included before combining; its generated workflow and example-file assumptions deserve review when source layouts differ.

Understand where a generated combine can become brittle

Microsoft’s standard Combine Files workflow is designed around files with a shared structure. It creates a sample transformation that is applied to the other binaries, then expands results. That is convenient for consistent exports. For changing schemas, inspect both the transformation and the final expansion instead of fixing only the last visible error.

Look for a fixed column count in the CSV parsing step, headings promoted only after files have been appended, or an expansion list copied from the original sample file. Each can limit what reaches the final table. In this worked solution, the parser reads each file independently, headers are validated before promotion, and the final projection declares exactly which named fields belong in the report.

Use the complete per-file normalization query

Open Data > Get Data > From Other Sources > Blank Query, then Home > Advanced Editor. Insert the code and update FolderPath. The first output should agree with combined-schema-expected.csv. Required header checks occur before the optional-field projection, so MissingField.UseNull cannot conceal a missing required amount field.

The query adds source_file and row_in_file after projection and before combination. row_in_file is a data-record ordinal within the parsed file, not necessarily a physical text line number when quoted fields span lines. No duplicate rows are removed. A repeated item ID could be a legitimate transaction in another file, so this recipe does not claim that item_id alone is a unique row key.

let
    FolderPath = "C:\CSVInput",
    Required = {"item_id", "amount"},
    Wanted = {"item_id", "amount", "note"},
    Parse = (content as binary, filename as text) as table =>
        let
            Raw = Csv.Document(content, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
            HeaderValues = if Table.IsEmpty(Raw) then error "Empty file: " & filename else Record.FieldValues(Raw{0}),
            HeaderNames = List.Transform(HeaderValues, each if _ = null then "" else Text.From(_)),
            HeaderChecked = if List.Contains(HeaderNames, "") or List.Count(List.Distinct(HeaderNames)) <> List.Count(HeaderNames)
                then error "Empty or duplicate header: " & filename else Raw,
            Promoted = Table.PromoteHeaders(HeaderChecked, [PromoteAllScalars=true]),
            Missing = List.Difference(Required, Table.ColumnNames(Promoted)),
            Checked = if List.Count(Missing) > 0 then error "Missing required header in " & filename & ": " & Text.Combine(Missing, ", ") else Promoted,
            Projected = Table.SelectColumns(Checked, Wanted, MissingField.UseNull),
            Indexed = Table.AddIndexColumn(Projected, "row_in_file", 1, 1),
            Blanks = Table.TransformColumns(Indexed, {{"amount", each if _ = "" then null else _, type nullable text}}),
            Typed = Table.TransformColumnTypes(Blanks, {{"item_id", type text}, {"amount", type number}, {"note", type text}}, "en-US"),
            Named = Table.AddColumn(Typed, "source_file", each filename, type text)
        in Named,
    FolderRows = Folder.Contents(FolderPath),
    CsvFiles = Table.SelectRows(FolderRows, each [Extension] <> null and Text.Lower([Extension]) = ".csv"),
    Parsed = List.Transform(Table.ToRecords(CsvFiles), each Parse([Content], [Name])),
    Combined = if Table.IsEmpty(CsvFiles) then error "No CSV files in the selected folder" else Table.Combine(Parsed),
    Reordered = Table.SelectColumns(Combined, {"source_file", "row_in_file", "item_id", "amount", "note"}),
    Sorted = Table.Sort(Reordered, {{"source_file", Order.Ascending}, {"row_in_file", Order.Ascending}})
in
    Sorted

Audit accepted changes and test rejected changes

For a.csv, expect two rows and amount total 10. For b.csv, expect one row and amount total 5 despite the reversed required-column order. For c.csv, expect one row and amount -2; the extra export_version column must be absent from the fixed report. These per-file checks make it harder for a missing file to hide behind an apparently reasonable grand total.

Next test a copy of b.csv with amount renamed to total. The query must stop rather than insert null amounts. Test a duplicate item_id heading and an empty heading too. Restore the valid inputs afterwards. Header-only files with the correct headings contain zero data rows; whether those are acceptable for a real reporting period should be a separate delivery rule.

Keep type errors and missing values visible

The sample amount convention is a dot decimal with en-US parsing. It does not accept mixed regional conventions by guessing. An invalid amount such as pending should remain a conversion error to investigate. A blank amount remains missing and must be counted separately from a numeric zero. The four valid sample values include a zero and a negative adjustment, so removing zero or negative rows would break the expected total.

If optional note is missing from an entire file, every record from that file receives null in note. That is different evidence from an empty note supplied in a present column, even if both later appear blank on a worksheet. The source-schema-audit.csv records that distinction at file level. Keep such an audit when optional fields affect downstream decisions.

Expand the contract deliberately instead of accepting everything

When a new useful field appears, decide whether it is optional or required, add it to the appropriate lists, and create an expected output fixture. Update the contract before publishing the changed report. Blindly unioning every new heading can create duplicate concepts such as amount, Amount, and total_amount that look plausible but are not interchangeable.

This template is a small local-file workflow, not a complete data ingestion platform. It does not enforce a delivery calendar, identify duplicate source exports, or validate business uniqueness. Add those checks only when the real reporting contract requires them. Start with the three-file example, reconcile all rows, then test the actual file set and current Excel build before treating the process as operational.

Native execution results and older-engine compatibility

We ran schema-folder-query.pq against the three local CSV inputs in Excel 16.0 build 5569.0 on October 5, 2026. It returned all four rows in the expected order, retained 0007 and the other text identifiers, and totaled 13. The missing note in b.csv became null; the supplied empty note in a.csv remained empty text. The exported CSV renders both as a blank field, so inspect the query value when that distinction matters.

In separate runs, renaming amount to total stopped the query with Missing required header in b.csv: amount. Duplicating a heading or leaving one empty stopped it with Empty or duplicate header: b.csv. A header-only b.csv was accepted as zero rows; the other files then produced three rows totaling 8. This is why row counts and delivery expectations must accompany the grand total.

The original five-argument Table.AddIndexColumn call failed on this older engine, which accepted only two to four arguments. The download now uses four arguments for the same 1-based record ordinal. The query and all declared valid/error cases executed after that correction. Native provider execution does not verify every menu or dialog in other Excel versions.

Download schema-native-checks.csv outside the input folder. Start with only a.csv, b.csv and c.csv in the input folder, compare the valid result, then change a copy of b.csv for one negative case at a time. Restore the original before continuing. Never place a test checklist or expected-output CSV in the folder being combined.