Forum Discussion
Merge two tables as a list
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] |
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! 🙂
2 Replies
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! 🙂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"
