Transpose and separate data

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
379
Office Version
2016
Platform
Windows
Book4
ABCDE
1TITLENAMESubjectGradeScore
2BusinessTomABC1010D72
3BusinessTomABC1020D75
4BusinessTomABC1110C63
5BusinessTomABC1690D71
6BusinessTomABC1500N45
7BusinessTomABC1700P51
8EngineeringJackABC4160C62
9EngineeringJackABC4390D73
10EngineeringJackABC4490D72
11EngineeringJackABC4140C62
12ArtsPeterABC4160C60
13ArtsPeterABC4390P52
14ArtsPeterABC4140N38
15ArtsPeterABC4270N47
16ArtsFredABC6010N4
17ArtsFredABC6020N3
18ArtsFredABC6090N3
19ArtsFredABC6120N3
20BusinessBertABC2020D73
21BusinessBertABC2040N42
22BusinessBertABC2050D73
23BusinessBertABC2080N44
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,
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
379
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
try

corrected
Rich (BB code):
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
you didn't show expected result so maybe this?

transp.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
I was playing around with separate queries but you can do that another way

could you show what you've so far ?
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
379
Office Version
2016
Platform
Windows
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.

Rich (BB code):
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"
 
Last edited by a moderator:

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
379
Office Version
2016
Platform
Windows
Rich (BB code):
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,357
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top