Table.PromoteHeaders Function in Power Query M (3 Examples)

If you have a table where the real column names are sitting in the first row of data, Table.PromoteHeaders is what turns that first row into the actual headers. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.PromoteHeaders Function

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

where

table (required, table). The input table whose first row you want to use as the column names.
options (optional, nullable record). Controls how values get promoted. Two fields are valid: PromoteAllScalars (set to true to promote non-text values like numbers and dates too) and Culture (a culture name such as "en-US" that decides how those values render as text). Omit the record to promote only text and number values.

Returns: a table where the first row’s values have become the new column names, and every row below it is now data.

In plain terms, it takes the top row of your table and uses those values as headers, pushing everything else down into the rows.

Example 1: Promote the first row to headers

Say a table came in with generic names like Column1, Column2, Column3, and the real headers are sitting in row 1.

Here is the starting data:

Column1	Column2	Column3
Region	Quarter	Units
East	Q1	120
West	Q1	95
East	Q2	138

Promote that first row to headers:

let
    Source = Excel.CurrentWorkbook(){[Name="Imported"]}[Content],
    Promoted = Table.PromoteHeaders(Source)
in
    Promoted

The first row’s values become the column names.

The result keeps:

Region	Quarter	Units
East	Q1	120
West	Q1	95
East	Q2	138

Region, Quarter, and Units are now proper headers, and the data starts on the row below.

Example 2: Promote a first row that holds numbers

By default, only text and number values get promoted. Dates, logicals, and nulls in row 1 are skipped and leave their column with a generic name.

To force every value in row 1 into a header, pass [PromoteAllScalars=true]. This matters most when row 1 holds a date or a true/false value. Per Microsoft Learn, a date like 1/1/1980 is left behind by default but becomes a header once the flag is set.

The example below has a row that mixes text with year numbers, so you can see the flag in use.

Here is the starting data:

Column1	Column2	Column3
Product	2021	2022
Widget	540	610
Gadget	320	405

Promote the row with the flag set:

let
    Source = Excel.CurrentWorkbook(){[Name="Years"]}[Content],
    Promoted = Table.PromoteHeaders(Source,[PromoteAllScalars=true])
in
    Promoted

The result keeps:

Product	2021	2022
Widget	540	610
Gadget	320	405

The year numbers become column names too. Add Culture to the record when you need control over how a date or number renders as that text header.

Example 3: Promote then re-apply column types

This is the everyday case. A file gets imported, the real headers land in row 1, and you promote them before doing anything else.

There is one catch. Promotion drops the column types, so a number column comes out as any until you re-type it. The usual fix is to follow with Table.TransformColumnTypes.

Here is the starting data:

Column1	Column2	Column3
OrderID	Customer	Amount
A1001	Asha Iyer	250
A1002	Ravi Menon	480
A1003	Neha Rao	175

Promote the headers, then set Amount back to a number:

let
    Source = Excel.CurrentWorkbook(){[Name="RawExport"]}[Content],
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted,{{"Amount",type number}})
in
    Typed

The result keeps:

OrderID	Customer	Amount
A1001	Asha Iyer	250
A1002	Ravi Menon	480
A1003	Neha Rao	175

Now the headers are real and Amount is typed as a number rather than left as any.

Things to keep in mind with Table.PromoteHeaders

It only promotes the FIRST row. If the real headers are not on row 1 (title or blank rows sit above them), remove those rows first with Table.Skip or the “Remove Top Rows” command, then promote.
Non-text values are skipped by default. Dates, logicals, and nulls in row 1 are left behind and keep a generic name. Add [PromoteAllScalars=true] to force them in, and Culture to control how they render as text.
Promotion drops column types. Every column comes out as any, so follow with Table.TransformColumnTypes (this is the “Changed Type” step the UI adds automatically) to get numbers and dates typed correctly.
Duplicate header values get suffixed. If two row-1 values are identical, Power Query disambiguates by appending .1, .2, and so on, so two Amount cells become Amount and Amount.1.

Common questions about Table.PromoteHeaders

Is the “Use First Row as Headers” button the same thing?

Yes. The Home tab’s “Use First Row as Headers” command generates Table.PromoteHeaders behind the scenes. To add an option, edit the step to Table.PromoteHeaders(#"Changed Type",[PromoteAllScalars=true]).

What is the difference between Table.PromoteHeaders and Table.DemoteHeaders?

They are exact inverses. Table.PromoteHeaders turns the first data row into headers, while Table.DemoteHeaders pushes the current headers down into the first data row and resets the column names back to Column1, Column2, and so on.

List of All Power Query Functions

Related Power Query Functions / Articles: