How to Combine Files from a Folder with Excel Power Query

Power Query can combine files from a folder when those files follow a stable schema. The reliable workflow is to isolate the input folder, use the Combine Files interface, preserve the source filename, test schema changes, and validate row counts and totals after every refresh.

Define the expected schema

Write down the required file type, table or sheet name, column headers, key columns, date/number conventions, and whether subfolders are allowed. A folder is not a schema; one inconsistent file can break or silently distort the combined result.

Prepare a dedicated input folder

• Place only files intended for the dataset inside it.
• Remove temporary lock files and manually edited copies.
• Prefer the same file type and structure.
• Use a separate quarantine folder for files that fail validation.

Connect Excel to the folder

1. Open a blank workbook.
2. Select Data > Get Data > From File > From Folder.
3. Select the input folder.
4. Choose Combine & Transform Data.
5. Select the sample sheet, table, or object that represents the intended data.

Excel creates a main query plus helper queries and a transform function. Those helpers are dependencies, not clutter to delete without review.

Keep traceability in the main query

Retain Source.Name and, when needed, folder path information until the workflow is stable. Traceability makes it possible to identify which file introduced an unexpected row, type error, or new column.

Understand the generated M pattern

let
    Source = Folder.Files("C:\Corporate\Raw_Ingestion_Zone"),
    FilteredHiddenFiles = Table.SelectRows(
        Source,
        each [Attributes]?[Hidden]? <> true
    ),
    InvokeTransformFile = Table.AddColumn(
        FilteredHiddenFiles,
        "Transform File",
        each #"Transform File"([Content])
    ),
    RenamedColumns = Table.RenameColumns(
        InvokeTransformFile,
        {{"Name", "Source.Name"}}
    ),
    KeptColumns = Table.SelectColumns(
        RenamedColumns,
        {"Source.Name", "Transform File"}
    ),
    ExpandedData = Table.ExpandTableColumn(
        KeptColumns,
        "Transform File",
        Table.ColumnNames(#"Transform File"(#"Sample File"))
    )
in
    ExpandedData

This pattern depends on the Transform File function and Sample File query generated by the interface. It is not a standalone query for a blank workbook.

Apply data types after structural steps

Automatic type detection can misread dates, leading zeros, and mixed numeric/text identifiers. Review the Changed Type step, preserve an original text column during risky conversions, and define locale explicitly when imported dates or decimals depend on regional conventions.

Plan for schema drift

Control subfolders and file types

Folder.Files can include files from subfolders. Filter Folder Path when only the selected directory should count. Filter extensions before invoking the transform so a PDF, temporary file, or old workbook is not treated as input accidentally.

Validate every refresh

1. Add one known test file.
2. Refresh and confirm its rows appear once.
3. Compare combined row counts with the input files.
4. Reconcile a known total and several key records.
5. Review query errors and blank keys.
6. Confirm dates, decimals, and leading zeros.
7. Remove the test file and refresh again.

Make the folder path maintainable

A hard-coded local path may fail for another user or device. For shared workbooks, document the required folder, consider a parameter, and confirm that all users have access to the same source location. Privacy-level or credential prompts can also differ by environment.

Choose the load destination

Use Close & Load for a worksheet result or Close & Load To for a connection or Data Model. Remove unused columns before loading, but retain enough traceability to investigate refresh problems.

Completion checklist

• The input folder contains only intended files.
• The expected schema is documented.
• Source filename traceability remains.
• New and missing columns are handled deliberately.
• Row counts, totals, types, and errors are reviewed after refresh.
• The path and credentials work for the intended users.

Official Microsoft references

• Folder.Files
• Table.RenameColumns
• Dealing with errors in Power Query