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]

Group by Title and Name then from Table split to columns

 

[Tigerexcel]

Hi Sandy,

Once you group by Title and Name a table is produced but the Split Columns option is dimmed out, can you directly split a Table into columns or is there an intermediary step?

 

[sandy666]

try

corrected

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]),
    Grade = Table.AddColumn(Subject, "Grade", each [Count][Grade]),
    Score = Table.AddColumn(Grade, "Score", each [Count][Score]),
    ExtractSubject = Table.TransformColumns(Score, {"Subject", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    ExtractGrade = Table.TransformColumns(ExtractSubject, {"Grade", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    ExtractScore = Table.TransformColumns(ExtractGrade, {"Score", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    ExtractScore

now you can split columns by delimiter and re-order as you wish

 

[sandy666]

you didn't show expected result so maybe this?

 

[Tigerexcel]

you didn't show expected result so maybe this?

View attachment 18706

Yes Sandy that is how it should look.

 

[sandy666]

I was playing around with separate queries but you can do that another way

could you show what you've so far ?

 

[Tigerexcel]

I played around a little and used this, this did start with the data in rows but from the third step it's the same as per my original post.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TITLE", type text}, {"NAME", type text}, {"Historical Results", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Historical Results", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Historical Results"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Historical Results", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Historical Results", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Historical Results.1", "Historical Results.2", "Historical Results.3", "Historical Results.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Historical Results.1", type date}, {"Historical Results.2", type text}, {"Historical Results.3", type text}, {"Historical Results.4", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Historical Results.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Historical Results.4", type text}}, "en-AU"),{"Historical Results.2", "Historical Results.3", "Historical Results.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"TITLE", "NAME"}, {{"Count", each [Merged], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Count", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Count", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4", "Count.5", "Count.6", "Count.7", "Count.8", "Count.9", "Count.10", "Count.11", "Count.12", "Count.13", "Count.14", "Count.15", "Count.16", "Count.17", "Count.18"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Count.1", type text}, {"Count.2", type text}, {"Count.3", Int64.Type}, {"Count.4", type text}, {"Count.5", type text}, {"Count.6", Int64.Type}, {"Count.7", type text}, {"Count.8", type text}, {"Count.9", Int64.Type}, {"Count.10", type text}, {"Count.11", type text}, {"Count.12", Int64.Type}, {"Count.13", type text}, {"Count.14", type text}, {"Count.15", Int64.Type}, {"Count.16", type text}, {"Count.17", type text}, {"Count.18", Int64.Type}})
in
    #"Changed Type3"

 

[sandy666]

this is not readable without tags [CODE] ... [/CODE]
so see this

s666-PQ-Transpose and separate data.xlsx

Microsoft Excel Workbook

1drv.ms

and play the game ?

 

[Tigerexcel]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TITLE", type text}, {"NAME", type text}, {"Historical Results", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Historical Results", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Historical Results"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Historical Results", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Historical Results", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Historical Results.1", "Historical Results.2", "Historical Results.3", "Historical Results.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Historical Results.1", type date}, {"Historical Results.2", type text}, {"Historical Results.3", type text}, {"Historical Results.4", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Historical Results.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Historical Results.4", type text}}, "en-AU"),{"Historical Results.2", "Historical Results.3", "Historical Results.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"TITLE", "NAME"}, {{"Count", each [Merged], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Count", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Count", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4", "Count.5", "Count.6", "Count.7", "Count.8", "Count.9", "Count.10", "Count.11", "Count.12", "Count.13", "Count.14", "Count.15", "Count.16", "Count.17", "Count.18"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Count.1", type text}, {"Count.2", type text}, {"Count.3", Int64.Type}, {"Count.4", type text}, {"Count.5", type text}, {"Count.6", Int64.Type}, {"Count.7", type text}, {"Count.8", type text}, {"Count.9", Int64.Type}, {"Count.10", type text}, {"Count.11", type text}, {"Count.12", Int64.Type}, {"Count.13", type text}, {"Count.14", type text}, {"Count.15", Int64.Type}, {"Count.16", type text}, {"Count.17", type text}, {"Count.18", Int64.Type}})
in
    #"Changed Type3"