Using Power Query (M) can I split a column value by uppercase characters?

[Anonymous]

Hi,

I have columns values such as the below:

JoeBloggs

TomJones

PeterKay

ChiAuYeung

I need to either add a <space> before each uppercase character (except the first character) or split the column on each uppercase character, is this possible using Power Query (M)?

Thanks.

[v-lili6-msft]

hi, @Anonymous

    You can try to this code in Power Query

let
    Source = #table(type table[Enum = text],{{"TomJones"},{"ChiAuYeung"},{"JoeBloggs"},{"PeterKay"}            }),
    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:

Best Regards,

Lin