Forum Discussion
SamWiseOwl
Super User
Super UserSplitting Names without separator
Hello folks! I feel like I am close to an answer but having Friday brain. Client has a list of everyone who attended a call. These calls can be from departments and start with an extension numb...
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
SamWiseOwlSuper User
Super UserHi 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
Community ChampionHi 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!
- dufoq3
You're welcome. Enjoy 😉