Forum Discussion
Anonymous
5 years agoNot applicable
Add space before capital word
Hi There I wanna transform data like this in power query. First font change into capital & add space before next Capital word. Thx in advance. RAW Result buildingGedung Building Gedun...
- 5 years ago
Here it is with dynamic column names and count.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSirNzEnJzEt3T00pzUtXitWJVkrMLsksS3QsLk4tCUgtKi1OzEhMzAPLYFFclpqRmZyT6p2al5JYBFOXkp9dmpuaF5CaVwJUjWxILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RAW = _t]), Count = {1..List.Max(List.Transform(Source[RAW], each Text.Length(Text.Select(_, {"A".."Z"}))))+1}, ColNames = List.Transform(Count, each "RAW."&Text.From(_) ), Split = Table.SplitColumn(Source, "RAW", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), ColNames), Merged = Table.CombineColumns(Split,ColNames,Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), Capitalized = Table.TransformColumns(Merged,{{"Merged", Text.Proper, type text}}) in Capitalizedor shorter version with transforming column...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSirNzEnJzEt3T00pzUtXitWJVkrMLsksS3QsLk4tCUgtKi1OzEhMzAPLYFFclpqRmZyT6p2al5JYBFOXkp9dmpuaF5CaVwJUjWxILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RAW = _t]), Custom1 = Table.TransformColumns(Source, {"RAW", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})}), #"Extracted Values" = Table.TransformColumns(Custom1, {"RAW", each Text.Proper(Text.Combine(List.Transform(_, Text.From), " ")), type text}) in #"Extracted Values"
neeom
2 years agoNew Member
I was also searching for a similar solution and found only one online tool here ( https://www.madeintext.com/add-space-before-capital-letters/ ) that allows to add space or custom punctuation before capital letters.
I hope it will help the community.