Transpose and separate data

[Tigerexcel]

Book4
	A	B	C	D	E
1	TITLE	NAME	Subject	Grade	Score
2	Business	Tom	ABC1010	D	72
3	Business	Tom	ABC1020	D	75
4	Business	Tom	ABC1110	C	63
5	Business	Tom	ABC1690	D	71
6	Business	Tom	ABC1500	N	45
7	Business	Tom	ABC1700	P	51
8	Engineering	Jack	ABC4160	C	62
9	Engineering	Jack	ABC4390	D	73
10	Engineering	Jack	ABC4490	D	72
11	Engineering	Jack	ABC4140	C	62
12	Arts	Peter	ABC4160	C	60
13	Arts	Peter	ABC4390	P	52
14	Arts	Peter	ABC4140	N	38
15	Arts	Peter	ABC4270	N	47
16	Arts	Fred	ABC6010	N	4
17	Arts	Fred	ABC6020	N	3
18	Arts	Fred	ABC6090	N	3
19	Arts	Fred	ABC6120	N	3
20	Business	Bert	ABC2020	D	73
21	Business	Bert	ABC2040	N	42
22	Business	Bert	ABC2050	D	73
23	Business	Bert	ABC2080	N	44
Sheet1

Would like to have the records showing (with each record component in a separate cell):
Business Tom ABC1010 D 72 ABC1020 D 75 ABC1110 C 63 etc,
Engineering Jack ABC4160 C 62 etc,

 

[Tigerexcel]

Hi, I'm new to Power Query. I tried to solve this problem and it seems worked well, post for your reference.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index =Table.AddIndexColumn(Table.UnpivotOtherColumns(Source, {"TITLE", "NAME"}, "Attr", "Value"),"Index",1),
    fx=(x)=>
        Text.From(
            List.Count(
                List.PositionOf(Table.SelectRows(Index,each [Index]<=x{4} and [TITLE]=x{0} and [NAME]=x{1})[Attr],x{2},2))),
    DeleteCol = Table.RemoveColumns(Table.AddColumn(Index,"Helper",each _[Attr]&"."&fx(Record.ToList(_))),{"Attr", "Index"}),
    Pivot = Table.Pivot(DeleteCol, List.Distinct(DeleteCol[Helper]), "Helper", "Value")
in
    Pivot

View attachment 19049

Transpose and separate data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	TITLE	NAME	Subject	Grade	Score		TITLE	NAME	Subject.1	Grade.1	Score.1	Subject.2	Grade.2	Score.2	Subject.3	Grade.3	Score.3	Subject.4	Grade.4	Score.4	Subject.5	Grade.5	Score.5	Subject.6	Grade.6	Score.6
2	Business	Tom	ABC1010	D	72		Arts	Fred	ABC6010	N	4	ABC6020	N	3	ABC6090	N	3	ABC6120	N	3
3	Business	Tom	ABC1020	D	75		Arts	Peter	ABC4160	C	60	ABC4390	P	52	ABC4140	N	38	ABC4270	N	47
4	Business	Tom	ABC1110	C	63		Business	Bert	ABC2020	D	73	ABC2040	N	42	ABC2050	D	73	ABC2080	N	44
5	Business	Tom	ABC1690	D	71		Business	Tom	ABC1010	D	72	ABC1020	D	75	ABC1110	C	63	ABC1690	D	71	ABC1500	N	45	ABC1700	P	51
6	Business	Tom	ABC1500	N	45		Engineering	Jack	ABC4160	C	62	ABC4390	D	73	ABC4490	D	72	ABC4140	C	62
7	Business	Tom	ABC1700	P	51
8	Engineering	Jack	ABC4160	C	62
9	Engineering	Jack	ABC4390	D	73
10	Engineering	Jack	ABC4490	D	72
11	Engineering	Jack	ABC4140	C	62
12	Arts	Peter	ABC4160	C	60
13	Arts	Peter	ABC4390	P	52
14	Arts	Peter	ABC4140	N	38
15	Arts	Peter	ABC4270	N	47
16	Arts	Fred	ABC6010	N	4
17	Arts	Fred	ABC6020	N	3
18	Arts	Fred	ABC6090	N	3
19	Arts	Fred	ABC6120	N	3
20	Business	Bert	ABC2020	D	73
21	Business	Bert	ABC2040	N	42
22	Business	Bert	ABC2050	D	73
23	Business	Bert	ABC2080	N	44
Sheet1

Very nice Shaowu, that is very efficient, you've achieved a lot in just a few steps!

 

[shaowu459]

Very nice Shaowu, that is very efficient, you've achieved a lot in just a few steps!

thanks for the feedback I began to learn Power Query about one month ago and I found it very interesting and enjoyed solving problems with this tool. I just learned another trick using List.Accumulate and I'd like to share to you?

transform consecutive numbers like 1,2,3,4 to 1-4 and 7,8 to 7-8.

 

[Tigerexcel]

thanks for the feedback I began to learn Power Query about one month ago and I found it very interesting and enjoyed solving problems with this tool. I just learned another trick using List.Accumulate and I'd like to share to you?

transform consecutive numbers like 1,2,3,4 to 1-4 and 7,8 to 7-8.

View attachment 19151

Thank you Shao, I've not heard of List.Accumulate until now, it would appear that it's a very versatile function.

 

[CA_Punit]

thanks for the feedback I began to learn Power Query about one month ago and I found it very interesting and enjoyed solving problems with this tool. I just learned another trick using List.Accumulate and I'd like to share to you?

transform consecutive numbers like 1,2,3,4 to 1-4 and 7,8 to 7-8.

View attachment 19151

Thanks for Sharing,, While Practicing I got how to do with formula also.

Book1
	J	K	L	M	N	O	P	Q
15	1,2,3,8,9,10,15,16			1			1-3,8-10,15-16
16				2
17				3
18				8
19				9
20				10
21				15
22				16
23
Sheet9

Cell Formulas
Range	Formula
M15:M114	M15	=IFERROR(TRIM(MID(SUBSTITUTE(J15,",",REPT(" ",LEN(J15))),(ROW(A1:A100)-ROW(A1))*LEN(J15)+1,LEN(J15)))+0,"")
P15	P15	=TEXTJOIN(",",TRUE,INDEX($M$15:$M$22,AGGREGATE(15,6,UNIQUE((((M15:M22)-INDEX($M$15:$M$22,ROW(A1:A10)+1)=-1)*ROW(A1:A10)=0)*ROW(A1:A10)),{1;2;3})+1)&"-"&INDEX($M$15:$M$22,IFERROR(AGGREGATE(15,6,UNIQUE((((M15:M22)-INDEX($M$15:$M$22,ROW(A1:A10)+1)=-1)*ROW(A1:A10)=0)*ROW(A1:A10)),{2;3;10}),COUNT($M$15:$M$22))))
Dynamic array formulas.