Power Query Pattern – Adding Spaces in Text within your data with Camel Case
In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column.
To get this to work for you, all that you do is need to make one change to the code.
Below is what the data looked like
Then I created the following Power Query Pattern below.
#"TRANSFORM - Camel Case" =
Table.TransformColumns(
#"Removed Columns3",
{{"Operation",
each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "), type text}})
- To use this pattern below are the following changes that you will need to make it work in your Power Query Editor
-
Line 1
- This is my step name
-
Line 2
- This is where I am using the Table.TransformColumns
- NOTE: Even though this does appear to only be used for transforming columns, it works for data within a column.
-
Line 3
- This is referring to the previous step name, which is returning the table contents
-
Line 4
- This is my column name where I want to add in the spaces. As with my image above the column was named “Operation”
- NOTE: This is the only part of the pattern that needs to be changed.
-
Line 5
- This is where it finds the Upper Case and puts in a space.
- As well as changes the column type to text.
- I did get this code originally from Ivan Bond’s great blog post How List.Zip helps with Renaming Columns in Power Query
What I did to get this to work is I was in my Power Query Editor Window
I then clicked on Advanced Editor in the Home ribbon
I then added this step into my code as shown below.
I then clicked on Done
I went back to my column and I could now see the data with the spaces after each capital word
Conclusion
As you can see I have demonstrated how Powerful Power Query (see the multiple use of Power!) is to get the data in the shape that you require.
If you have any suggestions or comments please let me know.
Great post (one of many)! However… using this exact method (on the same table) I run into a small problem. Any ideas on how we’d stop it from spacing out multiple uppercase characters (like “AAD”)? This has raised it’s ugly head in a couple of cases with me where acronmys have been being used.
Hi there
Thanks for the comment and I would not know off the top of my head how to escape it when there is a double more more Upper case values.
What I would do in my case, if there were not too many columns, would be to go and manually rename those columns after the auto rename.
Fantastic example. I used it to add space to separate column names
= Table.TransformColumnNames(#”step”, each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {“A”..”Z”},2)) (_), ” “))
Thanks for sharing, that is really cool!