Transform Column Names in Bulk in Power Query

Transforming multiple columns at once can save you a lot of manual work. This is particularly useful when you want to apply the same transformation on each column or when you have a large number of columns to rename. This post shows different approaches to renaming columns 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. Check out this article that shows countless examples of text functions in power query.

2. Advanced Transformations

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

2.1. Conditionally Rename Columns

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 calendar table that has some columns that contain the word “date”. And you decide you want to mark all the columns that contain the word “date” with a 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 is returned, represented by the underscore (_).

2.2. Split by Capital Letter / Uppercase to Lowercase

You may have columns with names like “ProductType”, “DueDate”, “ProductColor”. To make these more readable you can split column names at each transition from lowercase to uppercase. This is a more advanced transformation and 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 case 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 column with the new column names (NewName).

Translation table for renaming

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

{ { 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.

Enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

2 thoughts on “Transform Column Names in Bulk in Power Query”

    • 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