Automating Column Name Renames

, , ,

Ever rename a table’s column names to transform their style from what makes sense in the database world to what looks attractive in a reporting environment?

Perhaps a data source returns field names like:

user_id, username, first_name, last_name, is_active_user

In your report, you’d like these columns to show up as:

User ID, Username, First Name, Last Name, Is Active User

The obvious way to achieve this is to use the query editor to manually rename each column. For five columns in one table (such as the above example), hand-transforming their names isn’t too tedious. However, imagine a report that uses ten or twenty tables, each with twenty or thirty columns—and it’s a different story.

Automate It!

Why not automate the transform? Simply create a function that accepts a database-style field name as a string and returns the transformed name as a string. Then, wire that function in so that it’s used to actually rename columns.

Transformer Function

First, build the transformer function. In the above example, the rules to convert from database-style to report-style names are simple: replace underscores with spaces, proper case each word (i.e. capitalize the first letter) and make sure that “ID” is always output in all-caps.

One way to implement these rules in Power Query is:

(name as text) as text =>
let
  #"Split into Parts" = Text.Split(name, "_"),
  #"Change Case" =
    (input as text) as text =>
      if Comparer.Equals(Comparer.OrdinalIgnoreCase, "id", input)
      then Text.Upper(input)
      else Text.Proper(input),
    #"Transformed Parts" = List.Transform(#"Split into Parts", #"Change Case"),
    Result = Text.Combine(#"Transformed Parts", " ")
in
  Result

(The above could be named something like Column Name Transformer.)

Apply the Transform

With the transformer function written, all that’s left to do is wire it in so that it transforms column names. To do this, simply replace the Table.RenameColumns code you’re using for your manually specified renames with a call to Table.TransformColumnNames.

Change:

let
  Source = …
  RenamedColumns = Table.RenameColumns(Source,{{"user_id", "User ID"}, {"first_name", "First Name"}, {"last_name", "Last Name"}, {"is_active_user", "Is Active User"}})
in
  RenamedColumns 

To:

let
  Source = …
  RenamedColumns  = Table.TransformColumnNames(Source, #"Column Name Transformer")
in
  RenamedColumns 

(The second argument to Table.TransformColumnNames specifies the transformer function to use.)

You could take this a step further and refactor the column name transformer function to take a table as its input and then internally call Table.TransformColumnNames to apply the name transformation logic.

(input as table) as table =>
  let
    Transformer = ... insert column name transformer code from above here ...
  in
    Table.TransformColumnNames(input, Transformer)

This simplifies the code needed to apply the transforms.

let
  Source = …
  RenamedColumns  = #"Column Name Transformer"(Source)
in
  RenamedColumns 

Conclusion

How easy was that?! Instead of repetitively hand-applying a set of transformation rules, those rules were turned into a Power Query function which M’s interpreter then used to automate the renames. You saved yourself from a bit of tedium—and ensured consistent application of the rename rules!

This technique isn’t limited to simple transforms whose rules can be implemented in a self-contained function. The transformer function is an ordinary function that can do anything a function can do, including reference other data sources. So, for example, if your data source’s columns are 8-character fixed-length and you have a table that maps between those cryptic names and their human-friendly equivalents (e.g. field name GL000014 = “General Ledger-Historic”), your transformer function can reference that mapping table as it comes up with the values it outputs.

Revisions

  • 2022-07-07: Added idea of having transformer function internally call Table.TransformColumnNames.

9 thoughts on “Automating Column Name Renames

  1. ADAM CAIRNS

    Thanks.
    I’m interested in the last part about using a table to map between database names and human-friendly equivalents. Would you be able to add an example of how to do this?
    Adam

    Reply
    1. Ben Gribaudo Post author

      Does something like this help?

      let
      Source = #table({"Col1", "Col2", "Col3"}, {{"some value", "another value", "one more value"}}),
      ColumnRenames = #table({"Original", "Transform"}, {{"Col1", "Column A"}, {"Col2", "Column B"}}),
      #"Column Name Transformer" = (name) => Table.First(Table.SelectRows(ColumnRenames, each [Original] = name), [Transform = name])[Transform],
      Result = Table.TransformColumnNames(Source, #"Column Name Transformer")
      in
      Result

      Reply
      1. Raj

        Awesome solution- I was wondering if I could transform the data type also based on an input table, how would I go about that

        Reply
        1. Ben Gribaudo Post author

          You could try a transformer function like:

          (name as text) as text =>
          let
            Transformed = Transforms{[Old = name]}?,
            Result = if Transformed <> null then Transformed[New] else name
          in
            Result

          The above assumes that the old -> new column name mappings are in a table/query named Transforms which contains columns Old and New.

          Reply
  2. Tom Laresch

    Ben – great stuff! I showed this to my team, and they love it, too.
    I am loading data using HQL, and for some reason, if I use certain functions in the query, it causes my column aliases to all change to lower case. I tacked on a modified version of your transformer – I made it so the delimiter is underscore OR space. I now only have to add a Rename step when I have a column name that has an ALL CAPS word.

    I am going to try using Views to fix the issue with lower case – so I may be able to remove your function. But it got me over the hump, and the team and I will keep this treasure in our tool box.
    – Tom

    Column Name Transformer

    let
      Query = (name as text) as text =>
    let
      #"Split into Parts" = Text.SplitAny(name, "_ "),
      #"Change Case" =
        (input as text) as text =>
          if Comparer.Equals(Comparer.OrdinalIgnoreCase, "id", input)
          then Text.Upper(input)
          else Text.Proper(input),
        #"Transformed Parts" = List.Transform(#"Split into Parts", #"Change Case"),
        Result = Text.Combine(#"Transformed Parts", " ")
    in
      Result
    in
      Query
    
    Reply
  3. Rocco

    Just a variation on the theme,

    let
    Source = #table({"Col1", "Col2", "Col3"}, {{"some value", "another value", "one more value"}}),
    ColumnRenames = Record.FromTable(#table({"Name", "Value"}, {{"Col1", "Column A"}, {"Col2", "Column B"}})),
    #"Column Name Transformer" = (name) => Record.FieldOrDefault(ColumnRenames, name,name),
    Result = Table.TransformColumnNames(Source, #"Column Name Transformer")
    in
    Result
    Reply
  4. Rocco

    could the transformer function be shortened by making use of the null coalesce operator?

    (name as text) as text => (Transforms{[Old = name]}[New]?)??name
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *