Splitting Names without separator

[SamWiseOwl]

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 number.

The data returned has no spaces between the names and some names can have multiple capitals (darn my fellow Scots).

So far I have extrated the capitals and the space position.

I feel like I am close but can't put the final pieces together.

Maybe something like ignore a capitals position if it is within 3 characters of the last one. 

I want to split everyone on the call out or at least add seperators between them.

Here is the M code using a loaded table:

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)
in
Text.Combine(List.Transform(Result, 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(_)), ", "))
in
#"Added Custom1"

Any help logical or code appreciated!

Sam

[bhanu_gautam]

@SamWiseOwl , 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 · Accepted solution]

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!

[dufoq3]

You're welcome. Enjoy 😉