Dynamically Rename Columns in Power Query

Introduction

We are going to show how to dynamically rename columns in Power Query using data that has the old column names and new column names stored in two columns. It will look something like:

The source for this data can be wherever and however you like that can be accessed by your team. The easiest solution is probably to maintain it as a CSV or Excel file, but since those files can accidentally be deleted, if you are able to maintain the column renaming data in a database, that would be even better.

Problem

Having a consistent column naming convention is important for anyone that works with data. Otherwise you end up with columns like this…

EmployeeNumber, Employee_Number, Employee#, Employee_#, EmployeeID, Employee_ID, EmpNumber, etc.

…that all mean the same thing.

While the users working with the data regularly may know that each field means the same thing, it would be much better if we had a consistent naming convention that everyone uses and understands.

Power Query allows us to manually rename columns pretty easily. Take this sample employee data here:

Say that as an organization, we decided we want these three respective fields to be called EmployeeNumber, EmployeeName, and ManagerNumber. For whatever reason, our source data didn’t have the correct column names, so we can right-click each column name, select Rename, and type in whatever we want it to be.

After renaming our columns, we can see the results, along with the syntax used to generate that result:

You’ll notice that it renames the columns by using a list of lists. Each individual list is ordered with old column name first and new column name second. Take note of this syntax as we will use this later.

Unfortunately, there are some problems with this manual approach:

1. Not everyone will automatically know what the correct column names are. You may keep documentation on them somewhere, but a new employee may not know about that immediately.
2. Someone could easily make typos when renaming.
3. Someone could forget to rename a column on accident.
4. This will get extremely tedious the more columns that exist in the data. In our example, it is only three columns, but if you have a source with 50+ columns to rename to a standard naming convention, that would quickly get tiresome.

Let’s dive into a better way to rename columns.

Note: I am using Power Query in Power BI, but I assume this would also apply to Power Query in Excel.

Solution

Part 1: Bring in your working data, along with the column rename data
We start with our working data (data we are using to build report):

We also bring in our column rename data (this should include all possible column names that could be renamed):

Part 2: Create a custom column of lists
We want to create a new column that makes a list of the Old Column Names and New Column Names values.

Select Add Column > Custom Column:

Create the new column of lists by using curly brackets around the column names:

Part 3: Turn your lists column into a list
If you remember the syntax from our manual renaming above, Power Query renames columns by using a list of lists. So we need to convert our new custom column into a list. There’s at least two ways you can do this. You only have to do one of them.

• Option 1: Right-click your new custom column (Column Name List in our example), and select Drill Down.

• Option 2: Select your new custom column, and select Transform > Convert to List.

Now Column Renames, that used to be a table, is a list of lists.

Part 4: Use your list of lists to rename your table columns
We will now use our Column Renames list to rename columns in Sample Employee Data.

Open your Advanced Editor, and add in a Table.RenameColumns function:

Notice in line 6, when we are renaming the columns from the previous step called Changed Type, that we aren’t hard-coding our column renames, but rather using our Column Renames list that we generated from our column rename data.

We also added the MissingField.Ignore parameter to the function because we want to ignore any columns in our list that don’t exist in our data. This additional parameter is important because our column rename data had a value of Employee_ID that doesn’t exist in our working data. If we didn’t include this parameter, it would error out because it couldn’t find the Employee_ID column to rename.

Lastly, make sure that you update line 8 with your new step name, so that Power Query interprets your logic correctly.

Click Done in Advanced Editor to apply your changes.

Part 5: Begin your work!
You have now taken this…

…and turned it into this…

…without having to manually change any column names. Now you can begin building out your report and using the standardized column names that your organization has agreed upon.

Conclusion

Hopefully this will help you and your team in having standard naming convention for columns so that solutions you deliver are more consistent.

Thanks for reading!