Table.TransformColumnNames
Changes every column name with one function, such as trimming spaces or changing capital letters.
Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as tableReturns: 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
- table: the table whose headings are changed. Required.
- nameGenerator: a function that takes a name and returns the new name. Required.
- options: an optional record with settings, such as whether to ignore missing names. Optional.
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
- This changes names only. It does not change the values under them.
- If two names become the same after the change, the step fails. Check for duplicates.
- Clean headings before combining files with Table.Combine, so the matching by name works.
- Query folding: check View Native Query on database sources. Header changes are mostly metadata, but the result depends on the source.
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.