Forum Discussion
Super UserSplitting Names without separator
Hi SamWiseOwl, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}}), Ad_NamesWithSeparator = Table.AddColumn(ChangedType, "Names with Seperator", each [ a = Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, (x)=> not List.Contains({"a".."z", " "}, x))([Names]), "|"), b = Text.Replace(a, "Mc|", "Mc") ][b], type text) in Ad_NamesWithSeparator
Super UserSamWiseOwl , You can try below code, but not sure since it is Friday evening
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Capital position", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = Text.Upper(Text.Middle(Source, _ - 1, 1)) and Text.Middle(Source, _ - 1, 1) <> Text.Lower(Text.Middle(Source, _ - 1, 1)) then _ else null),
Result = List.RemoveNulls(Indexes),
FilteredResult = List.FirstN(Result, 1) & List.Select(Result, each _ > List.Last(List.FirstN(Result, 1)) + 3)
in
Text.Combine(List.Transform(FilteredResult, each Text.From(_)), ", ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Space positions", each let
Source = [Names],
Length = Text.Length(Source),
Indexes = List.Transform({1..Length}, each if Text.Middle(Source, _ - 1, 1) = " " then _ else null),
Result = List.RemoveNulls(Indexes)
in
Text.Combine(List.Transform(Result, each Text.From(_)), ", ")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Names with Separators", each let
Source = [Names],
CapitalPositions = Text.Split([Capital position], ", "),
Positions = List.Transform(CapitalPositions, each Number.FromText(_)),
NamesWithSeparators = Text.Combine(List.Transform(Positions, each Text.Middle(Source, _, 1) & " "), "")
in
NamesWithSeparators)
in
#"Added Custom2"
- SamWiseOwl
Hi bhanu_gautam
Thank you for the response, sadly that didn't play ball.
The goal on this data would be a split where each black line is.
The final collumn was generated by the above code.
As you say it being a Friday evening makes it hard!
- dufoq3
Community Champion
Hi SamWiseOwl, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1NC4JAFEX/ysN1LZTMdVoE9rGZwIW4eDlPefCcgTET/32DUYLLyzn33rIMUvuEI75ZK+zgVp8HdhRUmzLIqWkgt4b6OR605wqNFnJ3rq0QXFh3aDJBX4GU0M3iw3aQuYF7WsWUDKgXix/4HrAn6BxNqxiFSbwdfcOOUpDRExS/tUXa76K/NKMrtShwwlYoiZNwYdUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}}), Ad_NamesWithSeparator = Table.AddColumn(ChangedType, "Names with Seperator", each [ a = Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, (x)=> not List.Contains({"a".."z", " "}, x))([Names]), "|"), b = Text.Replace(a, "Mc|", "Mc") ][b], type text) in Ad_NamesWithSeparator- SamWiseOwl
Looks good thank you. Having to manually replace all the double capitals isn't perfect but its further than I got!
