Power Query Promote Headers: Skip Report Titles Without Losing the First Record

Find the true header row in the source

This walkthrough uses Power Query in Excel for Windows to import a comma-delimited UTF-8 file. The first source row contains a report title, and the second contains preparation metadata. The third is the actual header. Every source record contains three fields, so structural parsing can succeed while the chosen headings are still wrong.

Read the raw file or preview before changing the query. A line containing a plausible word such as quantity is useful evidence, but it is not a universal header-detection algorithm. For this export, the contract says there are exactly two preamble records followed by three known headings. That bounded rule lets us explain every removed row and detect a future layout change.

Inspect automatic steps before adding new ones

Use Data > From Text/CSV and choose Transform Data. Check Applied Steps for Promoted Headers and Changed Type. If a previous step already promoted Warehouse export, a later Remove Top Rows operation is starting from the wrong row count. Work from the parsed Source step that still shows the original preamble as rows.

On a copy of the query, remove the inappropriate automatic promotion and any dependent type step, or edit the chain so the raw parsed table is the input to the correction. Inspect the preview immediately afterwards. It should have generic column names such as Column1 and show Warehouse export in its first row. Do not repeatedly promote headers until the column names look acceptable; each promotion consumes another row.

Remove two rows and promote exactly once

The procedure assumes the raw Source preview still contains all six CSV records. If it does not, first identify where records have already been consumed. The source file remains unchanged throughout the transformation.

1. In Power Query Editor, select Home > Remove Rows > Remove Top Rows and enter 2. The first remaining row must read item_id, region, quantity.
2. Choose Use First Row as Headers from the Home or Transform tab. Confirm the three column names match the agreed labels exactly.
3. Set item_id and region to Text; set quantity to Whole Number. Remove any earlier type conversion that would already have stripped the zero prefixes.
4. Confirm the first data record is 0007, North, 4. If the first record shown is 0012, South, 0, the query may have promoted one row too many.
5. Load the result to a new worksheet. Compare the output to headers-expected.csv, including the repeated item ID in a different region and the zero quantity.

Use a complete query that rejects a moved or changed header

Download headers-input.csv to a local folder. Create a blank query, open Advanced Editor, paste the following program and replace FilePath with your actual local file path. It reads raw CSV records without automatic type detection, skips exactly two records and checks the complete header row before promotion. The required names and order are deliberately fixed for this export.

The header guard rejects an added preamble line, duplicate or empty heading and reordered headings. The parser uses exactly three columns with ExtraValues.Error so a fourth field is rejected rather than silently dropped. This program does not infer the new schema. A header-only export returns zero data rows; a file ending before the expected header returns an error. Quantity conversion errors remain errors to investigate, rather than rows to discard.

let
    FilePath = "C:\CSVInput\headers-input.csv",
    Raw = Csv.Document(File.Contents(FilePath), 3, ",", ExtraValues.Error, 65001),
    AfterPreamble = Table.Skip(Raw, 2),
    ExpectedHeaders = {"item_id", "region", "quantity"},
    Checked = if Table.RowCount(AfterPreamble) = 0 then error "Missing header row after the two-record preamble"
        else if Record.FieldValues(AfterPreamble{0}) <> ExpectedHeaders then error "Header contract changed: expected item_id,region,quantity"
        else AfterPreamble,
    Headers = Table.PromoteHeaders(Checked, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Headers, {{"item_id", type text}, {"region", type text}, {"quantity", Int64.Type}}, "en-US")
in
    Typed

Handle duplicate or empty header labels as a schema problem

Power Query table column names must be unique, so promotion can disambiguate repeated labels by adding a suffix. That prevents a naming collision but does not explain which business field each repeated heading represents. If a source arrives with quantity twice, stop and ask which measure each column is intended to contain before mapping it into a stable report.

For this workflow, all three headings must be nonempty and exactly match the expected set. A blank heading or an extra field is a contract change to investigate, rather than a reason to silently use Column4. Trimming header whitespace may be an agreed repair, but retain a record of the original header text and check that trimming does not collapse two distinct headings into one name.

Separate preamble changes from record changes

Adding a fourth inventory record below the header should be a routine refresh. Adding an extra report-title row above the header is different: a fixed Remove Top Rows of two will now promote the wrong line. Use the negative test in header-refresh-tests.txt to make sure the next operator recognizes this failure instead of manually deleting a data row.

For variable-length reports, choose a documented marker or a separate clean export format. A robust marker rule must address missing markers, multiple matches, and lines that resemble headings inside the data. Do not replace the simple fixed rule with a search for the first nonblank row; report titles are often nonblank too. If the source owner can provide a table-only CSV, that is easier to operate than inferring a changing presentation layout.

Protect the first record and the meaning of types

A useful check is to pin the first expected record by its full values. In this sample, 0007, North, 4 must survive every step. A correct total is not sufficient: losing one record and duplicating another could hide behind an unchanged sum. Check row count, first record, zero presence, and the two separate 0012 region records.

When the headings are numeric dates or other scalar values, Table.PromoteHeaders supports options that control promotion and culture. Avoid relying on how a local machine formats a date to construct a permanent column name. This example deliberately uses stable text headings. For monthly date headers, normalize the names to an agreed format before downstream steps refer to them.

Keep a compact refresh checklist beside the query

Record the source filename pattern, comma delimiter, UTF-8 encoding, two-row preamble, exact header set, and identifier type. The file headers-contract.txt contains that information for the worked example. It also states that duplicate item IDs are allowed across regions, so a later operator does not remove them to make the data appear unique.

At the next refresh, compare the first preview records before loading the full report. After loading, confirm that the new row count agrees with the expected source inventory records and that quantity conversion produced no errors. If the export begins including totals at the bottom, treat that as a new contract decision; this guide has no footer rule and should not silently count a report total as another inventory record.

Reproduce the checked result

The downloadable query was executed in the native Power Query engine through Excel 16.0 build 5569.0 on October 5, 2026. The three output records matched headers-expected.csv exactly: 0007/North/4, 0012/South/0 and 0012/East/6. Adding 0020/West/3 produced four records totaling 13.

Native tests also rejected a moved header, duplicate heading, blank heading, extra field and missing header. A header-only file returned zero records, and a nonnumeric quantity stayed identifiable as a conversion error. These checks exercise parsing and M execution; they do not claim that every version uses identical automatic import steps or menu labels. Inspect your own Source and Applied Steps before using the menu-only route.