Skip to content

Change column name by position in Power Query (with 2 easy functions)

Change Column name by postion - featured image

Do you keep getting the error that states The column ______ of the table wasn’t found? It’s because the column names in the source data have changed. The thing is, if we can control the column names in our query, it is much less likely to break. There is a skill in writing queries that minimize the use of column names. One useful technique is to change column name by position in Power Query, and that’s what we are looking at in this post.

Table of contents

  1. The column names problem
  2. Power Query solution

The column names problem

Many actions in Power Query hardcode column names into the M code. To demonstrate this, let’s look at a two common scenarios – Change Type and Move Columns.

The examples are based on the following data. The issue is the Forecast Jun-22 column, as when it refreshes in 1 months’ time it will be called the Forecast Jul-22.

Rename columns due to changing names

Change Type – Change type may be applied by Power Query automatically depending on your default settings. This can even occur before we’ve had a chance to make any transformations.

= Table.TransformColumnTypes(
  #"Promoted Headers",
  {{"Item", type text}, {"Region", type text}, 
{"Forecast Jun-22", Int64.Type}}

Move Columns – Moving columns is a frustrating transformation as it hard codes all the columns, even those which haven’t moved.

= Table.ReorderColumns(#"Promoted Headers",{"Item", "Forecast Jun-22", "Region"})

Power Query assumes column names will be identical every time a query refreshes. However, if the column Forecast Jun-22 in the source file changes we get the column ‘Forecast Jun-22’ of the table wasn’t found error.

Column not found error

It’s unlikely that we can change the source file (these often come from other systems), so we need to find a Power Query solution to solve this.

Power Query solution

While there are a few Power Query solutions to deal with this scenario, we are only looking at one of these, changing column name by position. Instead of referring to columns by their name, we can refer to them by their number.


Power Query has a zero base. Therefore, the first column is 0, the second column is 1, and so on.

The strategy is to name columns before applying any other transformations. This gives us greater control, as we know every column name in the remainder of our query and in our DAX measures.

Power Query functions

For this, we will be using two Power Query functions Table.RenameColumns and Table.ColumnNames.

Let’s look at each of these in turn.

Table.RenameColumns function

The Table.RenameColumns function renames columns based on a list of pairs of text values. Within each pair is the “change from” name, followed by the “change to” name.


Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table
  • table: the name of the table to perform the transformation on. This is usually the name of the previous step.
  • renames: the list of names to change to and from
  • missingField: an optional parameter to determine what to do with missing names. For this parameter, the values can be either:
    • MissingField.UseNull: if the name cannot be found, a new column is created using the new name and containing all null values.
    • MissingField.Ignore: if the name cannot be found, no changes are made, and no errors are triggered.
    • MissingField.Error: the default behavior of displaying an error message.

More info:

Let’s see this function in action.

If we double-click the header and rename the column, the M code created will look like this:

= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast"}})

If the column Forecast Jun-22 exists, it will rename the column; otherwise, it will trigger the error.

We can add the additional Missing Field parameter into the code as shown below.

= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast", MissingField.Ignore}})

Now, no error will be created even if the name does not exist.

This has been included for information. Avoiding the error in this way may be a solution in some scenarios. However, usually this doesn’t help us. The point of renaming the columns is to give us greater understanding or control. Avoiding the error achieves neither of these. Instead, it pushes the error later in the query.

Table.ColumnNames function

If we want the name of the nth column, we use the Table.ColumnNames function to return a list of names. Then, we simply select the nth column from the list.


Table.ColumnNames(table as table) as list
  • table: the table from which to obtain the list of column names. Usually this will be the name of the previous step.

More info:

Let’s look at a few examples of this function in action.

Example 1: Return all the column names

= Table.ColumnNames(#"Promoted Headers")

This code returns a list of the column headers: {“Item”, “Region”, Forecast Jun-22″}

Example 2: Returns column name in a specific position within the list

= Table.ColumnNames(#"Promoted Headers"){2}

This code returns only the column header of the 3rd column: {Forecast Jun-22″}

Nesting functions to change column name by position

Now we understand the two functions, it’s time to use them together. The Table.ColumnNames{n} function returns the name of the column from position n. This column name is then used in the Table.RenameColumns function.

Change column name by position

This means we change this code:

= Table.RenameColumns(#"Promoted Headers",{{"Forecast Jun-22", "Forecast"}})

Into this code:

= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){2}, "Forecast"}})

The biggest risks now are:

  • the column number is not a valid column number (e.g., a negative number, or a number greater than the amount of columns).
  • the column to be renamed does not remain in the same position

But in many scenarios, this simple technique is sufficient to avoid most issues.

Other Resources

Looking for more Power Query content? Check out these resources:

Remove spaces in Power Query (the easy way)

Quick start guide to Power Query (Excel)