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!
We can find the function in the Split Column button menu (Home ribbon)
And if you select a column and apply the “By Lowercase to Uppercase” you can see the function code
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!
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”.
The BI Journey 
Just leaving this here because I think you forgot to actually make your function copy/pasteable:
= Table.TransformColumnNames(#”Your Previous Powerquery Step Name” , each Text.Combine(Splitter.SplitTextByCharacterTransition({“a”..”z”}, {“A”..”Z”}) (_), ” ” ))
Thanks a lot though, work’s a charm!
LikeLike