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 used for the hand-done applied renames with a call to Table.TransformColumnNames.

Change:

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

To:

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

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

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.

2 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

Leave a Reply

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