Downloads
Power Query MTable: columnsIntermediate

Table.TransformColumnNames

Changes every column name with one function, such as trimming spaces or changing capital letters.

Syntax
Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table

Returns: The table with its column names changed. The values are not changed.

What it does

Table.TransformColumnNames applies a function to every column name in one step. It is the fast way to clean a set of headings that all have the same problem, such as extra spaces or inconsistent capitals. For a few specific renames, Table.RenameColumns is clearer.

Syntax and arguments

Example

Headings in a bank export have stray spaces, such as " Date " and "Amount ". Each heading is trimmed.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Tidy = Table.TransformColumnNames(Source, each Text.Trim(_))
in
    Tidy

After the step, the headings are "Date" and "Amount", and the values under them are untouched. Later steps can use the clean names.

Example: a second, different use

Headings from two different systems use different capitals. They are all changed to lower case so they can be combined.

let
    Source = Excel.CurrentWorkbook(){[Name = "ARExport"]}[Content],
    Lower = Table.TransformColumnNames(Source, Text.Lower)
in
    Lower

Things to watch

In business terms

Exports from the same system often have inconsistent headings from one month to the next. Cleaning them all in one step is a simple way to stop a combined report from breaking.

Related functions

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