Blogs

Power BI > How to efficiently change the column names?

Inkey, September 11, 2020 5992 Views

While preparing our Data Model, we are often tasked to change the column names of the tables which are brought into Power BI using GetData. It is very time consuming if we change the names of each column of all the tables manually but it is equally important. The time & efforts seem futile when such a simple task takes a lot of time, especially when there are more than 5 columns and we see a pattern in the column names to be changed.

Well, we have figured out a solution to do this in just a couple of seconds. Let’s try to understand this with an example. Say I have a table with the following columns:

Here, I want to change the column names: “segment” to “Segment” and if the column name has two or more words, for example, “units_sold”, I want to change it to “Units Sold”. So, I can identify a pattern as to how I want my column names to be. The pattern here is – the column names should be changed to Camel Case and the underscore should be replaced with space.

Traditionally, I would have to go to each column and change their names. But, by using an M Query function, we can achieve the same in less effort and time.
The DAX function that we’ll be using is: Table.TransformColumnNames

The syntax for the function is:
Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record)

The first parameter is the table name. Generally, we use the output of the previous step in here.

The second parameter, here, plays the main role. As you can notice, it needs to have a function. So, the function that we use should have the logic that changes the column names as we need.

Let’s write the logic that changes column names to Camel Case and replaces underscore with space. To achieve this, we will be using the below 2 Text functions – 
Text.Proper is used to capitalize only the first letter of each word.
Text.Replace is used to replace an old text with a new text.

Using these two functions, the logic can be as follows:
Text.Proper(Text.Replace(_,”_”,” “))

Note: The underscore is used to reference the current row/record of the table.

We’ll use the above logic in the TransformColumnNames function as:
Table.TransformColumnNames(Source, each Text.Proper(Text.Replace(_,”_”,” “)))

Using the above, we can transform all the column names as we want at once. All we had to do is, figure out the pattern & then develop a transformation logic. It takes a few moments to change their names.