Change Column Type By Position in Power Query

Introduction

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.

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" }} )

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.

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.

• 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’.

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.

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}})

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:

"Date_MM_YY"

to

= Table.ColumnNames( #"Changed Type" ){ 0 }

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