Downloads
Power Query MTable: headers and shapeIntermediate

Table.DemoteHeaders

Turns the current column headers into the first row of data and gives the columns generic names.

Syntax
Table.DemoteHeaders(table as table) as table

Returns: A table with the old headers as its first data row and columns named Column1, Column2 and so on.

What it does

Table.DemoteHeaders does the reverse of Table.PromoteHeaders. The current column names become the first row of the data, and the columns are given generic names. It is used when the headings were read as headings, but they are really values, such as a list of account numbers, and the table needs them as data.

Syntax and arguments

Example

A file of account numbers was loaded with the account numbers as its headers, because the file had no heading row. The numbers are demoted to a data row, and the columns are renamed for the next step.

let
    Source = Excel.CurrentWorkbook(){[Name = "AccountList"]}[Content],
    AsData = Table.DemoteHeaders(Source),
    Named = Table.RenameColumns(AsData, {{"Column1", "Account"}})
in
    Named

Each account number is now a data row, and the column that holds them has a usable name.

Things to watch

In business terms

Some lists, such as a single column of codes, are exported with the codes in the heading. Demoting the headers puts them back in the table as data, which is what a lookup or join needs.

Related functions

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