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.

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.

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.

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.