Renaming all the Columns in a Table at Once using Power Query (M Language) - Excelerator BI

Renaming all the Columns in a Table at Once using Power Query (M Language)

There are some valid reasons why you may need to change the names of all the columns in a table for use in Power BI. It is of course possible to change each column name by editing it manually, but what I am going to show you today is changing the names of all the columns in a table at once using the ‘M’ language in Power Query.  The main point of this article is “how” I used M and Power Query to make this change, however it is useful to first understand the common use case.

Two Calendar Tables

It can be a common requirement to do analysis of orders and deliveries over time.  If you want to see results in a matrix highlighting orders taken in one year and delivered in a different year, you must have 2 calendar tables – an inactive relationship will not work.

In the image below I have such a matrix set up.  The order year is coming from one calendar table and the delivery year is coming from a second calendar table.

Matrix

When you are using a matrix like this, it can be difficult to tell which “Year” column is coming from which table, as shown below.

Confusing Years

One solution to this problem is to rename all the columns in each table by pre-pending Order or Delivery to the front of the existing column names.  Once that is done, it is much clearer which column is which.

Clear Years

In my video this week, I shown you 2 ways to rename all the columns in a table.

Method 1: Renaming Columns in Table Hacking the ‘M’ Code

These are the steps I used to hack the ‘M’ code in Power Query to rename all the columns.

  • Manually change a couple of column names using the UI
  • Find the List of Lists in the M Code. {{“Oldname1”, “Newname1”}, {“Oldname2”, “Newname2”}}
  • Build your own List of Lists using either Excel and or the techniques I show in the video.
  • Replace the original list of lists with the new list of lists.

Method 2: Renaming Columns in Table using ‘M’ Functions

This is a straightforward approach calling a specific function.  I used the ‘M’ function “Table.TransformColumnNames” to add a prefix “Del ” to the existing column names. With this method you can do the same transformation on every column.

Watch the Video to Learn

I recorded a walk through of both methods outlined above in the video below.  You will also learn how I use the user interface (UI) to learn more about the M Language.

Learn Power Query Tips and Techniques

Want to learn more of such Power Query tips and techniques? You can learn by enrolling to my Power Query Online Training. It is a self-paced course that has 7.5 hours of video content, worked out examples, and course material for you to practice your new skills.

Power Query Online Training

8 thoughts on “Renaming all the Columns in a Table at Once using Power Query (M Language)”

  1. This is really useful. I had a similar situation except that I had to change the name of the names instead adding a suffix. I used List.Zip to do that- it was a trick I found on youtube (forgot who it was unfortunately)

  2. Nithyanandam Raman

    Hi Matt,
    Thanks for this video and my first powerBI material(supercharge powerBI),

    it is possible that I might not under stand this problem exactly.

    will the following method work?

    use header as fist row
    transpose
    add a custom column : “Del ” + column1
    move the newly created custom column to the beginning
    remove column1
    transpose

    Regards
    Nithyanandam

    1. Yes, your method will work, but consider if your table has 1 million rows. When you transform the table, you will have 1 million columns. I don’t know what performance would be like in this scenario. But regardless, what ever approach works for you is good.

Leave a Comment

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

Scroll to Top