Power Query: Space for a CamelCase
25 November 2020
Welcome to our Power Query blog. This week, I look at how to automate inserting spaces into CamelCase headings.
I have some tent data:
My headings are in CamelCase, i.e. each word begins with a capital letter, but there is no space between the words. I want to change all my headings to separated words.
I extract my data to Power Query by using ‘From Table’ in the ‘Get & Transform’ section of the Data tab.
I accept the usual defaults.
I want to investigate how Power Query would do this; I am going to demote my headings and manipulate my data.
On the Transform tab, I can ‘Use Headers as First Row’ in the dropdown from ‘Use First Row as Headers’.
I can now look at how I could divide up my data. I can use the ‘Split Column’ options on the Transform tab.
I have the option to split ‘By Lowercase to Uppercase’, which is similar to the effect I want to achieve. I try this.
This has separated my data into individual words, and now I can recombine the columns.
I select my new columns and choose ‘Merge Columns’.
I choose to use a space as the separator.
I now have the two core parts of the code I will need. I need to split the text by change of case, and then combine my text with a delimiter. In Power Query, these functions are Splitter.SplitTextByCharacterTransition() and Text.Combine().
Splitter.SplitTextByCharacterTransition(before as anynonnull, after as anynonnull) as function
This returns a function that splits text into a list of text, according to a transition from one kind of character to another. The before and after parameters can either be a list of characters, or a function that takes a character and returns true / false.
In the step, the code is:
Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})
Text.Combine(texts as list, optional separator as nullable text) as text
returns the result of combining the list of text values, texts, into a single text value. An optional separator used in the final combined text may be specified, separator.
I don’t have to demote my headings to make these changes thanks to another Power Query M function Table.TransformColumnNames():
Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table
This transforms column names by using the given nameGenerator function.
I can combine these M functions to get my result.
When I enter my new step I can see the results:
The M code I have used is:
= Table.TransformColumnNames(#"Changed Type", each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "))
This will convert CamelCase to separate words in any number of column headings.
Come back next time for more ways to use Power Query!
