Change Column Type By Position in Power Query

change column type by position


As 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 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 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 dates column. However, your colleague sometimes provide 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 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 it’s only argument and returns the Column Names in a list. You can find Microsoft’s official reference here.

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 Column Names of the table presented at an earlier step called ‘Changed Type’.

The result of this is a list containing all the column names of the table showed at 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. 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 in the beginning of this post.

Index number in Power Query

Change Column Type by Position

Power Query creates 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!

Rick de Groot
About Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.