Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |