Merging rows

[Tigerexcel]

I have a sample of a worksheet in its current state and just underneath it is the desired output, I need to merge these records and I don't mind if the Results (ie Math 50, Sci 45) are merged into one cell if that provides a solution.

ID	Name	Subjects	Total marks	Result 1	Result 2	Result 3	Result 4
1	Tom	4	174	Biol 42	Math 43	Sci 45	Eng 44
2	Mary	1	55	Sci 55
2	Mary	2	120	Math 60	Sci 60
ID	Name	Subjects	Total marks	Result 1	Result 2	Result 3	Result 4
1	Tom	4	174	Biol 42	Math 43	Sci 45	Eng 44
2	Mary	3	175	Sci 55	Math 60	Sci 60

I have tried Power Query but can't quite get there so am looking for anything that gets the job done.

 

[sandy666]

maybe

ID	Name	Subjects	Total marks	Result 1	Result 2	Result 3	Result 4		ID	Name	Subjects	Total marks	Result
1	Tom	4	174	Biol 42	Math 43	Sci 45	Eng 44		1	Tom	4	174	Biol 42,Math 43,Sci 45,Eng 44
2	Mary	1	55	Sci 55					2	Mary	3	175	Sci 55,Math 60,Sci 60
2	Mary	2	120	Math 60	Sci 60

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Mrg = Table.CombineColumns(Source,{"Result 1", "Result 2", "Result 3", "Result 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Trim = Table.AddColumn(Mrg, "Result", each Text.TrimEnd([Merged],",")),
    Grp = Table.Group(Trim, {"ID", "Name"}, {{"Subjects", each List.Sum([Subjects]), type number}, {"Total marks", each List.Sum([Total marks]), type number}, {"All", each _, type table}}),
    List = Table.AddColumn(Grp, "Result", each [All][Result]),
    Extract = Table.TransformColumns(List, {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract

 

[Tigerexcel]

Thanks Sandy for your solution.

I often receive the data in the following format whereby I need to link the 2 tables, output will be the same as per previous post. Could you (or anyone who knows how) show me how you'd connect all this data?

ID	Name	Age
1	Tom	18
2	Mary	20
ID	Subjects	Total marks	Result 1	Result 2	Result 3	Result 4
1	4	174	Biol 42	Math 43	Sci 45	Eng 44
2	1	55	Sci 55
2	2	120	Math 60	Sci 60

 

[sandy666]

like this?

ID	Name	Age	Subjects	Total marks	List
1	Tom	18	4	174	Biol 42,Math 43,Sci 45,Eng 44
2	Mary	20	3	175	Sci 55,Math 60,Sci 60

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Mrg = Table.CombineColumns(Source2,{"Result 1", "Result 2", "Result 3", "Result 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Trim = Table.AddColumn(Mrg, "Result", each Text.TrimEnd([Merged], ",")),
    Group = Table.Group(Trim, {"ID"}, {{"Subjects", each List.Sum([Subjects]), type number}, {"Total marks", each List.Sum([Total marks]), type number}, {"All", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [All][Result]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Join = Table.NestedJoin(Source1,{"ID"},Extract,{"ID"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Subjects", "Total marks", "List"}, {"Subjects", "Total marks", "List"})
in
    Expand

 

[Tigerexcel]

Thanks Sandy, can you please explain how you got
Join = Table.NestedJoin(Source1,{"ID"},Extract,{"ID"},"Table",JoinKind.LeftOuter)
was it through a PQ menu option, if so I couldn't find it or is it part of the M language?

 

[sandy666]

look at Merge Queries
everything is an M