Please note javascript is required for full website functionality.


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!