Split Column by First Letter

Hi surajch,

The following code will give you the desired result provided that:

- there are no trailing spaces at the end of the "Current" key

- the name is alway at the end of the key and doesn't contain numbers

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current", type text}, {"Goal", type text}, {"Goal2", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Current", "Current - Copy"),
   
   /* The split column by position breaks down the characters of "Current - Copy" one by row while replicating the corresponding "Current"
    key by row*/
    #"Split Column by Position" = Table.ExpandListColumn(
                                    Table.TransformColumns(
                                        #"Duplicated Column",
                                        {{"Current - Copy", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}),
                                        "Current - Copy"),
                             
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Current - Copy", type text}}),

    //Grouped rows creates a nested table for each key
    #"Grouped Rows" = Table.Group(
            #"Changed Type1",
            {"Current"},
            {{"GroupedRows", each _, type table [Current=text, #"Current - Copy"=text, Goal=text, Goal2=text, Custom=text]}}),

    //Then we add an index column to each nested table
    #"Added Custom1" = Table.AddColumn(
            #"Grouped Rows",
            "Index",
            each Table.AddIndexColumn([GroupedRows],
            "rank",
            1)),
    //we expand the nested tables into the main table
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom1", "Index", {"Current - Copy", "rank"}, {"Current - Copy", "rank"}),

    //then we add a custom column that will identify the first rank where last characters of "Current" include non-alpha characters
    #"Added Custom2" = Table.AddColumn(
        #"Expanded Index",
        "Custom",
        each Text.Length(Text.End([Current],[rank]))-Text.Length(Text.Select(Text.End([Current],[rank]),{"a".."z","A".."Z"}))),
    
    //then we filter out all the ranks that would include non-alpha characters
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom] = 0)),

    /*finally, we group again by "Current" and retrieve the max rank for each key 
    (this will be the number of characters at the end of the key that correspond to the name)*/
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Current"}, {{"Counter", each List.Max([rank]), type number}}),
    //the rest is just basic custom columns using the obtained number as the variable
    #"Added Custom3" = Table.AddColumn(#"Grouped Rows1", "Name", each Text.End([Current],[Counter]),type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "ID", each Text.Start([Current],Text.Length([Current])-([Counter]+1)), type text)
in
    #"Added Custom4"

Let me know if this solves your issue.

If not, can you share what failed and, if possible, the code for your query?

This would be so incredibly simple with RegEx support. 😭