Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SamWiseOwl
Community Champion
Community Champion

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

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

SamWiseOwl_0-1726834973360.png

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


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

1 ACCEPTED SOLUTION

Hi @SamWiseOwl, check this:

 

Output

dufoq3_0-1726847648330.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
bhanu_gautam
Super User
Super User

@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"




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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.

SamWiseOwl_0-1726836331605.png

 

As you say it being a Friday evening makes it hard!


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Hi @SamWiseOwl, check this:

 

Output

dufoq3_0-1726847648330.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Looks good thank you. Having to manually replace all the double capitals isn't perfect but its further than I got!


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

You're welcome. Enjoy 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.