Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Change Column Type By Position in Power Query


As a Power Query beginner, it is comfortable to use the user interface to change your column names. It’s easy and quick. You double-click a column name, rename it and press enter. Yet, using this method may cause errors in the long run. For example, when the column name in the source data changes. In this post, you will learn how to change a column name by its position using the Table.ColumnNames formula. This may prove useful when you know a column always has a fixed position.

The Easy Way

Imagine you pulled the below data set into Power Query.

data set for exercises

You don’t like the name of the first column. So, to rename it:

  • Double click the first column name Date_MM_YY -> rename it to Date
  • Press Enter

This creates the following formula:

= Table.RenameColumns( #"Changed Type",{{ "Date_MM_YY", "Date" }} )
Rename Column formula

Power Query creates a new applied step. It uses the Table.RenameColumns formula to change from the old name Date_MM_YY to the new name Date. So far, so good. The query, as it is, assumes the source data always has Date_MM_YY as the dates column. However, your colleague sometimes provides you with data with different column names. The next time you refresh the query, it seems the date column provided by your colleague is now called Dates. Since Power Query still tries to change the column Date_MM_YY to Date, it runs into the below error.

Error After Renaming Source

After all, the column name DATE_MM_YY does not exist anymore. There is an easy way to prevent this error from happening. It involves referring to the relative position of the column name containing the dates. And to do this, I’d like to introduce you to the Table.ColumnNames formula.

Table.ColumnNames Function

The Table.ColumnNames function returns all the Column Names in a table. It takes a table as its only argument and returns the Column Names in a list.

Let’s see how that works:

  • Click the Fx button in Power Query to create a custom step.
Add custom function button
  • Next, type in: =Table.ColumnNames(#”Changed Type”). Essentially, you tell Power Query to return the table’s Column Names presented at an earlier step called ‘Changed Type’.

The result of this is a list containing all the column names of the table shown at the applied step ‘Changed Type’.

Result of the Table.ColumnNames function

Imagine you would like to refer to the first column in the list. You can do this by writing the same formula but specifying the index number of the relevant row behind it.

Note: Power Query uses an index that starts at 0. This means Column Index  = N – 1. So the first column would be 1 – 1  = Index {0}. And the third column would be 3 -1 = Index {2}.

Start by writing the same formula as before. Then add {0} to the formula.

Read more on this syntax: How to use Lists in Power Query – Complete Guide »

When you press enter, Power Query shows the resulting value. In this case, the result shows Date_MM_YY. This is the first value found in the list of Column Names. Let’s use this principle to change the column type, as at the beginning of this post.

Index number in Power Query

Change Column Type by Position

Power Query creates the below formula when you change the Column Type Date_MM_YY to Date.

= Table.TransformColumnTypes(Source,{{"Date_MM_YY", type date}})
Change colmn type using hard coded values

This formula results in an error when the column name changes. However, the trick you just learned can prevent this from happening. To do this, change the purple highlighted text from:



= Table.ColumnNames( #"Changed Type" ){ 0 }
Change column type using a dynamic coded formula

And the result is the same. Yet no matter what happens, the formula now always converts the first column to column type Date!

Share on:

Latest from my blog

  1. I’m trying to do something similar but using the column position to set which column to apply a filter to. So far I have this:

      each ( Table.ColumnNames (Source){2}) = SearchValue

    with my SearchValue coming from another query. But it doesn’t return any results (even though I know there are some there and I’ve accounted for the column numbers being zero based). I suspect I’m missing () or {} somewhere!

    • Hey Shirley,

      The M language does not allow referring to columns by their text value. That’s what Table.ColumnNames is doing.

      However, if you use Table.Column, you can input the result of your formula in the second argument.

        each Table.Column( Source, Table.ColumnNames (Source){2} ) = SearchValue

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.