Table.RenameColumns
Changes the names of columns, leaving the values as they are.
Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as tableReturns: The table with the new column names.
What it does
Table.RenameColumns gives columns new names. Source systems often use short or technical names such as "Txn Dt" or "orderNumber". Renaming them to plain English makes the report easier to read and lets files from different systems share the same column names.
Syntax and arguments
- table: the table to rename. Required.
- renames: a list of pairs. Each pair is the old name and the new name in quotes, for example
{{"Txn Dt", "Date"}, {"Amt", "Amount"}}. Required. - missingField: what to do when an old name does not exist. The default is an error.
MissingField.Ignoreskips missing names.
Example
A bank exports its transactions with abbreviated headings. The headings are changed to a standard set that matches the company's other bank files.
let
Source = Excel.CurrentWorkbook(){[Name = "BankExport"]}[Content],
Standard = Table.RenameColumns(Source, {{"Txn Dt", "Date"}, {"Desc", "Description"}, {"Amt", "Amount"}})
in
Standard
The data stays the same. Only the headings change, so later steps and the report use the standard names.
Example: a second, different use
A database column name is turned into a label that a report reader will understand.
let
Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
Labelled = Table.RenameColumns(Orders, {{"orderNumber", "Order number"}, {"orderDate", "Order date"}})
in
Labelled
Things to watch
- Names must match exactly, including capital letters and spaces. "Txn Dt" and "Txn dt" are different.
- Renaming in Power Query does not rename anything in the source file or database.
- A later step that uses the old name fails. Rename before the steps that use the names, or update those steps.
- Query folding: renaming usually folds into the SQL column aliases, so the step is cheap.
In business terms
Mapping each source system's headings to one standard set is how a finance team combines monthly files from different banks or systems into one clean table for reporting.