Combine monthly Excel files with a schema check

Combining monthly files is reliable only when their structure and meaning agree. Identical-looking worksheets can differ in column names, units, table names or data types. Validate each file against an expected schema before accepting its rows into a consolidated report.

The spreadsheet quality lab supplies two valid XLSX files in monthly-inputs. January contains five clean lines totaling 55,500 paise; February contains two totaling 17,000. Each workbook has a table named SalesExport with the same eleven columns.

Define the input folder deliberately

In Excel, create a Power Query folder connection to monthly-inputs and inspect the file list before combining. Keep only the intended XLSX exports. Exclude temporary lock files, backups, prior combined outputs and unrelated workbooks.

Retain the source filename with each row. It gives a reviewer a direct route from an unexpected value back to the originating file. Also retain a batch or receipt identifier if files can be replaced under the same name.

Microsoft's folder connector documentation explains why files should be filtered before combination and how the selected object is applied across files. Choosing SalesExport from a sample workbook assumes the other accepted workbooks contain the corresponding object.

Check schema before appending values

The expected columns are LineID, InvoiceID, InvoiceDate, CustomerID, CategoryRaw, Quantity, UnitPricePaise, DiscountPaise, Status, Category and NetPaise. Order can be normalized, but missing or unexpected columns require a policy.

An illustrative Power Query guard after reading one source table is:

let
    Expected = {"LineID", "InvoiceID", "InvoiceDate", "CustomerID", "CategoryRaw", "Quantity", "UnitPricePaise", "DiscountPaise", "Status", "Category", "NetPaise"},
    Actual = Table.ColumnNames(SourceTable),
    Missing = List.Difference(Expected, Actual),
    Extra = List.Difference(Actual, Expected),
    Checked = if List.Count(Missing) + List.Count(Extra) > 0
              then error "Unexpected monthly export schema"
              else Table.SelectColumns(SourceTable, Expected)
in
    Checked

SourceTable is the table produced by your file-reading step; this guard belongs inside the per-file transformation, not only after all files have been combined. It deliberately requires an exact column set. A more flexible policy could allow documented optional columns while still requiring every measure and key.

The fragment is provided for Excel application review. The lab's Python script separately checks the supplied workbook headers and reference totals; it does not execute Power Query M.

Reject a changed unit even if the number looks reasonable

The separate schema-failure-example folder contains a workbook with NetRupees instead of NetPaise. Its numeric value is scaled into rupees. Renaming the column without converting the unit would understate its value by a factor of 100.

Keep this intentionally invalid file outside the valid import folder. For the failure test, copy it into a temporary test input folder and confirm that the guard stops or quarantines it with a visible error.

Do not enable a “skip errors” workflow that silently removes a month's data while leaving a successful-looking refresh. A partial report needs a visible incomplete status and an excluded-file list.

Validate types and business keys next

Matching headers are necessary but insufficient. Preserve CustomerID as text, parse dates under a known format, and require numeric amounts in the specified unit. A column named NetPaise could still contain rupees if the exporter changed semantics without renaming it.

After append, validate LineID uniqueness. A corrected January export delivered alongside the original should not automatically double January activity. Decide whether files are snapshots, replacements or incremental batches before determining how overlapping records are handled.

Reconcile at file and combined levels

The valid result contains seven lines and 72,500 paise. Reconcile January and February separately as well: an overall total can hide offsetting monthly mistakes. Maintain a file manifest with filename, row count, amount total, accepted/rejected status and reason.

Use Microsoft's combine-files overview for the generated sample-transform workflow, then add the business controls that the connector cannot infer.

Exercise: remove February from a test folder. The remaining data is structurally valid, so explain why a schema check alone cannot detect a missing expected month. Add an expected-period manifest and make completeness a separate acceptance condition.

NeuraPath's Data Analytics with Generative AI course connects repeatable spreadsheet imports with reporting quality. A dependable monthly workflow proves which files were accepted, which were rejected and why the final total is complete.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Power Query types: protect identifiers with leading zeros.
• Continue with Excel missing values: distinguish blank cells and empty strings.