Forum Discussion

nbrandborg's avatar
nbrandborg
Helper II
5 years ago
Solved

Lookup one value from multiple tables

Hi all,

I'm having a master table containing product numbers in scope from which I want to lookup values in from other sources/tables.
The values to one of the columns in the master table needs to lookup one value from multiple tables per product number. All product numbers are clustered in groups/tables having different columns and number of columns but all share the same column "Availability" which is comparable across. This columns is the one from which I need to lookup in. A product number only exist once across the lookup tables.

The lookup tables looks simplified like this (I'm having 10 tables)

Product numberxyAvailability
123xxxyyy10%
234xxxyyy20%

and

Product numberxyzAvailability
345xxxyyyzzz15%
456xxxyyyzzz22%

 

And the master table are supposed to look like this after the lookup

Product numberAvailability
12310%
23420%
34515%
45622%

 

Any great suggestions to solve this? 🙂

Best regards,
Nichlas

Need Help
  • pradeept's avatar
    pradeept
    5 years ago

    hi nbrandborg  I think it may work. I used the below code snippet.

    1. Create loook up table like this.
    Product_LookupTable =
    Union(
    SELECTCOLUMNS(Table1, "Product number", Table1[Product number],"Availability", Table1[Availability],"New_Column",Table1[x]),
    SELECTCOLUMNS(Table2, "Product number", Table2[Product number],"Availability", Table2[Availability],"New_Column",Table2[y])
    )

    2. Create columns in the Product Master table like this.

    Availability =
    LOOKUPVALUE(Product_LookupTable[Availability],Product_LookupTable[Product number],Product_Master[Product number])

    New_Column_Master =
    LOOKUPVALUE(Product_LookupTable[New_Column],Product_LookupTable[Product number],Product_Master[Product number])

    Did I answer your question? Mark my post as a solution! It helps others.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    nbrandborg , as lookup can work across two tables only .

     

    You might have to try something like this with 10 tables

     

    new column =
    var _1= maxx(filter(Table1, Table1[Product number] = master[Product numbe]),table1[Availability])
    var _2= maxx(filter(Table2, Table1[Product number] = master[Product numbe]),table2[Availability])
    //add other
    Switch(true() ,
    not(isblank(_1)) ,_1 ,
    not(isblank(_2)) ,_2 ,

    //add for other 8
    0)

    • nbrandborg's avatar
      nbrandborg
      Helper II
      5 years ago

      amitchandak 
      Thank you for providing a solution.
      I just tried to set it up partly with some of the tables, but I get an syntax error around the 'switch' expression.

      Availability =
      var _1= maxx(filter('EC010326', 'EC010326'[Material] = Products[Material]),'EC010326'[Availability])
      var _2= maxx(filter('EC010737', 'EC010737'[Material] = Products[Material]),'EC010737'[Availability])
      var _3= maxx(filter('EC011491', 'EC011491'[Material] = Products[Material]),'EC011491'[Availability])
      var _4= maxx(filter('EC010980', 'EC010980'[Material] = Products[Material]),'EC010980'[Availability])
      Switch(true() ,
      not(isblank(_1)) ,_1 ,
      not(isblank(_2)) ,_2 ,
      not(isblank(_3)) ,_3 ,
      not(isblank(_4)) ,_4 ,
      0)

      Do you spot any errors?
  • pradeept's avatar
    pradeept
    Resolver I
    5 years ago

    hi nbrandborg  you can try like this; I am not sure about your data model.

    1. Create a lookup table, by merging all 10 tables, like this.
    Product_LookupTable =
    Union(
    SELECTCOLUMNS(Table1, "Product number", Table1[Product number],"Availability", Table1[Availability]),
    SELECTCOLUMNS(Table2, "Product number", Table2[Product number],"Availability", Table2[Availability])
    )

    2. Create a new column in Product Master table like thhis.
    Availability =
    LOOKUPVALUE(Product_LookupTable[Availability],Product_LookupTable[Product number],Product_Master[Product number])


    Did I answer your question? Mark my post as a solution! It helps others.

    • nbrandborg's avatar
      nbrandborg
      Helper II
      5 years ago

      pradeept it was not a bad idea, and definately not something I had considered. But tried implementing it and it worked fine! 

      I considered expanding it and tried to take in another column to make the table more useful and central for lookups. Unfortunately I did not succeed in importing another column. The columns comes from the same supporting tables and has the same logic as 'availability'.
      Can the function be altered to take more columns in still based on the product numbers.

      • pradeept's avatar
        pradeept
        Resolver I
        5 years ago

        hi nbrandborg  I think it may work. I used the below code snippet.

        1. Create loook up table like this.
        Product_LookupTable =
        Union(
        SELECTCOLUMNS(Table1, "Product number", Table1[Product number],"Availability", Table1[Availability],"New_Column",Table1[x]),
        SELECTCOLUMNS(Table2, "Product number", Table2[Product number],"Availability", Table2[Availability],"New_Column",Table2[y])
        )

        2. Create columns in the Product Master table like this.

        Availability =
        LOOKUPVALUE(Product_LookupTable[Availability],Product_LookupTable[Product number],Product_Master[Product number])

        New_Column_Master =
        LOOKUPVALUE(Product_LookupTable[New_Column],Product_LookupTable[Product number],Product_Master[Product number])

        Did I answer your question? Mark my post as a solution! It helps others.