Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address *
First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Rename columns in Power Query when names change

Rename columns in Power Query when column names change

Power Query is great for extracting data from a source, reshaping it, then loading it into Excel. A standard part of the transformation process involves Power Query using column headers to reference each column. When the column headers change in the source data, it gives us a big problem.

Recently, my friend, Celia Alves released a YouTube video giving her solution to this problem. There is so much good stuff in that video showing how to use M code formulas and list techniques. You should definitely check out her video her solution: https://youtu.be/wSwXyfaXQgU. So, in this post, I want to share two ways to rename columns in Power Query when the column names change.

I’ve got two approaches that are a little different to Celia’s, so I thought I would post my solutions to this common problem in the interest of sharing other techniques.

Download the example file

I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0066 Rename columns in Power Query.zip

Watch the video


Watch the video on YouTube.

The problem

Let’s assume we receive the following file each week:

Original Source Data with columns

It doesn’t look too tricky to transform in Power Query. But it becomes tricky the following week when we receive an updated version of the file:

New Source Data with columns changed

The headings for the last 3 columns have changed. Therefore, when we refresh the query, we get the following error:

Error Message missing column

The problem with the query is the column headers are explicitly stated in the M code during the Changed Type step.

M Code problem - contains column names

So, how can we rename columns in Power Query when names change? Let’s find out.

100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Solution #1 – Using only the User Interface

The first solution is based entirely on the user interface; there are no coding requirements. There are quite a few steps, but hopefully, nothing too tricky.

Load the data in Power Query

To load the example data into Power Query:

  1. In the ribbon, click Data> Get Data > From File > From Workbook
     Get Data from Workbook - Data Ribbon
  2. Navigate to the file location and click Import
  3. The Navigator window opens, select Data and click the Transform Data button

Remove Promote Headers & Changed Type

If you have the default Power Query setup, then your preview window will look like this:

Example data after load

At present, we don’t need the Promoted Headers or Changed Type step, so delete those steps by clicking on the cross next to each.

Remove Promoted Headers and Changed Types

Note: If you have changed your default Power Query setup, you may not need to remove these steps.

Delete top 3 rows

The top 3 rows are not part of the data set, so we can remove those:

  1. Click Home > Remove Rows (dropdown) > Remove Top Rows
    Remove Top Rows
  2. Enter 3 into the Remove Top Rows dialog box and click OK
    Remove top rows dialog box

Promote headers

Now we can promote headers by clicking Home > Use First Rows as Header

Home - Use First Row as Headers

If the Changed Type step automatically appears, delete it. 

So far, so good; no column names have been included in the code.

Unpivot other columns

Next, we want to unpivot those columns with the names that change.

  1. Select all the columns with names that will not change; in this example, that is columns from Item Ref to PR on Hand.
  2. Click Transform > Unpivot Columns (dropdown) > Unpivot Other Columns
    Transform - Unpivot Other Columns

The preview window data now looks like this. The Attribute column now contains the names of the columns.

Preview window after unpivot

Add an index/modulo column

As there were 3 columns with names that change, we want to create a reoccurring numbering of 0 to 2 against each of those items. That might seem strange, but it will all become clear once it’s completed:

  • From the ribbon, click Add Column > Index Column (dropdown) > From 0
    Add index column from zero
  • With the Index column selected click Transform > Standard (dropdown) > Modulo
    Transform - Standard Modulo
  • As there are 3 columns, enter 3 into the Modulo window and click OK.
    Modulo dialog box

The preview window now looks like this. Notice the Index column has values 0, 1, 2 repeating

Preview window after modulo column added

Replace the values

Then we will replace each number with the name of the header we want to use.

  1. Change the data type of the Index column to Text, by clicking on the data type icon and selecting Text from the menu.
    Change Index column data type to text
  2. Transform > Replace Values (dropdown) > Replace Values
    Transfrom replace values
  3. In the Replace Values window, apply the following parameters
    Value To Find: 0
    Replace With: Last Week Sales
    Then click OK
    Replace values dialog box
  4. Repeat steps 2 and 3 for the other columns:
    • 1 is replaced with Two Weeks Ago
    • 2 is replaced with Three Weeks Ago

Preview window after replace values

Delete the Attribute column

We have no further use for the Attribute column created during the Unpivot process.

Select the Attribute column and click Home > Remove Column

Home - Remove columns

Pivot the header column

The Index column now contains the new headers we want to apply, so let’s Pivot on that column to create the new headers.

  1. With the Index column selected, click Transform > Pivot
    Transform - Pivot from Ribbon
  2. In the Pivot Column window, select Values from the dropdown box (as that contains the numeric values for each column). Then click OK.
    Pivot Column dialog box

Apply Changed Type

We can now apply the Changed Type step. I would generally recommend looking at each column individually and deciding on the data type, but for this illustration, we will let Power Query choose the default type.

Select all the columns and click Transform > Detect Data Type

Transform - Detect Data Type

Close and Load into Excel

Finally, we can load the table into Excel.

  1. Click Home > Close & Load (dropdown) > Close & Load To…
    Home Close & Load to
  2. From the Import Data dialog box, select a Table in the existing Worksheet in cell A1, then click OK.
    Import Data Dialog Box

This is what Excel looks like with the new column names.

Data in Excel - after transformations

Refresh the query

OK, now let’s assume we’ve got some updated data.

  • Delete the Sample Data.xlsx file
  • Rename Sample Data – New.xlsx to be Sample Data.xlsx
  • In Excel, click Data > Refresh All

The query now refreshes and… Ta-Dah! The data below loads!

New data imported into Excel

Amazing!!! The query refreshes with no errors 😀.

Solution #2 – Editing M Code

OK, now it’s time to look into another solution. This solution is much easier, but you have to feel a little confident editing the M formulas to get this right.

Initial transformations

Follow the steps in Solution #1 until the end of the Promote Headers step, then come back here.

Rename the columns manually

Go ahead and just double-click on each of the columns you wish to rename and change them.

Renamed Columns

The column names are included in the M code. Isn’t this precisely what we said we didn’t want to do!!! Yes, it is. But just wait; it’s all about to come together.

Change the M code formula

M code is not the most forgiving of languages; you need to make the syntax changes exactly as stated below (including the correct upper and lower case).

If the formula bar is not visible, click View > Formula Bar

View - Formula Bar

Select the Rename Column step and make the following changes in the formula bar. Change the highlighted text from this (the code has been wrapped for the ease of reading):

= Table.RenameColumns(#"Promoted Headers",{{
"Sales Wk of 12/27", "Last Week Sales"}, 
{"Sales Wk of 12/20", "Two Weeks Ago"}, 
{"Sales Wk of 12/13", "Three Weeks Ago"}})

to this:

= Table.RenameColumns(#"Promoted Headers",{
{Table.ColumnNames(#"Promoted Headers"){6}, "Last Week Sales"}, 
{Table.ColumnNames(#"Promoted Headers"){7}, "Two Weeks Ago"}, 
{Table.ColumnNames(#"Promoted Headers"){8}, "Three Weeks Ago"}})

So, rather than changing a column based on its name, we are changing it based on its position.

Note: “Sales Wk of 12/27” is column 6 in Power Query even though it is the 7th column. Power Query starts counting at zero, so while it’s the 7th column, the column number for Power Query is always 1 lower.

Final transformations

Now pick up Solution #1at the Apply Changes step, then work through to the end.

Once again, we have changed column names even when the source data changes.

Conclusion

This post proves that there are multiple ways of solving the same problem in Power Query. For example, Celia’s approach and my approaches above are all completely different, yet they achieve the same result.

Which method you use is up to you, but in comparing all three approaches, I hope you’ve learned a lot about using Power Query to solve problems.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address *
First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *