Split one column with different delimiters into three new ones by rows

[montyfern]

Hi Everyone,

I've read some posts and think this will be very easy for you pros. The closest post uses the same delimiter which this data doesn't provide. Thus, I need to split this column into rows, unpivot, then split by position into a table format? I would please like: Max Goodwin to be first entry under column 1 called NAME (reverse first/last name), then column 2 called DIVISION with FAA/ABC [E] first entry under it, and column 3 is MEMBER with Member under it. They're all members. Much Obliged!

Column1

Goodwin, Max (FAA/ABC) [E]

FDA/DCC/HDRP/HAS

Member

Bond, James (FDC/FIA/ERP) [E]

FAA/DCC/HDRP/HAS

Member

Pearson, Beth (NIH/NCI) [E]

FDA/DCC/HDRP/HAS

Member

Simpson, Homer (NIH/NCI) [E]

Program Director

FAA/DCC/HDRP/HAS

Member

Griffin, Peter (NIH/NCI) [E]

Program Director

FDA/DCC/HDRP/HAS

Member

Hoyt, Jenny (NIH/NCI) [E]

ABC/DCC/HDRP/HAS

Member

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Filter = Table.SelectRows(Source, each Text.Contains([Column1], ",")),
    Split = Table.SplitColumn(Filter, "Column1", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"nm", "DIVISION"}),
    Replace = Table.ReplaceValue(Split,")","",Replacer.ReplaceText,{"DIVISION"}),
    Split1 = Table.SplitColumn(Replace, "nm", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"ln", "fn"}),
    Merge = Table.CombineColumns(Split1,{"fn", "ln"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"NAME"),
    AddCol = Table.AddColumn(Merge, "MEMBER", each "Member")
in
    AddCol

Book1
	A	B	C	D	E	F
1	Column1		NAME	DIVISION	MEMBER
2	Goodwin, Max (FAA/ABC) [E]		Max Goodwin	FAA/ABC [E]	Member
3	FDA/DCC/HDRP/HAS		James Bond	FDC/FIA/ERP [E]	Member
4	Member		Beth Pearson	NIH/NCI [E]	Member
5	Bond, James (FDC/FIA/ERP) [E]		Homer Simpson	NIH/NCI [E]	Member
6	FAA/DCC/HDRP/HAS		Peter Griffin	NIH/NCI [E]	Member
7	Member		Jenny Hoyt	NIH/NCI [E]	Member
8	Pearson, Beth (NIH/NCI) [E]
9	FDA/DCC/HDRP/HAS
10	Member
11	Simpson, Homer (NIH/NCI) [E]
12	Program Director
13	FAA/DCC/HDRP/HAS
14	Member
15	Griffin, Peter (NIH/NCI) [E]
16	Program Director
17	FDA/DCC/HDRP/HAS
18	Member
19	Hoyt, Jenny (NIH/NCI) [E]
20	ABC/DCC/HDRP/HAS
21	Member
22
Sheet2

 

[montyfern]

Absolutely brilliant JGordon11 or Austin Powers! I need to learn M code! Did you write the code from scratch? MANY thanks.

 

[JGordon11]

YW. This one can be completely done via the user interface. I used rename steps to get rid of the #"StepName" distractions and advanced editor to shorten some column names, but could be completely done via UI. However, many tasks cannot be done with just the UI so learning to use M code expands PQ capabilities manyfold over using the UI alone.