Power Query to split a column containing varying numbers of items and organize results under matching columns

[KRice]

I have recently begun using Power Query to clean up a data table that was obtained by transforming a text file from a financial business system. Each row of the table represents a payment for goods or services. Unfortunately, key transaction details are combined under the Description column. Those transaction details may include the Vendor Name (N), Vendor Number (V#), Purchase Order Number (PO#), Invoice Number (I#), Finance Group (F#), and Requisition Number (R#). There are many inconsistencies in how the data appear, as the order of these details varies from row to row, and the presence of these details varies as well. Using abbreviations to represent the six types of details listed above, the table includes row-by-row examples that contain VPN, VP, VIFRN, VIFN, NP, VIF, VIFR, N, and NI. If I split the Description column at the pipe ( | ) delimiter, I obtain the result shown in columns C:G, which is not useful for subsequent analysis.

MonthlyMaint.xlsx
	A	B	C	D	E	F	G
1		Description	Desc.1	Desc.2	Desc.3	Desc.4	Desc.5
2	VPN	V# 013578 |PO# 021623 |TUIT RES INC.	V# 013578	PO# 021623	TUIT RES INC.
3	VP	V# 009373 |PO# 175247	V# 009373	PO# 175247
4	VIFRN	V# 009243 |I# 152803 |F# SYS2 |R# 207673| COLUMBUS SALES	V# 009243	I# 152803	F# SYS2	R# 207673	COLUMBUS SALES
5	VIFN	V# 014804 |I# IN338171 |F# SYS1|NET SYSTEMS	V# 014804	I# IN338171	F# SYS1	NET SYSTEMS
6	NP	Data Analytics |PO# 2082	Data Analytics	PO# 2082
7	VIF	V# 021703 |I# 19389 |F# SYS1	V# 021703	I# 19389	F# SYS1
8	VIFR	V# 008273 |I# 030678996 |F# SYS2 |R# 201878	V# 008273	I# 030678996	F# SYS2	R# 201878
9	N	BGE HEALTH SUPPLIES	BGE HEALTH SUPPLIES
10	NI	Calls First, Inc. |I# 69177781159	Calls First, Inc.	I# 69177781159
Transactions

What I would like is an output similar to this, where all of the V#, PO#, I#, F#, R#, and Vendor Name details are shown under the correct columns. The order of these columns is not important. I would very much appreciate any help.

MonthlyMaint.xlsx
	I	J	K	L	M	N
1	V#	PO#	I#	F#	R#	Name
2	V# 013578	PO# 021623				TUIT RES INC.
3	V# 009373	PO# 175247
4	V# 009243		I# 152803	F# SYS2	R# 207673	COLUMBUS SALES
5	V# 014804		I# IN338171	F# SYS1		NET SYSTEMS
6		PO# 2082				Data Analytics
7	V# 021703		I# 19389	F# SYS1
8	V# 008273		I# 030678996	F# SYS2	R# 201878
9						BGE HEALTH SUPPLIES
10			I# 69177781159			Calls First, Inc.
Transactions

 

[Bo_Ry]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    SplitColumn = Table.ExpandListColumn(Table.TransformColumns(AddedIndex, {{"Description", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"),
    Trimmed = Table.TransformColumns(SplitColumn,{{"Description", Text.Trim, type text}}),
    GetHeader = Table.AddColumn(Trimmed, "Custom", each Text.Start([Description],Text.PositionOf([Description],"#")+1)),
    Pivoted = Table.Pivot(GetHeader, List.Distinct(GetHeader[Custom]), "Custom", "Description"),
    RenamedColumns = Table.RenameColumns(Pivoted,{{"", "Name"}}),
    KeepColumns = Table.SelectColumns(RenamedColumns,{"V#", "PO#", "I#", "F#", "R#", "Name"})
in
    KeepColumns

Book1
	B	C	D	E	F	G	H	I
1	Description		V#	PO#	I#	F#	R#	Name
2	V# 013578 |PO# 021623 |TUIT RES INC.		V# 013578	PO# 021623				TUIT RES INC.
3	V# 009373 |PO# 175247		V# 009373	PO# 175247
4	V# 009243 |I# 152803 |F# SYS2 |R# 207673| COLUMBUS SALES		V# 009243		I# 152803	F# SYS2	R# 207673	COLUMBUS SALES
5	V# 014804 |I# IN338171 |F# SYS1|NET SYSTEMS		V# 014804		I# IN338171	F# SYS1		NET SYSTEMS
6	Data Analytics |PO# 2082			PO# 2082				Data Analytics
7	V# 021703 |I# 19389 |F# SYS1		V# 021703		I# 19389	F# SYS1
8	V# 008273 |I# 030678996 |F# SYS2 |R# 201878		V# 008273		I# 030678996	F# SYS2	R# 201878
9	BGE HEALTH SUPPLIES							BGE HEALTH SUPPLIES
10	Calls First, Inc. |I# 69177781159				I# 69177781159			Calls First, Inc.
Sheet1

 

[KRice]

@Bo_Ry , Thank you very much...your solution worked great!

 

[shaowu459]

Another solution for your reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    res = Table.Combine(
                        List.Transform(
                                        Source[Description],
                                        each Table.FromRecords(
                                                                {
                                                                    Record.Combine(
                                                                                    List.Transform(
                                                                                                    Text.Split(_,"|"),
                                                                                                    (x)=>if Text.Contains(x,"#") then Record.FromList({x},{Text.Split(x," "){0}})
                                                                                                         else [Name=x]
                                                                                                  )
                                                                                  )
                                                                }
                                                              )
                                      )
                       )
in
    res

 

[shaowu459]

Change some functions:

let
    Source=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    res=Table.Combine(
                        List.Transform(
                                        Source[Description],
                                        each
                                            let
                                                a=Text.Split(_,"|")
                                            in
                                                #table(
                                                        List.Transform(a,(x)=>if Text.Contains(x,"#") then Text.Split(x," "){0} else "Name"),
                                                        {a}
                                                      )
                                      )
                     )
in
    res

 

[KRice]

@shaowu459 , Thank you for these solutions. Your offerings and the one from @Bo_Ry are much better than anything I tried before asking for help. I have several approaches to study now! I appreciate the help from both of you.

 

[shaowu459]

Glad we could help, thanks for the feedback