none
Lookup with Multiple if Condition RRS feed

  • Question

  • Hello,

    I am trying to do lookup from Main Table to Lookup Table,I am not sure How can I use lookup with multiple condition. Can any one have any suggestion?

    My Data Set below,

    Main Table

    Lookup Table

    World

    Details

    Region

    collaboration

    support

    Asia

    ABC

    Asia

    DEF

    PQR

    Europe

    DEF

    South Americas

    PQR

    South Americas

    XYA

     

    MNP

    Final Result

    World

    Details

    Comments

    Asia

    ABC

    Dealer

    Europe

    DEF

    collaboration

    South Americas

    PQR

    support

    South Americas

    XYA

    Deal

    China

    MNP

    Deal

    Tuesday, January 22, 2019 1:37 PM

Answers

  • Hi ABC1919,

    As far as I understand, there is no relation between Region and collaboration/support columns in Lookup table. So on my opinion it make sense to divide Lookup table into separate tables. But if for some reason you need this data structure, you may use this code (please, check, that logic fits yours):

    let
        Source = Excel.CurrentWorkbook(){[Name="Main"]}[Content],
        lookup = Excel.CurrentWorkbook(){[Name="Lookup"]}[Content],
        regions = lookup[Region],
        collaboration = lookup[collaboration],
        support = lookup[support],
        comments = Table.AddColumn(Source, "Comments", each if List.Contains(regions,[World]) then "Deal" else if List.Contains(collaboration,[Details]) then "Collaboration" else if List.Contains(support,[Details]) then "Support" else "Deal")
    in
        comments

    • Edited by Aleksei Zhigulin Wednesday, January 23, 2019 8:44 AM
    • Marked as answer by ABC1919 Wednesday, January 23, 2019 12:10 PM
    Wednesday, January 23, 2019 8:43 AM

All replies

  • Hi ABC1919,

    Please, specify logic, which you want to implement. It's not clear, what should do with Region = Asia in Lookup table and where Comments = Deal, Dealer in Final Result come from.

    Tuesday, January 22, 2019 3:15 PM
  • Hi Aleksei,

    Please see logic below,

    In a Main Table,World column should look in to lookup table “Region” column and if World =Region then put “Deal” and if world does not match with region then it needs to check next logic which is details column should check collaboration and if colloboration=details then pur colloboration other move to next details column should match with support column and details=support then support as a comments other wise “deal”

    let me know if you need more information 

    Tuesday, January 22, 2019 3:28 PM
  • Hi ABC1919,

    As far as I understand, there is no relation between Region and collaboration/support columns in Lookup table. So on my opinion it make sense to divide Lookup table into separate tables. But if for some reason you need this data structure, you may use this code (please, check, that logic fits yours):

    let
        Source = Excel.CurrentWorkbook(){[Name="Main"]}[Content],
        lookup = Excel.CurrentWorkbook(){[Name="Lookup"]}[Content],
        regions = lookup[Region],
        collaboration = lookup[collaboration],
        support = lookup[support],
        comments = Table.AddColumn(Source, "Comments", each if List.Contains(regions,[World]) then "Deal" else if List.Contains(collaboration,[Details]) then "Collaboration" else if List.Contains(support,[Details]) then "Support" else "Deal")
    in
        comments

    • Edited by Aleksei Zhigulin Wednesday, January 23, 2019 8:44 AM
    • Marked as answer by ABC1919 Wednesday, January 23, 2019 12:10 PM
    Wednesday, January 23, 2019 8:43 AM
  • Hi Aleksei,

    There has no relationship between tables.I would like to go with your suggestion to divide lookup table into separate tables and it is working perfectly fine.

    Thank you so much.

    Wednesday, January 23, 2019 12:13 PM