Renaming columns

Overview

Renaming columns is a common data preparation step as it can improve the user experience. It does, however, need to be done carefully, as it can result in ETL refresh failures, if the names of the columns in the source data change.

Though columns are often renamed by their name, under certain conditions, it can be better to rename them by their position. Such renaming is best achieved with a function.

The following function, for example, (called: fnRenameColumnsByIndices) renames columns by their index position:

let
    Source = (Source as table, ColumnNamesNew as list, Indices as list) =>
let
    ColumnNamesOld = List.Transform( Indices, each Table.ColumnNames(Source){_} ), 
    ZippedList = List.Zip( { ColumnNamesOld, ColumnNamesNew } ),
    #"Renamed Columns" = Table.RenameColumns( Source, ZippedList )
in
    #"Renamed Columns"
in
    Source

Example Data with custom function

So if you had the following data:

And needed to rename columns 3 onwards to ‘Data1’, ‘Data 2’, and ‘Data 3’, instead of renaming each column, like this:

= Table.RenameColumns(Source, 
    {
        {Table.ColumnNames(Source){3}, "Data 1"},
        {Table.ColumnNames(Source){4}, "Data 2"}, 
        {Table.ColumnNames(Source){5}, "Data 3"} 
    }
)

It can be better to call the custom function from earlier, like this:

= fnRenameColumnsByIndices (
	Source,
	List.Transform (
		{1..3},
		each "Data " & Text.From(_)
	),
	{3..5}
)

The function should return the same result, without any hardcoding of column names:

Other functions

Another way of renaming columns is to use the function: Table.TransformColumnNames

The advantage of this function is that is that it can be used to rename columns based on their names, and so will work even if the source table is re-ordered. This can be better in certain situations.