Forum Discussion
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:
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.
Community ChampionYes, 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:
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
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.