Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • Jakinta's avatar
    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
        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"