Splitting a column at a variable position

Create a blank query which IS a List of Suffixes.

{"CEO","III","PhD"}

Then try this code (which refers to the Suffix query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY5BCsIwEEWvMmQ99A7SVIwiLdVd6CLE0QTatEwm4vGNXbj9vPf41irNDdzcAhfH4uDMDULb9Qh3N88xw1DnRJzVhFYdmR4/eMtrQjDGIOjI5GVlhEPeKIFJWaIUoV0Yi4RaLs/FpWoMQSN0H/JF4pv+7o5eHfsIbaD0qg9OY6+m6Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    #"Add Name" = Table.AddColumn(Source,"Name", (r)=>
        let 
            split = Text.SplitAny(r[Column A]," ,"),
            s = List.Accumulate(
                    Suffix,
                    {},
                    (s,c)=>s & List.Combine({List.FindText(split,c)})),
            Name = if s={} then Text.BeforeDelimiter(r[Column A],",") else Text.BeforeDelimiter(r[Column A], s{0}) & s{0},
            p = if s = {} then Text.AfterDelimiter(r[Column A],",") else Text.AfterDelimiter(r[Column A], s{0}),
            Position = Text.Trim(p,{" ",","})
        in 
            [Name = Name, Position = Position], type [Name=text, Position=text]),
    #"Removed Columns" = Table.RemoveColumns(#"Add Name",{"Column A"}),
    #"Expanded Name" = Table.ExpandRecordColumn(#"Removed Columns", "Name", {"Name", "Position"})
in
    #"Expanded Name"

I suspect there is a more efficient method, but this should work according to your specifications.

Data

Results

paul_cranberry Thanks for sharing such a lovely problem. just copy the following code and past it into Advance editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY5BCsIwEEWvMmQ99A7SVIwiLdVd6CLE0QTatEwm4vGNXbj9vPf41irNDdzcAhfH4uDMDULb9Qh3N88xw1DnRJzVhFYdmR4/eMtrQjDGIOjI5GVlhEPeKIFJWaIUoV0Yi4RaLs/FpWoMQSN0H/JF4pv+7o5eHfsIbaD0qg9OY6+m6Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    List = {{"PhD,","PhD"},{"CEO,","CEO"},{", III"," III"},{"Director,","Director"}},
    #"Replaced Value1" = List.Accumulate(List,Source,(a,b)=>Table.ReplaceValue(a,b{0},b{1},Replacer.ReplaceText,{"Column A"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Column A", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column A.1", "Column A.2"})
in
    #"Split Column by Delimiter"