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

Power Query - BlogLeadImage.png
If you've ever exported data from your ERP system and imported into Power BI, you'll be familiar with the rigmarole of renaming hundreds of columns to a more descriptive name. It's both time-consuming and prone to human error.

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:
Column Names Mapping

Column Names Mapping

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 tableNameGenerator 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, get in touch with us, and we can step you through it.

Soheil.jpg

Soheil is a Microsoft MVP (Most Valuable Professional) in Data Platform, having held his title for the past two years. He's been writing technical blog posts on his website biinsight.com since 2013. As a Business Intelligence Consultant, he can help with a huge range of services; everything from training workshops to BI strategy.