Forum Discussion

JeremyB's avatar
JeremyB
Frequent Visitor
8 years ago

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 ...
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

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:

JeremyB's avatar
JeremyB
Frequent Visitor
8 years ago

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's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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.