Add space before capital word

[Anonymous]

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 Gedung
aktivaAssetPerusahaan	Aktiva Asset Perusahaan
buildingGedung	Building Gedung
vehicleKendaraan	Vehicle Kendaraan
dokumenPentingPerusahaan	Dokumen Penting Perusahaan

[neeom]

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.

[Jakinta · Accepted solution]

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
    Capitalized

or 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"

[latimeria]

You can try this:

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]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "RAW", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"RAW.1", "RAW.2", "RAW.3"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Split Column by Character Transition",{{"RAW.1", Text.Proper, type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Capitalized Each Word", "Merged", each Text.Combine({[RAW.1], [RAW.2], [RAW.3]}, " "), type text)
in
    #"Inserted Merged Column"