cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeremyB Frequent Visitor
Frequent Visitor

Split CamelCase text to new column

So here's the scenario, I have 12 enums coming through to my table.  The enum text values are in CamelCase and I want to split them at the left of each upper case letter (except for the first letter since it is the beginning of the enum text value).

 

Example:

EnumOne -> Enum One

EnumTwo -> Enum Two

Etc.

 

 

Can anyone give me some guidelines on how I should go about doing this?  I'd like to do it within PowerBI dekstop via DAX or Power Query.  

 

Thanks!

5 REPLIES 5
Highlighted
MarcelBeug Super Contributor
Super Contributor

Re: Split CamelCase text to new column

It is a bit confusing if you want to split the text (into a list, or multiple columns) or insert a space.

I assume you want to insert a space.

This is a Power Query solution:

 

let
    Source = #table(type table[Enum = text],{{"EnumOne"},{"EnumTwo"}}),
    Splitted = Table.TransformColumns(Source,{{"Enum", each Text.Insert(_, Text.PositionOfAny(_,{"A".."Z"},Occurrence.Last)," "), type text}})
in
    Splitted

 

Edit: this is how the split the column into 2 columns:

 

let
    Source = #table(type table[Enum = text],{{"EnumOne"},{"EnumTwo"}}),
    #"Split Column by Position" = Table.SplitColumn(Source, "Enum", each Splitter.SplitTextByPositions({0, Text.PositionOfAny(_,{"A".."Z"},Occurrence.Last)})(_), {"Enum.1", "Enum.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Enum.1", type text}, {"Enum.2", type text}})
in
    #"Changed Type"

 

You can find information on Power Query functions in my dashboard.

Specializing in Power Query Formula Language (M)
JeremyB Frequent Visitor
Frequent Visitor

Re: Split CamelCase text to new column

Yes, I want to be able to split the text with a space at the left of each upper case letter into a new computed column.  I don't necessarily need to keep the original column though, I just need the text to be split.

 

 

I'll give these a try and see if I can get them to work.  I appreciate the help!

 

 

EDIT:

@MarcelBeug

 

Is it possible to do it for each capital letter instead of the last one?

 

Ex. 

EnumNumberOne -> Enum Number One

 

I tried changing 'Occurrence.Last' to 'Occurrence.All', but was given an error.  

MarcelBeug Super Contributor
Super Contributor

Re: Split CamelCase text to new column

Yes, it can.

 

In such cases, the biggest challenge is to design the query such that it is dynamic with regard to the number of resulting columns.

 

This requires some Advanced Editor programming, resulting in:

 

let
    Source = #table(type table[Enum = text],{{"EnumNumberOne"},{"EnumTwo"}}),
    Splitted = Table.TransformColumns(Source, {{"Enum", each Splitter.SplitTextByPositions(Text.PositionOfAny(_,{"A".."Z"},Occurrence.All))(_)}}),
    Tabled = Table.TransformColumns(Splitted,{{"Enum", each Table.FromColumns(List.Zip({_}))}}),
    ColumnNames = Table.ColumnNames(Table.Combine(Tabled[Enum])),
    NewColumnNames = List.Transform(ColumnNames, each "Enum."&Text.Middle(_,6)),
    ExpandedEnum = Table.ExpandTableColumn(Tabled, "Enum", ColumnNames, NewColumnNames),
    TransformList = List.Transform(NewColumnNames, each {_,type text}),
    Typed = Table.TransformColumnTypes(ExpandedEnum, TransformList)
in
    Typed

 

Result:

Camel Split.png

Specializing in Power Query Formula Language (M)
JeremyB Frequent Visitor
Frequent Visitor

Re: Split CamelCase text to new column

@MarcelBeug My apologies for the delayed response and thanks for the help!  I was able to get this working, although it is not supported in direct query mode.  Any suggestions on that?  I have another way to do this outside of PowerBI, so it's not the end of the world if there isn't.

MarcelBeug Super Contributor
Super Contributor

Re: Split CamelCase text to new column

Hi, sorry, I have no real experience with Direct Query mode.
I don't think this can be rewritten such that it works in Direct Query mode.

 

Maybe someone else can help you with this.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,291)