fbpx

Change Column Type By Position in Power Query #11

Change Column Type by Position

Introduction

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.

Power Query - Change Column Names - 1. Data Set

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 - Change Column Names - 2. Rename Column

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.

Power Query - Change Column Names - 3. 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 Formula

The Table.ColumnNames formula 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.

 

Power Query - Change Column Names - 4. Add Custom Step

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

Power Query - Change Column Names - 6. Table.ColumnNames

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.

Power Query - Change Column Names - 7. Use Index Number In Formula

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

Power Query - Change Column Names - 8. Hard Coded Value

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}

Power Query - Change Column Names - 9. Dynamic Coded Value

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

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

1 Response

Share Your Thoughts!