Table.DemoteHeaders
Turns the current column headers into the first row of data and gives the columns generic names.
Table.DemoteHeaders(table as table) as tableReturns: 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
- table: the table whose headers become data. Required.
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
- The generic column names are Column1, Column2 and so on. Rename them before you combine or join the table.
- The values are read as the type of the old headers, which are text, so set the types again if needed.
- The step is rarely needed. If you find yourself using it, check that the source file really has no heading row.
- Query folding: this step is a shape change on loaded data and does not fold.
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.