Forum Discussion

ryan_b_123's avatar
ryan_b_123
Frequent Visitor
4 years ago
Solved

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 Te...
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    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! 🙂

rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

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's avatar
    AlexisOlson
    Super User
    4 years ago

    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"