none
Conditional multi Column lookup in Another Table RRS feed

  • Question

  • Hello,

    I have two tables, Table1 – Truck defined routes and Table2 – Truck routes driven.  I need to match the truck [id] in Table1 with the Truck routes driven Table2 based on (key) Type, Origin and Dest.  I can execute a join by the keys and match most of the records. 

    - Table 1 -

    id

    Type

    origin

    dest

    440656

    200

    TTT

    HHH

    440661

    300

    AAA

    HHH

    440662

    300

    HHH

    AAA

    440667

    300

    BBB

    JJJ

    440669

    200

    BBB

    KKK

    440672

    300

    CCC

    JJJ

    440673

    200

    JJJ

    JJJ

    440674

    300

    JJJ

    JJJ

    440675

    300

    HHH

    HHH


    - Table2-

    Type

    origin

    dest

    truck

    Trailer

    200

    TTT

    HHH

    10808

    307

    300

    HHH

    AAA

    13501

    1327

    300

    AAA

    HHH

    13501

    1327

    200

    BBB

    KKK

    13601

    1399

    300

    CCC

    JJJ

    13533

    1439

    200

    JJJ

    BBB

    13533

    1439

    300

    HHH

    TTT

    13533

    1439


    However, if there is no match I have default routes defined in Table1 with type, origin & dest defined.
     

    id

    Type

    origin

    dest

    440673

    200

    JJJ

    JJJ

    440674

    300

    JJJ

    JJJ

    440675

    300

    HHH

    HHH

    If the entry in Table2 does not match an entry in Table1, need to use a conditional OR lookup for [ orig ]  or [dest]  and by Type.  Example in Table 1, anything [orig] or [dest] in ‘JJJ’  and [Type] 200 has an [id] of  440673

    Table 2

    Type

    origin

    dest

    truck

    Trailer

    200

    JJJ

    BBB

    13533

    1439

    Table1

    id

    Type

    origin

    dest

     

    440673

    200

    JJJ

    JJJ

     

    So after the match Table 2 would look like :

    id

    Type

    origin

    dest

    truck

    Trailer

    440673

    200

    JJJ

    BBB

    13533

    1439


    Any Ideas

    Thursday, April 4, 2019 12:59 AM

Answers

  • Thanks, this worked great.  I've been out of pocket for a couple days.  I really appreciate the snippet of code, I've been working on a solution for a couple days.

    Cheers,

     
    • Marked as answer by GregS25 Saturday, April 6, 2019 10:13 PM
    Saturday, April 6, 2019 10:13 PM

All replies

  • Hi GregS25,

    If I understand you right, then following code should work.

    let
        Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        join1 = Table.NestedJoin(Table1,{"Type", "origin", "dest"},Table2,{"Type", "origin", "dest"},"1"),
        join2 = Table.NestedJoin(join1,{"Type", "origin"},Table2,{"Type", "origin"},"2"),
        join3 = Table.NestedJoin(join2,{"Type", "dest"},Table2,{"Type", "dest"},"3"),
        result = Table.AddColumn(join3, "result", each if Table.IsEmpty([1]) then Table.Combine({[2],[3]}) else [1]),
        remove = Table.RemoveColumns(result,{"1", "2", "3"}),
        expand = Table.ExpandTableColumn(remove, "result", {"truck", "Trailer"}),
        duplicates = Table.Distinct(expand)
    in
        duplicates
    • Proposed as answer by anthony34 Sunday, April 7, 2019 6:33 PM
    Thursday, April 4, 2019 9:29 AM
  • Thanks, this worked great.  I've been out of pocket for a couple days.  I really appreciate the snippet of code, I've been working on a solution for a couple days.

    Cheers,

     
    • Marked as answer by GregS25 Saturday, April 6, 2019 10:13 PM
    Saturday, April 6, 2019 10:13 PM