Excel · ChatGPT

Merge Multiple Excel Files with Power Query

Append monthly Excel files without changing the source workbooks. Preserve provenance, align reordered or missing columns safely, detect repeated headers and duplicate files, then reconcile row counts and control totals.

Choose your situation — copy the tested prompt:

I need to append monthly Excel files with Power Query without changing the source workbooks.

Each workbook is stored in the same folder and contains an Excel table named Sales with these columns:
Date, OrderID, Amount

Files and control totals:
- Sales_Jan.xlsx: 2 data rows, Amount total 200.00
- Sales_Feb.xlsx: 2 data rows, Amount total 250.00
- Sales_Mar.xlsx: 1 data row, Amount total 150.00

Give me exact Excel Power Query steps to:
1. import every .xlsx file from the folder;
2. read only the Sales table from each workbook;
3. append the rows;
4. retain the original filename in a column named Source File;
5. exclude temporary files whose names begin with ~$;
6. set Date, OrderID, and Amount to appropriate data types;
7. load the combined result to a new table without editing the source files;
8. verify the file count, row count, and Amount control total.

State the exact expected final row count and Amount total. Warn me not to use the folder's sample-file transformation in a way that removes the filename before it is propagated to the combined rows.

Test 1 of 3 — approved PASS and preserved as tested.

Help me safely append three Excel tables with Power Query when their column order is inconsistent and one file is missing a column.

All workbooks contain a table named Sales:

North.xlsx columns:
OrderID, Region, Amount, Tax
Rows:
N-01 | North | 100 | 20
N-02 | North | 50 | 10

South.xlsx columns:
Amount, OrderID, Tax, Region
Rows:
80 | S-01 | 16 | South

West.xlsx columns:
Region, Amount, OrderID
Rows:
West | 70 | W-01
The West file has no Tax column.

Design the append by column name, not column position. Give exact Power Query steps or M logic that:
1. produces the canonical columns OrderID, Region, Amount, Tax, Source File;
2. maps reordered columns correctly;
3. creates Tax as null when it is absent instead of shifting another value into it;
4. flags unexpected extra columns for review;
5. preserves every source workbook;
6. verifies row count and control totals.

State the exact expected row count, Amount total, Tax total, and the expected Tax value for W-01. Do not invent zero tax when the source value is unknown.

Test 2 of 3 — approved PASS and preserved as tested.

I am combining Excel exports with Power Query, but one export contains a repeated header row inside the data and the folder also contains a duplicate copy of a monthly file.

April.xlsx:
Date | OrderID | Amount
2026-04-01 | A201 | 100
Date | OrderID | Amount
2026-04-02 | A202 | 150

April_copy.xlsx contains the same valid transactions as April.xlsx.

May.xlsx:
Date | OrderID | Amount
2026-05-01 | A203 | 200

Give me a safe Power Query workflow that:
1. keeps Source File;
2. identifies and removes only rows that are demonstrably repeated headers, such as Date="Date", OrderID="OrderID", and Amount="Amount";
3. does not remove legitimate data merely because one field happens to equal a column name;
4. detects that April.xlsx and April_copy.xlsx may duplicate the same business records;
5. reports duplicate OrderID groups before deciding which rows to retain;
6. keeps an audit table of excluded header rows and potential duplicates;
7. calculates row counts and Amount totals before and after each exclusion.

Do not silently delete duplicates. Explain what result is safe before business confirmation, and state the expected result only if April_copy.xlsx is confirmed to be an accidental duplicate.

Test 3 of 3 — approved PASS and preserved as tested.

Before — Three monthly Sales tables

Source condition	Risk
Three monthly Sales tables	5 data rows across Jan–Mar; no combined provenance or control check yet.

After — verified result

Output	Expected result
Combined query	5 rows · Amount 600.00 · Source File on every row

Before — North, South, West schemas

Source condition	Risk
North, South, West schemas	Columns reordered; West has no Tax column.

After — verified result

Output	Expected result
Canonical schema	4 rows · Amount 300 · known Tax 46 · W-01 Tax = null

Before — Repeated header + April copy

Source condition	Risk
Repeated header + April copy	Headers can become data and duplicate files can double revenue.

After — verified result

Output	Expected result
Audited result	After duplicate confirmation: 3 orders · Amount 450 · exclusions retained

Diagnosis — what can make this workflow unsafe

Filename lost

Sample transformation removes Name before expansion

High risk

Position-based append

Reordered columns shift values into the wrong fields

Corruption

Missing column

Unknown Tax is replaced with zero instead of null

Review

Silent deduplication

Legitimate transactions may be deleted without an audit

High risk

How to Merge Multiple Excel Files with Power Query

Practical workflow — 5 steps

Inventory the folder

Confirm file extensions, the Sales table name, expected files, row counts, and control totals before combining.

Import without touching sources

Use Data → Get Data → From File → From Folder; filter to .xlsx and exclude filenames beginning with ~$.

Normalize by column name

Keep Source File, select the canonical fields, align names rather than positions, and use null for genuinely missing fields.

Separate exceptions

Create review queries for unexpected columns, repeated header rows, and duplicate business keys instead of silently deleting them.

Reconcile the output

Compare file count, row count, Amount and other control totals before and after every exclusion, then load to a new table.

Verification rule: keep the source unchanged until the approved expected result, data types, row counts, and control checks agree with the workbook.

Merge Multiple Excel Files with Power Query — FAQs

How do I merge multiple Excel files into one workbook?

Use Power Query's From Folder connector, filter the files, extract the intended table from each workbook, append the rows, and load the result to a new table.

Can Power Query merge files when columns are in a different order?

Yes. Power Query can align fields by column name. Define a canonical schema so reordered columns do not shift values into the wrong fields.

What should happen when one Excel file is missing a column?

Add the missing field as null and flag the schema difference for review. Do not substitute zero when the source value is unknown.

How can I keep the source filename after combining files?

Retain the folder query's Name field through the transformation and rename it Source File before expanding or loading the combined rows.

How do I verify that merged Excel files are complete?

Reconcile the expected file count, final row count, distinct source filenames, and control totals such as Amount. Audit every removed header or duplicate separately.

Merge Multiple Excel Files with Power Query

How to Merge Multiple Excel Files with Power Query

Inventory the folder

Import without touching sources

Normalize by column name

Separate exceptions

Reconcile the output

Merge Multiple Excel Files with Power Query — FAQs

Related Excel workflows