Bulk renaming columns in a table using a Power Query Custom Function

Luckily, there's an easier way. Let's step through the solution.

‍

Here's the challenge we were faced with:

• We exported ten files, including 15 tables from various systems in different formats. The systems included Excel, CSV and OData.
• Some of these tables were really wide: between 150 to 250 columns.
• The table and column names weren't user friendly (sounding familiar?), and we needed to rename them to something meaningful.

‍

Here's what we did:

Steps to renaming columns in bulk

1. Our customer kindly provided us with a Column Name Mapping table. This table had all the current column names (the Column Name column) alongside the more user-friendly names that they'd given them (the Description column in the following image). Here's an example:

We could manually rename all those in the left column with their more descriptive names - as per the right column - but there are over 800 columns to rename. Renaming all of them means wasted time, energy and, most likely, unnecessary frustration. When we're trying to make things easier for ourselves, we're in the danger zone of making it harder.

To avoid burning project time by renaming all 800 columns, we found an easier way.

‍

Use a Custom function

Writing a custom function in Power Query allows you to rename all the columns at once.

Before you start, you'll need the mapping table in Power BI.

The first step is to invoke the custom function within the Table.TransformColumnNames() function in Power Query. This step has to be repeated for each table.

• Create a new table using the Enter Data

The Enter Data functionality is a better option than getting data directly from the file (in our example, it was an Excel file). The reason for this: you're using the custom function today to build your data model, based on the new column names created by the custom function. What happens if you lose the mapping file in future? The custom function still works, but it brings back the original column names - which are different from what you already have in your data model. Hence, the whole model breaks. You don't want that.

• Copy the data from the mapping table and paste it into the new table.
• Name the new table Column Names Mapping.
• You don't need to have the mapping table in your data model, so you should also disable load on the Column Names Mapping.
• Use this fnRenameColumnsFromRefQuery Custom Function below:

‍

//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

‍

• The following custom 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 match. If it doesn't find the match, it leaves the column name as is. Here's the function:

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 are the mapping tables shown in the preceding image that hold 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]. It then gets the corresponding value from the [Description] column which contains the new column name, otherwise, it brings back the original column name.

• The last step is to invoke the fnRenameColumnsFromRefQuery custom function to rename the columns.
• Power Query has a function to rename column names in tables, which is the Table.TransformColumnNames (table as table, NameGenerator as function). The Table.TransformColumnNames() function accepts a NameGenerator as its second operand. We invoke our custom function in the second operand within the Table.TransformColumnNames() function to rename all columns. The final code looks like this:

Table.TransformColumnNames(PREVIOUS_STEP_NAME, fnRenameColumnsFromRefQuery)

• Here's a screenshot of the column names before and after renaming the columns:

‍

More info about the code

• 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 holds the original column names and their mapping. 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's the Column Names Mapping table, then it looks for the ColumnName parameter in the [Column Name] column within the Column Names Mapping. If it finds the matching value, then it uses the ColumnName parameter to filter the [Column Name]. It then gets the corresponding value from the [Description] column which contains the new column name, otherwise, it brings the original column name back.

Automating this job might look a bit complex, confusing and time-consuming at first, but it's worth it. You can save time in many other similar scenarios too. If it's something you need to do, we can step you through it.