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 Power Query to split this column so that job titles — but not the 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 first 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!

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

I have answered your question in the MS Excel forums here.  When you post across forums, please give the questions link to the others forum(s) where you have posted the same question.

[ThxAlot]

Easy enough, split the text by job titles.

[rajendraongole1]

Hi @paul_cranberry - you can achieve this in power query editor and added the description for each step, please check below m code:

use below power query editor code as bleow:

let
// Step 1: Load the Data
Source = Table.FromRows(
{
{"Dr. Sam Karta Jr., CEO, Tallis Partners"},
{"Fred Sampson, III, Director, Aspen Institute"},
{"Ruth Kaufmann, PhD, Executive Director"},
{"Marci Cheng, CHRO"}
},
{"Column A"}
),

// Step 2: Define a List of Suffixes
Suffixes = {"Jr.", "III", "PhD"},

// Step 3: Identify Suffixes in the Text
AddSuffixFlag = Table.AddColumn(Source, "HasSuffix", each
List.AnyTrue(List.Transform(Suffixes, (suffix) => Text.Contains([Column A], suffix)))
),

// Step 4: Extract the Prefix (Name and Suffix)
ExtractPrefix = Table.AddColumn(AddSuffixFlag, "Prefix", each
if [HasSuffix] then
Text.BeforeDelimiter([Column A], List.First(List.RemoveNulls(List.Transform(Suffixes, (suffix) => if Text.Contains([Column A], suffix) then suffix else null))) & ",")
else
Text.BeforeDelimiter([Column A], ",", 0)
),

// Step 5: Extract the Job Title and Remaining Text
ExtractJobTitle = Table.AddColumn(ExtractPrefix, "JobTitle", each
if [HasSuffix] then
Text.TrimStart(Text.AfterDelimiter([Column A], List.First(List.RemoveNulls(List.Transform(Suffixes, (suffix) => if Text.Contains([Column A], suffix) then suffix else null))) & ","))
else
Text.TrimStart(Text.AfterDelimiter([Column A], ",", 0))
),

// Step 6: Clean Up Columns
RemoveColumns = Table.SelectColumns(ExtractJobTitle, {"Prefix", "JobTitle"}),

// Step 7: Rename Columns
RenameColumns = Table.RenameColumns(RemoveColumns, {{"Prefix", "Column A"}, {"JobTitle", "Column B"}})
in
RenameColumns

[paul_cranberry]

This was close. It dynamically split the data based on the presence of certain suffixes, but it deleted those suffixes unnecessarily from column A.