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,

 

[sandy666]

Your code contain different name(s) than source you show in the first post so it doesn't work to me.
eg. Historical Results
but I think you can do that yourself now

I've to go so happy querying

 

[Tigerexcel]

No problem, thanks for your help.

 

[sandy666]

Here is a single query which will do that
(I added this code to the shared file from post#9)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"TITLE", "NAME"}, {{"Count", each _, type table}}),
    Subject = Table.AddColumn(Group, "Subject", each [Count][Subject]),
    ExtractSubject = Table.TransformColumns(Subject, {"Subject", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Grade = Table.AddColumn(ExtractSubject, "Grade", each [Count][Grade]),
    ExtractGrade = Table.TransformColumns(Grade, {"Grade", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Score = Table.AddColumn(ExtractGrade, "Score", each [Count][Score]),
    ExtractScore = Table.TransformColumns(Score, {"Score", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitSubject = Table.SplitColumn(ExtractScore, "Subject", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Subject.1", "Subject.2", "Subject.3", "Subject.4", "Subject.5", "Subject.6"}),
    SplitGrade = Table.SplitColumn(SplitSubject, "Grade", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Grade.1", "Grade.2", "Grade.3", "Grade.4", "Grade.5", "Grade.6"}),
    SplitScore = Table.SplitColumn(SplitGrade, "Score", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Score.1", "Score.2", "Score.3", "Score.4", "Score.5", "Score.6"}),
    Reorder = Table.ReorderColumns(SplitScore,{"TITLE", "NAME", "Count", "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"}),
    Type = Table.TransformColumnTypes(Reorder,{{"TITLE", type text}, {"NAME", type text}, {"Count", type any}, {"Subject.1", type text}, {"Grade.1", type text}, {"Score.1", Int64.Type}, {"Subject.2", type text}, {"Grade.2", type text}, {"Score.2", Int64.Type}, {"Subject.3", type text}, {"Grade.3", type text}, {"Score.3", Int64.Type}, {"Subject.4", type text}, {"Grade.4", type text}, {"Score.4", Int64.Type}, {"Subject.5", type text}, {"Grade.5", type text}, {"Score.5", Int64.Type}, {"Subject.6", type text}, {"Grade.6", type text}, {"Score.6", Int64.Type}})
in
    Type

TITLE	NAME	Count	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
Business	Tom		ABC1010	D	72	ABC1020	D	75	ABC1110	C	63	ABC1690	D	71	ABC1500	N	45	ABC1700	P	51
Engineering	Jack		ABC4160	C	62	ABC4390	D	73	ABC4490	D	72	ABC4140	C	62
Arts	Peter		ABC4160	C	60	ABC4390	P	52	ABC4140	N	38	ABC4270	N	47
Arts	Fred		ABC6010	N	4	ABC6020	N	3	ABC6090	N	3	ABC6120	N	3
Business	Bert		ABC2020	D	73	ABC2040	N	42	ABC2050	D	73	ABC2080	N	44

 

[Tigerexcel]

Great thanks Sandy.

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day

 

[billszysz]

Hi Sandy & Tigerexcel
Here is my attempt to solve the problem (based on Sandy's file from post #9)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"TITLE", type text}, {"NAME", type text}, {"Subject", type text}, {"Grade", type text}, {"Score", Int64.Type}}),
    GroupedRows = Table.Group(Type, {"TITLE", "NAME"}, {{"lst", each List.Combine(Table.ToRows(Table.RemoveColumns(_, {"TITLE", "NAME"}))), type list }}),
    HowMany = Table.AddColumn(GroupedRows, "hm", each List.Count([lst])+2),
    SortedRows = Table.Sort(HowMany,{{"hm", Order.Descending}}),
    AddedTableColumn = Table.AddColumn(SortedRows, "Tbl", each Table.FromRows({{[TITLE], [NAME]} & [lst]})),
    Combine = Table.Combine(AddedTableColumn[Tbl]),
    Max = (List.Max(HowMany[hm])-2)/3,
    OldColNames = List.Buffer(Table.ColumnNames(Combine)),
    ColToRepl = List.Buffer(List.LastN(Table.ColumnNames(Source),3)),
    NewColNames = {"TITLE", "NAME"} & List.Combine(List.Transform({1..Max}, (x) => List.Transform(ColToRepl, (y) => Text.Combine({y, Text.From(x)},".")))),    
    Result = Table.RenameColumns(Combine, List.Zip({OldColNames, NewColNames}))
in
    Result

 

[sandy666]

@billszysz
Hi Zbyszek, long time no see. Hope all is well

 

[billszysz]

Hi Sandy )
Everything is ok. I just took a break

 

[Tigerexcel]

Hi Billszysz,

Thanks for your input, that is very clever. I have not seen some of those PQ steps before particularly the How Many added column and the List Buffer steps.

 

[shaowu459]

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

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