A Power Query Custom Function to Rename all Columns at Once in a Table

I am involved with a Power BI development in the past few days. I got some data exported from various systems in different formats, including Excel, CSV and OData. The CSV files are data export dumps from an ERP system. Working with ERP systems can be very time consuming, especially when you don’t have access to the data model, and you get the data in raw format in CSV files. It is challenging, as in the ERP systems, the table names and column names are not user friendly at all, which makes sense. The ERP systems are being used in various environments for many different customers with different requirements. So if we can get our hands to the underlying data model, we see configuration tables keeping column names. Some of the columns are custom built to cover specific needs. The tables may have many columns that are not necessarily useful for analytical purposes. So it is quite critical to have a good understanding of the underlying entity model. Anyhow, I don’t want to go off-topic.

The Problem

So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!

Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.

Background

I emailed back to my customer, asking for their help. Luckily they have a very nice data expert who also understands their ERP system as well as the underlying entity model. I emailed him all the current column names and asked if he can provide more user-friendly names. He replied me back with a mapping table in Excel. Here is an example to show the Column Names Mapping table:

I was quite happy with the mapping table. Now, the next step is to rename all columns is based on the mapping table. Ouch! I have almost 800 columns to rename. That is literally a pain in the neck, and it doesn’t sound quite right to burn the project time to rename 800 columns.

But wait, what about writing automating the rename process? Like writing a custom function to rename all columns at once? I recall I read an excellent blog post about renaming multiple columns in Power Query that Gilbert Quevauvilliers wrote in 2018. I definitely recommend looking at his blog post. So I must do something similar to what Gilbert did; creating a custom function that gets the original columns names and brings back the new names. Then I use the custom function in each table to rename the columns. Easy!

The Solution

Before we start, I need to have my mapping table in Power BI. So I create a new table using the Enter Data functionality. Then I copy the data from my mapping table and paste it into the new table. I name the new table Column Names Mapping. The main reason to use the Enter Data functionality instead of getting data directly from the file (in my case an Excel file) is that if for any reason in the future we miss the mapping file, the function below cannot find the columns to map. Hence, it brings back the original column names. This means if we created a data model on top of the new column names, then the whole model breaks, which is no good. I also disable load on the Column Names Mapping query as I don’t need to have it in my data model.

Creating fnRenameColumnsFromRefQuery Custom Function

The function reads through the Column Names Mapping table and renames the columns of the query that we invoked the function in when it finds the matching. If it doesn’t find the matching, it leaves the column name is. Here you go, this is the function I came up with.

//fnRenameColumnsFromRefQuery
(ColumnName as text) as text =>
let
    Source = 
        if (
            List.Contains(
                Record.FieldNames(#sections[Section1]), 
                "Column Names Mapping"
                ) 
             ) = true 
        then #"Column Names Mapping" 
        else null,
    ColumnNewName = 
        try 
            if List.Contains(Source[Column Name], ColumnName) = true 
            then 
                if Text.Trim(Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description]) = "" 
                then ColumnName 
                else Table.SelectRows(Source, each ([Column Name] = ColumnName)){0}[Description] 
            else Source 
        otherwise ColumnName
in
    ColumnNewName

You can download the preceding expressions from here.

Here is how it works:

• The fnRenameColumnsFromRefQuery accepts a ColumnName parameter as text and the output of the function is a text.
• The Source step checks the existence of a Column Names Mapping query. The Column Names Mapping is the mapping tables shown in the preceding image that holds the original column names and their mapping. You may use a different name of choice. If the Column Names Mapping query exists then Source = #"Column Names Mappings" else Source = null
• The ColumnNewName step checks the contents of the Source step which is either the Column Names Mapping table or a null. If it is the Column Names Mapping table then it looks for the ColumnName parameter in the [Column Name] column within the Column Names Mapping table. If it finds the matching value then it uses the ColumnName parameter to filters the [Column Name]. Then it gets the corresponding value from the [Description] column which contains the new column name, otherwise it brings the original column name back

The last step is to use the fnRenameColumnsFromRefQuery custom function to rename the table’s columns.

Invoking the fnRenameColumnsFromRefQuery Custom Function

Power Query has a function to renamed column names in tables which is the Table.TransformColumnNames(table as table, NameGenerator as function) function. The Table.TransformColumnNames() function accepts a NameGenerator as its second operand. So we can use our custom function as the second operand within the Table.TransformColumnNames() function to rename all columns. The final code must look like this:

Table.TransformColumnNames(PREVIOUS_STEP_NAME, fnRenameColumnsFromRefQuery)

Here is a screenshot of the column names before and after renaming the columns:

You can download the PBIX sample here.

Conclusion

If we think what we are doing is quite laborious and we are burning a lot of project time doing that, it is good to think about a way to automate the job. It might look a bit complex, confusing and time-consuming at first, but believe me, in many cases it is worth it and after you find the way you can use it in many other similar scenarios which saves you a lot of time. Having said that, there is no guarantee that we can automate our scenario, so it would be better to be prepared for it. For instance, it is a long time since I’m thinking to automate the process of renaming all queries appearing in the Query Editor, but I am yet to find a solution for it. Here is the challenge, in some cases, we connect to the data source, and we have many tables to be loaded to the model, and the table names are not always user friendly. It is similar to renaming columns but at the query level. So if you already know the solution, please share it with the community so everyone can benefit from your efforts. At the end of the day, we learn from each other. So sharing our knowledge is a paying forward to the next person.

The other point is to always remember to ask other experts for help. We might be experts in some areas but it is impossible for someone being an expert in everything. Like in my case the customer had an expert who provided me with the column names mapping table, without his help I couldn’t get the job done.

As always, I would love to know your opinion, so please share your thoughts in the comment section below.