Renaming Table Column Headings

Overview

In this article, we describe a technique for more easily renaming table column headings.

Changing Column Headings

Sometimes, you need to rename the column headings of an already existing table. This can happen, for example, if you are analysing Order times against Delivery times, but need to have two separately filterable calendar tables – but whose headings you want to be different so you can more clearly distinguish the fields across the two calendar tables.

You could, for example, pre-pend the column headings of one calendar table, with ‘Order’, and then pre-pend, manually, all the column headings of the other calendar table, with ‘Delivery’.

However, instead of renaming each column one by one, the entire process can be completely automated. This article explains how.

There are two different ways of doing this, and both employ the use ‘M’ code – the programming language that underlies Power Query.

Steps

1. Duplicate the ‘Order Calendar’ query

Let’s say you have an ‘Order Calendar’ query that looks as follows:

  • Create a Duplicate the Query, by going to the Queries pane on the far left. Right click on ‘Order Calendar’, and select Reference

This will create a copy of the Calendar.

  • Rename the query to ‘Delivery Calendar’

2. Create a List of Lists with the old and new column headings

The objective is to now rename the headings of the columns in this new query.

  • In the ‘Delivery Calendar’ query, add a second step, by entering the following in the formula bar:
M-Code 
    = Table.ColumnNames(Source)

This will transform the table into a list, showing the column headings.

  • Convert the list into a table, by clicking on the ‘To Table’ icon
  • Add a custom column, with the following settings:

The table will have a new column, showing the new headings.

  • Now, convert the table into a list, by wrapping the formula in the earlier ‘#Add Custom’ step with the ‘Table.ToRows’ function, as shown below:
M-Code 
    = Table.ToRows(Table.AddColumn(#"Converted to Table", "Custom", each "Del " & [Column1]))

The information in the query will now be shown as a list of lists, as follows:

3. Rename the columns using the List of Lists

  • Rename the last step to ‘ListOfLists’, as shown below
  • Add the following M-code as a last new step:
M-Code 
    = Table.RenameColumns(Source, ListOfLists)

The table will be shown as follows, with each column heading now pre-pended with “Del”:

4. Alternative Method

This method is quicker but less flexible than the ‘List of Lists’ method where, if you wanted to, you could load your own custom transformation list, separately, elsewhere, from within Excel.

With the Alternative Method,

  • Use the following formula, as the step after the ‘Source’ step.
M-Code 
    = Table.TransformColumnNames(Source, each "Del " & _)

This will return the same result as the earlier method (no more steps are needed) - with all column headings pre-prended with the word: “Del”