CODEWITHSUNDEEP
excelexcel-formulapowerquery
Alexandr Kudryashov
Alexandr Kudryashov

Reputation: 631

Excel join two table by field list

Hello can you help i need join two tables in Excel by power query

First:

  1. ID
  2. Name

Second:

  1. Field
  2. Field2
  3. List of ids

I make this operation but got error

 Table.NestedJoin(#"Table2",{"List of ids"},#"Table1",{"ID"},"NewField",JoinKind.LeftOuter)

enter image description here

Update:

enter image description here

Update: enter image description here

Upvotes: 0

Views: 164

Answers (1)

horseyride
horseyride

Reputation: 21318

Try this

In Table1, adds column containing tables of matching rows from Table2, where the ID from Table2 matches one of the items from Group3ID list on Table1

Table1

let Source = #table({"ID", "Group3ID","Group1"},{{1,{80,2,100,110},"a"},{2,{70,90,4,110},"b"},{3,{60,90,3,110},"c"},{4,{60,90,3,5},"d"},{5,{50,90,100,110},"e"},{6,{30,4,1,110},"f"}}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Expanded Group3ID" = Table.ExpandListColumn(#"Added Index", "Group3ID"),
#"Merged Queries" = Table.SelectColumns(Table.NestedJoin(#"Expanded Group3ID", {"Group3ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),{"Index", "Table2"}),
#"Grouped Rows" = Table.SelectColumns(Table.Group(#"Merged Queries", {"Index"}, {{"MatchedRows", each Table.Combine(_[Table2])}}),{"MatchedRows"}),
Combined = Table.FromColumns(Table.ToColumns(Source) & Table.ToColumns(#"Grouped Rows"),Table.ColumnNames(Source)&{"Match"})
in Combined

Table2

let Source = #table({"ID", "Group3ID","Group1"},{{1,{80,2,100,110},"a"},{2,{70,90,4,110},"b"},{3,{60,90,3,110},"c"},{4,{60,90,3,5},"d"},{5,{50,90,100,110},"e"},{6,{30,4,1,110},"f"}})
in Source

Upvotes: 2

Related Questions