Not merging records in PQ

[Tigerexcel]

Hi all,

I've been working on a PQ situation and did post in the general Excel Questions, I have progressed the solution (a little) but have reached a snag.

I have 2 tables,

Student Code	Last Name	First Name	Course
1	Smith	Fred	2607
2	Jones	Jack	2645
3	Charles	Tom	2607
4	Harris	Bert	2608
5	Ellis	Bob	2638
6	Jenkins	Harry	2638

Person ID	Part	SUA	Pass	Fail	Total Marks	001	002	003	004
1	2	4	3	1	221	Biol 60	Maths 70	Sci 40	Eng 51
2	2	5	3	1	219	Biol 60	Maths 70	Sci 38	Eng 51
3	1	4	3	1	217	Biol 60	Maths 68	Sci 38	Eng 51
4	2	4	4		230	Biol 60	Maths 70	Sci 50	French 50
5	1	1	1		72	Biol 72
5	2	3	1	2	115	Maths 50	Sci 40	Eng 25
6	1	4	4		221	Biol 60	Maths 70	Sci 40	Eng 51
7	2	4	3	1	240	Biol 60	Maths 70	Sci 70	Eng 40
8	2	8	4	4	302	Biol 60	Maths 70	Sci 100	Eng 72

Eventual output would be Id, last name, first name, Course, Part, SUA, Pass, Fail, Total Marks, Results (all in one cell).
I have had some assistance from Sandy666 and I have amended his solution to try and get mine to work but no matter what Bob Ellis who is ID 5 always appears on 2 rows ie I cannot merge his record successfully.
Here is Sandy's suggestion (which I played around with a little) which seemed to work with some mock data but I cannot get it to work on the data in this post.

let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Columns" = Table.CombineColumns(Source2,{"001", "002", "003", "004"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
Trim = Table.AddColumn(#"Merged Columns", "Result", each Text.TrimEnd([Merged], ",")),
#"Grouped Rows" = Table.Group(Trim, {"Person ID"}, {{"Subjects", each List.Sum([SUA]), type number}, {"Total marks", each List.Sum([Total Marks]), type number}, {"All", each _, type table}}),
List = Table.AddColumn(#"Grouped Rows", "List", each [All][Result]),
Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Join = Table.NestedJoin(Source1,{"Student Code"},Table2,{"Person ID"},"Table",JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Table", {"Course", "SUA", "Pass", "Fail", "Total Marks", "001", "002", "003", "004"}, {"Course", "SUA", "Pass", "Fail", "Total Marks", "001", "002", "003", "004"})
in
Expand
They need to be linked

 

[sandy666]

I suggest you post a linknext time to mentioned thread

Merging rows

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...

www.mrexcel.com

and use code tags for code like [CODE] your code here [/CODE]
would be fine to see expected result based on your example

 

[sandy666]

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Mrg = Table.CombineColumns(Source2,{"1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Trim = Table.AddColumn(Mrg, "Result", each Text.TrimEnd([Merged], ",")),
    Group = Table.Group(Trim, {"Person ID"}, {{"Part", each List.Sum([Part]), type number}, {"SUA", each List.Sum([SUA]), type number}, {"Pass", each List.Sum([Pass]), type number}, {"Fail", each List.Sum([Fail]), 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,{"Student Code"},Extract,{"Person ID"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Part", "SUA", "Pass", "Fail", "Total marks", "List"}, {"Part", "SUA", "Pass", "Fail", "Total marks", "List"})
in
    Expand

 

[Tigerexcel]

Thanks Sandy! That seems to work fine. I can't see that you changed much with the code. Can you tell me what I did wrong on the previous query or what you did right on the latest query?

 

[sandy666]

at first look
Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Join = Table.NestedJoin(Source1,{"Student Code"},Table2,{"Person ID"},"Table",JoinKind.LeftOuter),
should be Extract (previous step)

see here
Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Join = Table.NestedJoin(Source1,{"Student Code"},Extract,{"Person ID"},"Table",JoinKind.LeftOuter),

you need transformed layout of Table2 not original

 

[Tigerexcel]

Thanks Sandy so I guess having Table2 in the above command creates some sort of duplication which won't allow the combination of information in the separate rows. I think that makes sense, just trying to understand the theory behind this.

 

[sandy666]

structure

   Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//this is the first table

    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Mrg = Table.CombineColumns(Source2,{"1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Trim = Table.AddColumn(Mrg, "Result", each Text.TrimEnd([Merged], ",")),
    Group = Table.Group(Trim, {"Person ID"}, {{"Part", each List.Sum([Part]), type number}, {"SUA", each List.Sum([SUA]), type number}, {"Pass", each List.Sum([Pass]), type number}, {"Fail", each List.Sum([Fail]), 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}),
//this is the second table after transforming

    Join = Table.NestedJoin(Source1,{"Student Code"},Extract,{"Person ID"},"Table",JoinKind.LeftOuter),
// here are merged two tables from above

    Expand = Table.ExpandTableColumn(Join, "Table", {"Part", "SUA", "Pass", "Fail", "Total marks", "List"}, {"Part", "SUA", "Pass", "Fail", "Total marks", "List"})
// exapanded column table after merge

You can't merge table1 and table2 with their original layouts because it doesn't make sense that is why table2 is transformed to the proper layout (see step: Extract) then you can merge Source1 (Table1) and transformed table2 (Extract)

 

[Tigerexcel]

structure

   Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//this is the first table

    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Mrg = Table.CombineColumns(Source2,{"1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Trim = Table.AddColumn(Mrg, "Result", each Text.TrimEnd([Merged], ",")),
    Group = Table.Group(Trim, {"Person ID"}, {{"Part", each List.Sum([Part]), type number}, {"SUA", each List.Sum([SUA]), type number}, {"Pass", each List.Sum([Pass]), type number}, {"Fail", each List.Sum([Fail]), 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}),
//this is the second table after transforming

    Join = Table.NestedJoin(Source1,{"Student Code"},Extract,{"Person ID"},"Table",JoinKind.LeftOuter),
// here are merged two tables from above

    Expand = Table.ExpandTableColumn(Join, "Table", {"Part", "SUA", "Pass", "Fail", "Total marks", "List"}, {"Part", "SUA", "Pass", "Fail", "Total marks", "List"})
// exapanded column table after merge

You can't merge table1 and table2 with their original layouts because it doesn't make sense that is why table2 is transformed to the proper layout (see step: Extract) then you can merge Source1 (Table1) and transformed table2 (Extract)

Ok, thank you for the explanation.

 

[sandy666]

You are welcome
Have a nice day

 

[Tigerexcel]

You are welcome
Have a nice day

And you too.