Merge two tables as a list

[ryan_b_123]

I would like to join Table 2 (Team ID) onto Table 1 (Team ID) and add a list of names retrieved from the join (rather than a row for each match).  Appreciate the help!

Table 1

Shipment ID	Team ID
1	A
2	B
3	C
4	A

Table 2

Name	Team ID
Billy	A
John	A
Lisa	B
Carlos	C
Michelle	C
Tito	C

End Result

Shipment ID	Team ID	Names
1	A	[Billy, John]
2	B	[Lisa]
3	C	[Carlos, Michelle, Tito]
4	A	[Billy, John]

[rohit_singh · Accepted solution]

HI @ryan_b_123 ,

Please try this in Power Query :

1) Modify Table 2 as shown below. Perform a Group by operation on Team ID and then concatenate names into a single row. 

Table 2 : 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyalU0lFyVIrViVbyys/Ig3N8MosTgRwnMMc5sSgnvxjIdQZzfTOTM1JzclLhAiGZJfkQTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Team ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Team ID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Team ID"}, {{"Count", each _, type table [Name=nullable text, Team ID=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Name]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns"

2) Navigate to Table 1. Merge Table 1 and 2 using Team ID.

3) Expand Table 2 from the join. You need to extract only the list of names (column "Custom")

4) This will give you the final result 

Table 1 :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMoHIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Shipment ID" = _t, #"Team ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipment ID", Int64.Type}, {"Team ID", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Team ID"}, Names, {"Team ID"}, "Names", JoinKind.LeftOuter),
    #"Expanded Names" = Table.ExpandTableColumn(#"Merged Queries", "Names", {"Custom"}, {"Custom"})
in
    #"Expanded Names"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[AlexisOlson]

@ryan_b_123 @rohit_singh 

This can be done in a single step if you modify the Group By aggregation to use Text.Combine.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyalU0lFyVIrViVbyys/Ig3N8MosTgRwnMMc5sSgnvxjIdQZzfTOTM1JzclLhAiGZJfkQTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Team ID" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Team ID"}, {{"Names", each Text.Combine([Name], ", "), type nullable text}})
in
    #"Grouped Rows"