Power Query: combine files with different headers

To combine files with different headers in Power Query, rename each file’s headers to one standard set before appending. The M code below cleans every header (lower case, no spaces or symbols), looks it up in a small header map, then combines the files and stops with a clear message when an unmapped header appears. Copy it, or download it with three sample CSVs. Botlery builds Excel automations for a free fixed-price quote.

Updated October 2026 · 8 min read · By Rohail Nisar, founder of Botlery

Every month the same thing happens: the files are almost the same. One supplier writes “Qty”, another “Units”; someone exports with the columns in a different order; a header picks up a trailing space. Excel’s Combine Files button does not complain. It quietly fills the mismatched columns with blanks, and the total is wrong in a way nobody notices until it matters.

Why Combine Files gets this wrong

When you use Data › Get Data › From Folder › Combine, Excel builds one “sample file” transform from the first file and then expands every file into the column names it found in that sample. A file whose header says Quantity when the sample said Qty contributes an empty Qty cell, and its real quantities are dropped, with no error. If headers are not promoted per file, files are stacked by position instead, so a file with its columns in another order puts customers under dates.

The fix is to rename every file’s headers to one standard set before appending, and to stop the refresh when a header turns up that nobody has mapped yet.

Download the query and sample files

• power-query-combine-different-headers.txtThe full M query below, ready to paste into the Advanced Editor.
• sales-2026-09-07.csvHeaders: Order Date, Customer, SKU, Qty, Amount.
• sales-2026-09-14.csvHeaders: date, Customer Name, Item Code, Quantity, Total.
• sales-2026-09-21.csvColumns in another order, a trailing space in “Order date ”, “Amount (USD)” and an empty row.
• header-map.csvThe same header map as a two-column table, if you prefer to keep it in a worksheet.

Put the three sales files in a folder of their own, for example C:\Reports\Sales exports\.

Step 1: write down the header map

List every spelling you have seen on the left and the name you want on the right. The left side is written as the query’s Clean function sees it: lower case, letters and digits only. That one step already makes “Order Date”, “ORDER_DATE”, “order-date” and “Order date ” the same key, orderdate, so the map only needs the genuinely different words.

Step 2: paste the query

In Excel choose Data › Get Data › From Other Sources › Blank Query, open Advanced Editor, replace everything with the query and change FolderPath. Then Close & Load. In Power BI Desktop the same query works through Get Data › Blank Query.

Power Query (M) · Advanced Editor

// Combine CSV files whose headers differ: renames every header to one standard
// set BEFORE appending, so "Qty", "Units" and "Quantity" land in one column.
// From botlery.com/blog/power-query-combine-files-different-headers.html
//
// Excel: Data > Get Data > From Other Sources > Blank Query > Advanced Editor,
// paste this whole query, change FolderPath, then Close & Load.
let
    // 1. The folder the files are saved in (keep the trailing backslash).
    FolderPath = "C:\Reports\Sales exports\",

    // 2. The columns you want, in the order you want them.
    StandardColumns = {"Order Date", "Customer", "SKU", "Quantity", "Amount"},

    // 3. Every header spelling seen so far -> its standard name. Left side is the
    //    header as Clean() sees it: lower case, letters and digits only.
    HeaderMap = #table(
        {"Seen", "Standard"},
        {
            {"orderdate", "Order Date"}, {"date", "Order Date"}, {"invoicedate", "Order Date"},
            {"customer", "Customer"}, {"customername", "Customer"}, {"client", "Customer"},
            {"sku", "SKU"}, {"itemcode", "SKU"}, {"productcode", "SKU"},
            {"quantity", "Quantity"}, {"qty", "Quantity"}, {"units", "Quantity"},
            {"amount", "Amount"}, {"total", "Amount"}, {"amountusd", "Amount"}
        }
    ),

    // 4. true = refresh stops and names any header the map does not know yet.
    StopOnUnknownHeaders = true,

    Clean = (name as text) as text =>
        Text.Lower(Text.Select(name, {"a".."z", "A".."Z", "0".."9"})),
    MapRecord = Record.FromList(HeaderMap[Standard], HeaderMap[Seen]),
    Rename = (name as text) as text =>
        Record.FieldOrDefault(MapRecord, Clean(name), Text.Trim(name)),

    // One file: read it, promote headers, rename them, tag every row with the file name.
    LoadFile = (content as binary, fileName as text) as table =>
        let
            Raw = Csv.Document(content, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
            Promoted = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
            Renamed = Table.TransformColumnNames(Promoted, Rename),
            Tagged = Table.AddColumn(Renamed, "Source File", each fileName, type text)
        in
            Tagged,

    Files = Folder.Files(FolderPath),
    DataFiles = Table.SelectRows(Files, each Text.Lower([Extension]) = ".csv"),
    Loaded = Table.AddColumn(DataFiles, "Data", each LoadFile([Content], [Name])),
    Combined = Table.Combine(Loaded[Data]),

    Unknown = List.Difference(Table.ColumnNames(Combined), StandardColumns & {"Source File"}),
    Checked =
        if StopOnUnknownHeaders and not List.IsEmpty(Unknown) then
            error Error.Record("Unknown headers", "Add these to HeaderMap: " & Text.Combine(Unknown, ", "))
        else
            Combined,

    Ordered = Table.SelectColumns(Checked, StandardColumns & {"Source File"}, MissingField.UseNull),
    // Drop rows where every standard column is empty (blank lines, ",,,," rows).
    NoBlankRows = Table.SelectRows(Ordered, each not List.MatchesAll(
        Record.FieldValues(Record.SelectFields(_, StandardColumns)), each _ = null or _ = "")),
    Typed = Table.TransformColumnTypes(
        NoBlankRows,
        {{"Order Date", type date}, {"Customer", type text}, {"SKU", type text},
         {"Quantity", Int64.Type}, {"Amount", type number}},
        "en-US"
    )
in
    Typed

How it works, step by step

1. Clean lower-cases a header and keeps only letters and digits, so spaces, underscores, brackets and invisible characters such as a byte-order mark stop mattering.
2. MapRecord turns the two-column map into a lookup, and Rename returns the standard name for a known header, or the header unchanged (trimmed) for an unknown one.
3. LoadFile reads one CSV, promotes its own first row to headers, and runs Table.TransformColumnNames with Rename. This is the step Combine Files does not have: each file is renamed on its own, before it meets the others.
4. Table.Combine now appends by column name, not position, so column order inside each file no longer matters.
5. Unknown lists any column that is not in the standard set. With StopOnUnknownHeaders = true, the refresh stops with a message such as Add these to HeaderMap: Net Amount, instead of carrying on with a column of blanks.
6. Table.SelectColumns with MissingField.UseNull fixes the column order and adds any standard column that no file had, so the table always has the same shape for your pivots and formulas.
7. NoBlankRows drops rows where every standard column is empty, such as the ,,,, line in the third sample.
8. Table.TransformColumnTypes with "en-US" sets the data types using the culture the files were written in, not your PC’s.

What you get from the sample files

Nine rows in five standard columns plus Source File, with no empty Quantity or Amount cells: total quantity 67, total amount 595.95. The first row from each file looks like this:

Through Combine Files, the same three files fill the Qty and Amount columns for the first file only. The expand step knows only the sample file’s names, so the other two files’ quantities and amounts never arrive, and the Amount column adds up to 155.30 instead of 595.95.

Keep the map in a worksheet instead

If people other than you will add spellings, keep the map where they can see it. Paste header-map.csv into a sheet, format it as a table (Ctrl+T) and name the table HeaderMap in Table Design. Then replace the whole HeaderMap = #table(...) step with this one line:

Power Query (M) · replaces step 3

    HeaderMap = Excel.CurrentWorkbook(){[Name = "HeaderMap"]}[Content],

New spellings are then a new row in the table and a Refresh, with no code to touch.

Excel workbooks instead of CSV files

Workbooks bring one more variation: the sheet name. One file calls it Sheet1, the next one “Sales Sept”. Replace the LoadFile and DataFiles steps with the version below, which reads the first sheet of each workbook whatever it is called, and skips the ~$ lock files Excel leaves while a workbook is open.

Power Query (M) · replaces LoadFile and DataFiles

    // Excel files instead of CSV: read the first sheet of each workbook,
    // whatever the sheet is called. Headers come from its first row.
    LoadFile = (content as binary, fileName as text) as table =>
        let
            Sheets = Table.SelectRows(Excel.Workbook(content, true), each [Kind] = "Sheet"),
            FirstSheet = Sheets{0}[Data],
            Renamed = Table.TransformColumnNames(FirstSheet, Rename),
            Tagged = Table.AddColumn(Renamed, "Source File", each fileName, type text)
        in
            Tagged,

    Files = Folder.Files(FolderPath),
    // .xlsx only, and not the "~$" lock files Excel leaves while a workbook is open
    DataFiles = Table.SelectRows(Files, each Text.Lower([Extension]) = ".xlsx"
        and not Text.StartsWith([Name], "~$")),

Pitfalls

• Two headers in one file that mean the same thing, for example both “Date” and “Invoice Date”. Decide which one is the order date and map the other to its own name (for example “Invoice Date”) and add that to the standard columns, rather than mapping both to one name.
• Accented letters. Clean keeps a to z and digits only, so “Quantité” becomes quantit. That still works as a key; just add it to the map in that form.
• Headers not on the first row. If an export has title lines above the headers, drop them before parsing: read the file with Lines.FromBinary(content, QuoteStyle.Csv, false, 65001), skip the title lines with List.Skip, then pass Text.Combine(lines, "#(cr,lf)") to Csv.Document. Skipping after Csv.Document does not work, because it takes its column count from the first line it reads. Our weekly report guide shows the full step.
• Subfolders. Folder.Files includes files in subfolders. Keep an “old” or “archive” folder outside the source folder, or filter on the Folder Path column.
• Text encoding. The query reads files as UTF-8 (Encoding = 65001). If names with accents come out garbled, the files are probably Windows-1252; change the number to 1252.
• Numbers with comma decimals. Files written as 1.234,50 need a matching culture in the last step, such as "de-DE".

Frequently asked questions