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"
Jakinta
Solution Sage
5 years agoHere 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"