Table.PromoteHeaders
Uses the first row of the table as the column headers.
Table.PromoteHeaders(table as table, optional options as nullable record) as tableReturns: 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
- table: the table whose first row becomes the headers. Required.
- options: an optional record of settings.
[PromoteAllScalars = true]lets numbers and dates in the first row be used as headers as well as text. Optional.
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
- Only the first row is used. If a title or blank row sits above the headers, remove it first, or the title text becomes a column name.
- Headers from the first row keep whatever text was typed, including trailing spaces. Clean them with Table.TransformColumnNames if needed.
- Duplicate or blank header names cause trouble later. Rename them before you rely on them.
- Promoting headers is a shape step on the loaded data. It is not a database query question, since database tables already have named columns.
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.