Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

In this article, we’ll show you some simple and advanced techniques for replacing column names in bulk. From basic find and replace to more complex formulas, you can apply your logic to multiple columns at the same time.

Table of contents
Transform Column Names in Bulk in Power Query

1. Basic Transformations

The easiest way to transform column names is by using the Table.TransformColumnNames function. This function is useful when applying a similar transformation to each of your columns.

Examples are adding a prefix, capitalizing the first letters of a word, replacing underscores, etc. Let’s look at a few examples.

The syntax for this function is:

= Table.TransformColumnNames(
  table as table, 
  nameGenerator as function, 
  optional options as nullable record
)

1.1. Replacing Characters

Sometimes, column names come with characters you want to remove. For example, underscores or full stops in between words. To replace a character in all your column names at once, you can provide the following code:

= Table.TransformColumnNames( Source,  each Text.Replace( _, "_", " " ) )
// Replaces all underscores with a space

= Table.TransformColumnNames( Source,  each Text.Replace( _, ".", " " ) )
// Replaces all full stops with a space

1.2. Adding a Prefix or Suffix

In some cases, it is useful to differentiate columns that come from different tables. Let’s say you have multiple calendars in your data model.

To make it clear columns come from a specific calendar, you add a prefix or suffix to the column names with the following code:

= Table.TransformColumnNames( Source,  each "Prefix." & _ )
// Adds the text "Prefix." in front of each column name

= Table.TransformColumnNames( Source,  each _ & ".Suffix" )
// Adds the text ".Suffix" after each column name

1.3. Changing Capitalization

In a similar fashion, you can perform other transformations on your column names. Here are a few examples that work on the capitalization of letters:

= Table.TransformColumnNames( Source,  each Text.Lower( _ ) )
// Transforms column names to lowercase

= Table.TransformColumnNames( Source,  each Text.Upper( _ ) )
// Transforms column names to uppercase

= Table.TransformColumnNames( Source,  each Text.Proper( _ ) )
// Capitalizes each word in the column names

1.4. Clean or Trim Strings

When your data has unnecessary characters or spacing, Text.Trim or Text.Clean can help.

= Table.TransformColumnNames( Source,  each Text.Trim( _ ) )
// Removes leading and trailing whitespaces in column names

= Table.TransformColumnNames( Source,  each Text.Clean( _ ) )
// Removes non printable characters in column names

These are just a few examples to give you an idea. For more specific requirements, you can apply other text functions to your columns.

2. Advanced Transformations

This chapter deals with the more advanced transformations. The code may be a bit more complex, but it can really benefit your Power Query solution.

2.1. Conditionally Transform Column Names

So far, we applied the transformations on each of the columns. Yet you may find it useful to restrict the transformation to only columns that meet a certain condition.

Let’s say you have a calendar table that has some columns that contain the word “date”. You decide you want to mark all the columns that contain the word “date” with the prefix “Bingo.”. You can add your condition in your code by writing:

Table.TransformColumnNames( Source,
                            each if Text.Contains(_, "date" ) then "Bingo." & _ else _ )
// Adds a prefix to each column name that contains the text "date" 

The function first tests the condition and only prefixes any value where the condition is true. In all other cases, the original values are returned, represented by the underscore (_).

2.2. Split by Capital Letter / Uppercase to Lowercase

You may have columns with names like “ProductType”, “DueDate”, and “ProductColor”. To make these more readable, you can split column names at each transition from lowercase to uppercase. This is a more advanced transformation; the easiest way to do that is the following.

Select a text column in your model, go to Transform, select Split Column and select By Lowercase to Uppercase. This generates a formula like:

Table.SplitColumn(
  #"Split Column by Character Transition",
  "Product Color",
  Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"}),
  {"Product Color.1", "Product Color.2"}
)

// From here you only need to copy the following part: 
 = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})

This gives you a template for the function that splits your column names. You can use this template and combine it with the Table.TransformColumnNames function.

The Splitter.SplitTextByCharacterTransition function returns a function that splits text into a list of text values. Notice in the below example how I included “(_)” behind the splitter function. This instructs Power Query to perform the function on each of the current elements.

Also, since Splitter.SplitTextByCharacterTransition returns a list of strings you can wrap the results in a Text.Combine function that concatenates the values into a single text value.

Table.TransformColumnNames(
    Source, 
    each Text.Combine(
         Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})(_)
        , " ")
)
Transform Column Name by Capital Letter

2.3. Rename with Translation Table

In some cases, no general transformation will suffice. In a scenario like that, you can use a translation table to indicate rename your columns. This is not as dynamic but can prove useful if you work with multiple languages or just very specific needs. How does this work?

This solution makes use of the Table.RenameColumns function. The syntax for this function is:

= Table.RenameColumns( table as table,      // the table to rename columns on
                       renames as list,     // pairs of old and new colum names as list
                       optional missingField )

For your translation table, you can create a separate query called Rename that contains a column with the Old column names (OldName) and a column with the new column names (NewName).

Translation table for renaming

The Table.RenameColumns function needs these values in the format:

{ { OldName1, NewName1}, 
  { OldName2, NewName2}, 
  { OldName3, NewName3}, 
  { OldName4, NewName4}, 
  { OldName5, NewName5} } 

Since the Rename table contains two columns, you first need to transform these columns into the right format. You can do that either with List.Zip or with Table.ToRows.

In your main query, where you want to rename your columns, you can add one of the following two formulas.

= Table.RenameColumns( Source,
                       List.Zip( { Rename[OldName], Rename[NewName] } ),
                       MissingField.Ignore )
 
= Table.RenameColumns( Source,
                       Table.ToRows( Rename[[OldName],[NewName]] ),
                       MissingField.Ignore )

In case you try to rename a column that does not exist in your table, or when you make a spelling error, MissingField.Ignore makes sure this step does not throw an error. And when you need any changes to column names in the future, you can simply adjust the Replace table.

Conclusion

Well, there you have it! Now you know how to rename lots of columns at once using Power Query. And remember, you can form conditions in any desired form. It’s just your creativity limiting the possibilities.

Happy quering!

Share on:
  1. Hi Rick,
    Great video. My issue is I’m importing PDF pages all with many different number of columns. I delete a varied amount from each page and end up with 3 Columns which I append with all the pages. To do this all columns need the same heading in my case that is Column1, Column2, Column3. The original column names vary all the time, so you can’t say change XYZ to Column1 because next time its original name may be ABC. As I’m doing this month by month and over many years it is cumbersome. What formula would I need to create to automate this process, please?

    Reply
  2. Great stuff. How do you handle Modifying Header Names when there are duplicate headers? For instance my Old Name and NewName look like the following;
    OldNames NewName
    JPATP1 JPATP1 PT-ABC
    JPKGP1 JPKGP1 PT-ABC
    JPKOP1 JPKOP1 PT-ABC
    JPATP1 JPATP1 PT-XYZ
    JPKGP1 JPKGP1 PT-XYZ
    JPKOP1 JPKOP1 PT-XYZ
    Since the Headers are duplicated in the OldName column, I receive an error. This is just an Example above, what I have to transform is 931 headers.

    Reply
    • I just solved it. I forget that since it is a Table any duplicate header names will automatically get _1, _2, Etc… So I had to update my list table to reflect that. Once I did everything worked perfectly.

      Reply
  3. Very useful blog Rick! Thanks!
    One question: I have abbreviations in my column names which I would like to remain Uppercase. Rest of the text I would like to put in lower case.
    My idea was to convert the whole column name to lower case (as in 1.3) and use the Traslation table method to control abreviations as you mentioned in 2.3, but I only have to change the column name partially on the occurences in the table.
    How would you implement this?

    Reply
    • Hi Rick, I got this fixed using a translation table with the abbreviations in it combined with your article “Bulk Replace Using Translation Table” 🙂

      Reply
  4. Hello Rick,

    I had to clean column names and repeat that several times in the same project, so I created a function and call that every time needed.

    = Table.RenameColumns(#"Replace Null", fx_CleanColumnNames(#"Replace Null"))

    and the function fx_CleanColumnNames

    step 1: declare replacers, could be excel table also
    step 2: get column names
    step 3: replace
    step 4: zip in format for table.renamecolumns

    (ColumnNamesTable) =>
    
    let
        #"Replacers" = #table(
            {"Old", "New"}, 
            {
                {"old1", "new1"}, 
                {"old2", "new2"}, 
            }
        ),
        
        #"Column Names Raw" = Table.FromList(
            Table.ColumnNames(
                ColumnNamesTable)
            ),
        
        #"Replaced Values" = Table.AddColumn(
            #"Column Names Raw",
            "Column2",
            each List.Accumulate(
                Table.ToRecords(
                    #"Replacers"), 
                [Column1],
                (valuetoreplace, replaceOldtoNew) =>
                    Text.Replace(
                        valuetoreplace,
                        replaceOldtoNew[Old],
                        replaceOldtoNew[New]
                    )
                ),
                type text),
        
        #"Column Rename List" = List.Zip(
            {#"Column Names Raw"[Column1], #"Replaced Values"[Column2]}
        )
    in
        #"Column Rename List"

    Off course you can put whatever you want in that function.
    Hope this helps somebody out.
    Cheers, Stijn

    Reply
    • Thanks Konstantin. I also made a slight change to chapter 2.2. Which now better explains what’s happening with the Splitter.SplitTextByCharacterTransition. Hope you like the article 🙂

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.