Camel Case Column Renaming – The new Way in #PowerBI #PowerQuery

A new splitter function has been discovered in Power Query (by Chris Webb and also others probably) which is not in the highlight notes of the February 2019 PBI release, but it is a very nice addition – Splitting Text By Character Transition. So, I had an adequate use case some time ago when I didn’t want to rename all columns from a data warehouse source by hand. At the time I solved the problem very nicely with the help of DataChant.

However, today I wanted to update the code with the new default function, and here it goes…

Again what we want to avoid is the handy work of renaming the column names by inserting spaces (as in the picture) – the source data is the AdventureWorksDW as with most data warehouses the column names are mostly already meaningful but without spaces!

CamelCaseColumns

We can find the function in the Split Column button menu (Home ribbon)

Split-command-function

And if you select a column and apply the “By Lowercase to Uppercase” you can see the function code

SplitColumn-by-transition-highlighted

Splitter.SplitTextByCharacterTransition({“a”..”z”}, {“A”..”Z”})

And using this function which splits text by the transition that we want (CamelCase) and the use of a function that dynamically renames (transforms) the column names – Table.TransformColumnNames – we will be able to split CamelCase in column names to spare us the handy work!

Final-step

Looking at the column names in the picture above… they now have spaces! Voila.

Short but powerful. Power query really is a data mashup language that is highly dynamic, not for the classic workaholic environment. As Bill Gates puts it – “Busy is the new stupid”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s