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.

 

RAWResult
buildingGedungBuilding Gedung
aktivaAssetPerusahaanAktiva Asset Perusahaan
buildingGedungBuilding Gedung
vehicleKendaraanVehicle Kendaraan
dokumenPentingPerusahaanDokumen Penting Perusahaan
Need Help
  • Jakinta's avatar
    Jakinta
    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"

     

     

3 Replies

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

     

     

  • latimeria's avatar
    latimeria
    Solution Specialist
    5 years ago

    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"