Splitting a column into multiple columns with Query

[jonybandana]

Hi everyone. I have something like the following table (imported from SAP):

TITLE

0- Part 123

D142

D542

S653

0- Part 453

T311

Y221

K411

M091

J818

I would like to divide this data into columns in the following way:

0- Part 123	0- Part 453
S653	T311
D142	Y221
D542	K411
	M091
	J818

This would have to be done dynamically, because I have a lot of rows that begin with "0 - " and I would like each one to get its own column.

Is this possible to do?

 

[RoryA]

I can't help thinking I'm missing something obvious, but something like this should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TITLE", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GroupName", each if Text.Start([TITLE],2) = "0-" then [TITLE] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupName"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"GroupName"}, {{"Data", each _[TITLE]}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"Data"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns1"),
    #"Added Custom1" = Table.AddColumn(#"Transposed Table", "Custom", each Table.FromColumns(Record.ToList(_), Table.ColumnNames(#"Transposed Table"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", Table.ColumnNames(#"Removed Other Columns"{0}[Custom])),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

[Fluff]

If your interested in a formula.

Fluff.xlsm
	A	B	C	D	E
1	TITLE
2	0- Part 123		0- Part 123	0- Part 453	0- part 567
3	D142		D142	T311	a123
4	D542		D542	Y221	b567
5	S653		S653	K411	c999
6	0- Part 453			M091	d785
7	T311				e354
8	Y221
9	K411
10	M091
11	0- part 567
12	a123
13	b567
14	c999
15	d785
16	e354
17
Sheet7

Cell Formulas
Range	Formula
C2:E7	C2	=LET(a,VSTACK(FILTER(ROW(A1:A200),LEFT(A1:A200,2)="0-"),COUNTA(A1:A200)+1),m,MAX(DROP(a,1)-DROP(a,-1)),DROP(REDUCE("",SEQUENCE(ROWS(a)-1),LAMBDA(x,y,HSTACK(x,EXPAND(CHOOSEROWS(A1:A200,SEQUENCE(INDEX(a,y+1,1)-INDEX(a,y,1),,INDEX(a,y,1))),m,,"")))),,1))
Dynamic array formulas.

 

[smozgur]

I can't help thinking I'm missing something obvious, but something like this should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TITLE", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "GroupName", each if Text.Start([TITLE],2) = "0-" then [TITLE] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupName"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"GroupName"}, {{"Data", each _[TITLE]}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"Data"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns1"),
    #"Added Custom1" = Table.AddColumn(#"Transposed Table", "Custom", each Table.FromColumns(Record.ToList(_), Table.ColumnNames(#"Transposed Table"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", Table.ColumnNames(#"Removed Other Columns"{0}[Custom])),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Beautiful!

If your interested in a formula.

Fluff.xlsm
	A	B	C	D	E
1	TITLE
2	0- Part 123		0- Part 123	0- Part 453	0- part 567
3	D142		D142	T311	a123
4	D542		D542	Y221	b567
5	S653		S653	K411	c999
6	0- Part 453			M091	d785
7	T311				e354
8	Y221
9	K411
10	M091
11	0- part 567
12	a123
13	b567
14	c999
15	d785
16	e354
17
Sheet7

Cell Formulas
Range	Formula
C2:E7	C2	=LET(a,VSTACK(FILTER(ROW(A1:A200),LEFT(A1:A200,2)="0-"),COUNTA(A1:A200)+1),m,MAX(DROP(a,1)-DROP(a,-1)),DROP(REDUCE("",SEQUENCE(ROWS(a)-1),LAMBDA(x,y,HSTACK(x,EXPAND(CHOOSEROWS(A1:A200,SEQUENCE(INDEX(a,y+1,1)-INDEX(a,y,1),,INDEX(a,y,1))),m,,"")))),,1))
Dynamic array formulas.

Wow! This is also nice!

Sorry for hijacking the question, but I can't stop saying these two are beautiful!

 

[JEC]

Here one more

Book1
	A	B	C	D	E	F	G
1	TITLE			0- Part 123	0- Part 453	0- part 567
2	0- Part 123			D142	T311	a123
3	D142			D542	Y221	b567
4	D542			S653	K411	c999
5	S653				M091	d785
6	0- Part 453					e354
7	T311
8	Y221
9	K411
10	M091
11	0- part 567
12	a123
13	b567
14	c999
15	d785
16	e354
17
18
Sheet1

Cell Formulas
Range	Formula
D1:F6	D1	=LET(r,Table1[TITLE],z,HSTACK(r,SCAN(TAKE(r,1),r,LAMBDA(a,b,IF(LEFT(b,2)="0-",b,a)))),c,INDEX(z,,2),u,UNIQUE(c),IFERROR(DROP(REDUCE("",u,LAMBDA(x,y,HSTACK(x,FILTER(r,c=y)))),,1),""))
Dynamic array formulas.

 

[Fluff]

Wow! This is also nice!

Cheers Suat.

 

[JEC]

And one more Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"TITLE", type text}}),
    nCol = Table.AddColumn(cTypes, "Group", each if Text.Start([TITLE],2) = "0-" then [TITLE] else null),
    fill = Table.FillDown(nCol,{"Group"}),
    grp = Table.Group(fill, {"Group"}, {{"Count", each _[TITLE]}}),
    result = Table.PromoteHeaders(Table.FromColumns(grp[Count]))
in
    result

 

[JEC]

VBA as well

Sub jec()
 Dim ar, sq, j As Long
 ar = Sheets(1).ListObjects(1).DataBodyRange
 ReDim a(UBound(ar))
 
 With CreateObject("scripting.dictionary")
   For j = 1 To UBound(ar)
     If Left(ar(j, 1), 2) = "0-" Then
       sq = .Item(ar(j, 1))
       If IsEmpty(sq) Then sq = a
       sq(0) = ar(j, 1)
       sq(UBound(sq)) = sq(UBound(sq)) + 1
       .Item(ar(j, 1)) = sq
     Else
       sq(sq(UBound(sq))) = ar(j, 1)
       sq(UBound(sq)) = sq(UBound(sq)) + 1
      .Item(sq(0)) = sq
     End If
   Next
  Sheets(1).Range("D1").Resize(UBound(sq), .Count) = Application.Transpose(.items)
 End With
End Sub