Downloads
Power Query MTable: headers and shapeBeginner

Table.PromoteHeaders

Uses the first row of the table as the column headers.

Syntax
Table.PromoteHeaders(table as table, optional options as nullable record) as table

Returns: The table with the first row as headers and the remaining rows as data.

What it does

Many exports begin with a row of column titles that Power Query does not recognize as headers. Table.PromoteHeaders takes that first row and uses it as the column names, and the rest of the table becomes data. It is usually the second step of cleaning an export, after the title rows above the headers have been removed.

Syntax and arguments

Example

A bank export has four title rows, then the header row. The title rows are removed, and then the header row is promoted.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    NoTitle = Table.RemoveFirstN(Source, 4),
    Headers = Table.PromoteHeaders(NoTitle, [PromoteAllScalars = true])
in
    Headers

After the step, the columns carry the names from the bank's header row, such as Date, Description and Amount. The data rows start on the row below.

Example: a second, different use

A file exported with generic column names is given its real headings from the first row of data, after a small block of notes is removed from the top.

let
    Source = Excel.CurrentWorkbook(){[Name = "Ledger"]}[Content],
    Body = Table.Skip(Source, 2),
    Named = Table.PromoteHeaders(Body)
in
    Named

Things to watch

In business terms

A monthly export is only reusable if the same header row is found each time. Promoting headers in the query, after removing the preamble, is what lets a bank or ledger file refresh without manual edits.

Related functions

All Power Query M functions · View as Markdown · M basics