Combining files with merged header rows: flatten first, then stack

Why two-row headers break a combine

Power Query, VSTACK and pandas all treat the first row as the column names. A report header like the one below gives them Region, Q1, blank, Q2, blank, and turns the second header row into data, so Sales and Units of different quarters end up in columns with no name or with duplicate names.

Flatten one file by hand

1. Unmerge row 1 (select it, Home > Merge & Center).
2. Fill the group labels right: in a helper row, =IF(B1="", A3, B1) copied across (where row 3 is the helper row) repeats Q1 over both its columns.
3. Build the names: =TRIM(B3 & " " & B2) gives Q1 Sales, Q1 Units and so on. Keep Region as it is.
4. Paste the new names as values into row 1, then delete row 2 and the helper rows.

That works for a one-off. For a folder of monthly exports it is tedious and easy to get wrong, so use a query or a script.

Flatten in Power Query

Do this in the query that transforms each file (in a From Folder combine, edit the Transform Sample File query so every file gets the same treatment). Do not promote headers first.

1. Transform > Transpose so the two header rows become the first two columns.
2. Select the first column (the group labels) and Transform > Fill > Down: Q1 now appears against both Sales and Units.
3. Select both columns, Transform > Merge Columns with a space separator.
4. Transform > Transpose back, then Use First Row as Headers.
5. Trim stray spaces from names like Region with a rename, or by trimming the merged column before transposing back.

Transposing a large file is slow, so this is best for report-sized exports. Once every file has the same flat header, the append is straightforward; if the files have different columns, see combining Excel files with different columns.

Flatten in pandas

import glob
import pandas as pd

frames = []
for f in sorted(glob.glob("reports/*.xlsx")):
    df = pd.read_excel(f, header=[0, 1])           # two header rows -> MultiIndex
    df.columns = [
        " ".join(p for p in (str(a), str(b)) if p and not p.startswith("Unnamed")).strip()
        for a, b in df.columns
    ]
    df["source_file"] = f
    frames.append(df)

pd.concat(frames, ignore_index=True).to_excel("combined.xlsx", index=False)

With a two-row header, pandas normally carries a merged group label across its columns and names empty header cells "Unnamed: …"; the comprehension drops those parts. Print the resulting column list for the first file to confirm the names before combining all of them.

Long format is often better

Quarter in the header is really data. If you plan to total or chart by quarter, unpivot instead: in Power Query select Region and choose Unpivot Other Columns, then split the attribute into Quarter and Measure. A long table with Region, Quarter, Measure and Value stacks cleanly whatever quarters each file covers.

Merged cells in the body of the report need the same unmerge and fill-down treatment; see merged cells breaking sorting and filtering. Analistable also expects one header row per sheet, so flattening first gives you clean column names there too. The combine Excel sheets guide covers the stacking step.

Checking the flattened names

Before appending, confirm that every file produces exactly the same column names. Small differences in an export, such as Q1 versus Q1 with a trailing space, or Units versus Qty, create extra columns instead of lining up. Compare the header lists side by side:

Rename the odd ones in their own step (a rename in Power Query, or a dictionary passed to df.rename in pandas) so all files agree before they are stacked. Otherwise the March units land in separate columns and the January to March total of units is understated.

Some exports use three header rows, for example year over quarter over measure. The same approach works: fill each group row across, then join all the levels into one name such as 2026 Q1 Sales. In pandas pass header=[0, 1, 2]; in Power Query fill down each of the first columns after transposing before merging them. Check the result on one file first, since deeper headers are more likely to contain stray blank cells.

Frequently asked questions

Why does Power Query name my columns Column2 or leave them blank?

The first row had merged or empty cells. Flatten the two header rows into one, then use first row as headers.

Can VSTACK handle two header rows?

Only if you skip them and supply your own header. Stack the data rows and put a single flattened header above with VSTACK(header_row, data).

Should I keep wide or long format?

Long format (one value per row) stacks and summarises more easily. Keep wide only for the final report layout.

What if each file has a different number of header rows?

Remove the extra title rows first (Remove Top Rows in Power Query, or skiprows in pandas) so every file starts with the same header structure before you flatten it.

Can I keep the merged header for the final report?

Yes. Combine and analyse the flat data, then build the presentation layout from it with a pivot table, which can show quarters as grouped column headings.

Related guides

• How to combine Excel files with different columns
• Dates change format when combining Excel files: find and fix them
• Combining data with VSTACK, HSTACK and other dynamic arrays
• How to merge every Excel table into one sheet
• How to pull data from multiple sheets into one summary sheet