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.
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
Does something like this help?
letSource = #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
Awesome solution- I was wondering if I could transform the data type also based on an input table, how would I go about that
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 ResultThe above assumes that the old -> new column name mappings are in a table/query named Transforms which contains columns Old and New.
Amazing!! thanks!
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 QueryJust 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 Resultcould the transformer function be shortened by making use of the null coalesce operator?
(name as text) as text => (Transforms{[Old = name]}[New]?)??nameGreat idea! It’s nice how the null coalesce operator makes syntax more succinct. Thanks for suggesting this.