Splitting a column at a variable position

[paul_cranberry]

My data looks like this...

Column A

Dr. Sam Karta Jr., CEO, Tallis Partners

Fred Sampson, III, Director, Aspen Institute

Ruth Kaufmann, PhD, Executive Director

Marci Cheng, CHRO

I need to split this column so that job titles — but not suffixes (e.g., PhD, Jr., III, etc.) — transfer to a new column. Not everyone has a suffix, and each row entry has a different number of commas, so I cannot reliably split using a delimiter.

Instead, I want PQ to look for the existence of a suffix (e.g., PhD, Jr., III, etc.) then split any remaining text (i.e., text after the suffix) into a new column. If a suffix does not exist (see Marci Cheng), PQ splits at the comma.

So, the data would look like this...

Column A	Column B
Dr. Sam Karta Jr.	CEO, Tallis Partners
Fred Sampson, III	Director, Aspen Institute
Ruth Kaufmann, PhD	Executive Director
Marci Cheng	CHRO

Can you help? Thank you so much!

[ronrsnfld · Accepted solution]

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

[v-junyant-msft]

Hi @paul_cranberry ,

Thanks for all the replies!

I see that you have posted the same thread in both the Power BI Desktop and Power Query forums and there are many users who have given their solutions. Please check to see if there is a solution for you, and if there is, please remember to accept their reply as a solution so that other users can find a solution faster, and forward that reply to the bottom of the other threads you have posted with the same problem, thank you!

Best Regards,
Dino Tao

[Omid_Motamedise · Accepted solution]

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

[ronrsnfld · Accepted solution]

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

[lbendlin]

How many gazillions of suffixes and their variations do you have?