Split Column by First Letter

Hi Anonymous ,

You could try below M code to see whteher it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMvXQNTTVNdJ3yk9SitWJVrIMBgoY6RoZ6oakpoBFgNIGhrpGCr6JRZnFxYlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Current = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  let
        MyList = Text.ToList([Current]),
        ListCount = List.Count(MyList),
        ListArr = {"A".."Z"}
    in
        Text.Combine(List.Generate(()=> [
            a = 0, 
            b = MyList {a}
        ],
        each [a] < ListCount, 
        each [
            a = [a] + 1, 
            b = if List.Contains(ListArr, MyList {a}) then " " & MyList {a} else MyList {a}
        ], 
        each [b]
        ))),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Added Custom", "Text After Delimiter", each Text.AfterDelimiter([Custom], " ", {0, RelativePosition.FromEnd}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Custom"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous 

Try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMvXQNTTVNdJ3yk9SitWJVrIMBgoY6RoZ6oakpoBFgNIGhrpGCr6JRZnFxYlgMV8IALMTQaAYCFJSgBpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Current = _t]),
    #"Added Custom" = Table.AddColumn(Source, "records", 
        ( rec ) => let 
            position = Text.PositionOfAny( rec[Current], {"A".."Z"}, 1 ), 
            result = 
                [
                    Code = Text.Start( rec[Current], position -1 ), 
                    Name = Text.Range( rec[Current], position )
                ]  
        in 
            result
        ),
    #"Expanded records" = Table.ExpandRecordColumn(#"Added Custom", "records", {"Code", "Name"}, {"Code", "Name"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded records", {{"Code", null}, {"Name", null}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Errors",{{"Code", type text}, {"Name", type text}})
in
    #"Changed Type"