Combine Multiple Excel Files from a Folder with Power Query

Combine Multiple Excel Files from a Folder with Power Query is a practical guide to solving a repeatable Office automation problem. Each month, similarly structured sales workbooks arrive in a Reports folder, and the goal is one consolidated table without manual copy and paste. We begin by defining the source and the desired output, walk through the technique in manageable stages, and finish with tests, failure cases and operational handover. The goal is not simply to memorize a function name: the reader should be able to measure the result, explain discrepancies and make later changes without losing control of the workflow.

Problem and learning objective

Each month, similarly structured sales workbooks arrive in a Reports folder, and the goal is one consolidated table without manual copy and paste.

Before touching a production workbook or database, create a small, recoverable example. A few normal rows, one missing value, one duplicate and at least one boundary case are often enough to expose mistakes. Write the expected answer before running the tool rather than treating a visually attractive grid or a success message as proof. In business reporting, data quality is part of the solution itself, not an optional task performed after coding.

Separate the problem into three layers: incoming data, the transformation or calculation rule, and an output that someone can use. This distinction helps determine whether a failure came from a source file, an incorrect type assumption, or the reporting layer. At each stage record column names, row counts, date limits and stable identifiers. A small audit summary makes it far easier to explain why next month’s report differs from this month’s.

Prerequisites and assumptions

In desktop Excel, Power Query is a data-transformation layer. Its operations appear as Applied Steps and are replayed on refresh, which is different from a one-time manual edit. Remove irrelevant fields early when source files are large so that preview and reconciliation remain manageable. Avoid modifying the raw source when the same task can be expressed as a repeatable transformation with a documented output table.

Tutorial datasets are deliberately compact, so avoid copying examples directly into live processes without reviewing assumptions. Operational data includes locked files, renamed columns, numeric values stored as text and identifiers that unexpectedly repeat. Decide ahead of time whether each case should be rejected, stop the whole job or produce an actionable warning. Each policy can be appropriate, but a silent and unexamined outcome rarely is.

Consider maintenance after the first successful run. A solution may fail later because someone renames a worksheet, adds a source column or upgrades Office. State the essential assumptions at the top of the exercise and near the code that relies on them. When an assumption is violated, a clear error and controlled stop are safer than silently exporting an incomplete report. Good documentation often outlasts clever code tricks.

Step-by-step implementation

A useful automation is repeatable. Given the same source and settings, another run should produce an explainable result instead of depending on which cell was selected or which file happened to be open. Keep constants in one place, document the sequence and make dependencies on the current date or local file path explicit. The less hidden state a procedure uses, the easier it is to debug and hand over to a colleague.

Ensure source files share a stable structure

At this stage: Use consistent names for sales, date and branch columns across the source workbooks. A stable named Table is safer than assuming the first sheet contains the intended data. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Ensure source files share a stable structure” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Connect with Data > Get Data > From Folder

At this stage: Open From Folder in Excel and supply the input directory. Understand the difference between Combine and Transform Data, and inspect the file list before merging content. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Connect with Data > Get Data > From Folder” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Exclude temporary and hidden files

At this stage: Keep eligible xlsx workbooks and skip lock files prefixed with ~$; do not mix backup copies with the actual source folder. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Exclude temporary and hidden files” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Choose the intended named Table from each workbook

At this stage: Identify the Sales Table by name within each workbook rather than choosing the first Sheet. Make the missing-table case visible instead of importing unrelated data. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Choose the intended named Table from each workbook” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Inspect source-file lineage and data types

At this stage: Preserve a source-file field for traceability and assign a numeric type to Amount. Compare subtotals in individual files with the combined result. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Inspect source-file lineage and data types” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Test refresh after adding a new workbook

At this stage: Add one valid new workbook and run Refresh All. Verify that the row count grows as expected and separately test the response to a malformed workbook. Apply this to a small test case and compare the outcome with its input before moving to the next step.

Test “Test refresh after adding a new workbook” with one normal input and one boundary case. Record the column names, data types and observed result together. If this checkpoint fails, correct its assumption rather than suppressing the error.

Working example and code review

The following example demonstrates the structure of combine Excel files with Power Query in a form that can be adapted to a sample workbook or database. Replace the sample table and file names with those in your test environment. Put VBA in a standard module inside a macro-enabled workbook, M code in Power Query Advanced Editor, and spreadsheet expressions in an appropriate worksheet cell.

let
 Source = Folder.Files("C:\Reports"),
 Visible = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
 Added = Table.AddColumn(Visible, "SalesRows", each
   let b = Excel.Workbook([Content], true), t = Table.SelectRows(b, each [Kind] = "Table" and [Name] = "Sales")
   in if Table.IsEmpty(t) then null else t{0}[Data]),
 Valid = Table.SelectRows(Added, each [SalesRows] <> null),
 Result = Table.Combine(Valid[SalesRows])
in Result

After running the example, reconcile source rows with the output. Each month, similarly structured sales workbooks arrive in a Reports folder, and the goal is one consolidated table without manual copy and paste. When multiple operations are involved, validate each one separately: the success of a later step does not prove an earlier transformation was correct. For real customer or finance records, keep an auditable error trail and avoid printing confidential data into broad-access logs or the VBA Immediate Window.

Validation and edge-case tests

Correctness comes before speed. A fast but inaccurate financial summary is worse than a slower report whose totals can be independently reconciled. Compare row counts and control totals with the source and manually inspect some included and excluded records. Repeat those same checks whenever a formula, query or table structure changes. This lightweight regression test is often more valuable than adding another layer of clever automation.

Finish every exercise with a measurable acceptance criterion, not merely a screenshot. The number of processed files should match the eligible input count; a control total should remain consistent; or output identifiers should match the reference table. Explain the result to someone who did not write the automation. If that explanation is difficult, an important assumption or business rule probably remains implicit and deserves another review.

The M language is sensitive to step order and column types. A number-looking string is not necessarily a numeric value, and regional date strings may be parsed with the wrong culture. Apply type conversions after the appropriate text cleanup and at a point where errors are visible. Measure how many rows fail conversion; blindly removing error rows can erase valid finance data and conceal a broken upstream export.

Common pitfalls

Symptom or risk	Check and correction
An unexpected input structure or result at checkpoint 1; confirm field names, types and output cardinality.	Before repeating the full workflow, inspect the test input, record the relevant values and rerun stage 1 independently. Suppressing an error before finding the cause risks an incomplete result.
An unexpected input structure or result at checkpoint 2; confirm field names, types and output cardinality.	Before repeating the full workflow, inspect the test input, record the relevant values and rerun stage 2 independently. Suppressing an error before finding the cause risks an incomplete result.
A source field has the wrong data type for the operation.	Inspect the column type and a sample value first.
New records changed totals or result shape unexpectedly.	Recompute row counts and reconciliation totals.

Keep a testing workflow separate from the production workflow. Consistent names, recoverable backups and a simple change log prevent many avoidable failures. For finance or personnel data, do not publish real personal records as sample content. If a process produces a downloadable or transferable document, inspect its filename, scope, format and destination before delivery. Automation reduces repetitive effort but does not eliminate responsibility for the results.

Best practices and maintainability

Power Query does not automatically rerun every time a worksheet cell changes. Refresh must occur, and data-source permissions or load settings may affect the result. Moving a workbook can break an absolute folder path. Before handing the report to another user, record its source path, refresh instructions and a recovery procedure, then test with a newly added file rather than relying only on existing samples.

Consider maintenance after the first successful run. A solution may fail later because someone renames a worksheet, adds a source column or upgrades Office. State the essential assumptions at the top of the exercise and near the code that relies on them. When an assumption is violated, a clear error and controlled stop are safer than silently exporting an incomplete report. Good documentation often outlasts clever code tricks.

Correctness comes before speed. A fast but inaccurate financial summary is worse than a slower report whose totals can be independently reconciled. Compare row counts and control totals with the source and manually inspect some included and excluded records. Repeat those same checks whenever a formula, query or table structure changes. This lightweight regression test is often more valuable than adding another layer of clever automation.

Practice exercise

Create a compact practice version of “Combine Multiple Excel Files from a Folder with Power Query”. Add three new inputs: one valid row, one duplicate and one row with a missing value. Write down the expected outcome for each before running the workflow. Compare actual and predicted results side by side, then deliberately rename a column or change a file path to observe failure handling. Finally document how to restore a correct run without deleting useful diagnostic information.

A useful automation is repeatable. Given the same source and settings, another run should produce an explainable result instead of depending on which cell was selected or which file happened to be open. Keep constants in one place, document the sequence and make dependencies on the current date or local file path explicit. The less hidden state a procedure uses, the easier it is to debug and hand over to a colleague.

Finish every exercise with a measurable acceptance criterion, not merely a screenshot. The number of processed files should match the eligible input count; a control total should remain consistent; or output identifiers should match the reference table. Explain the result to someone who did not write the automation. If that explanation is difficult, an important assumption or business rule probably remains implicit and deserves another review.

Related topics

Use the related lessons below to develop a connected learning path. Planned links can be activated once their destination articles are published.

• Access Data from Another Excel File with VBA
• Clean and Reshape Excel Data with Power Query Unpivot and Refresh
• Excel XLOOKUP Tutorial: Exact Matches and Missing Values
• Excel FILTER, UNIQUE and SORT: Dynamic Array Examples

Conclusion

combine Excel files with Power Query becomes genuinely useful when its result is repeatable and independently checkable. Each month, similarly structured sales workbooks arrive in a Reports folder, and the goal is one consolidated table without manual copy and paste. By working in small stages, testing edge cases and recording assumptions, a short example can become a reliable workflow. Before processing live records, repeat the checks on a backup and confirm compatibility with the relevant Office version and data model.