locked
Get Matching Item from Unrelated Table - Need Help RRS feed

  • Question

  • I have a two table PowerPivot.

    Table A has 1 column:  "Item Code"

    Table B has 2 columns:  "Item Code" and "Item Name"

    They are in a M2M format.  I am pulling both tables directly into PowerPivot from CSV.  No ETL or ability to use select distinct when I import table B. There are dups in both, but for every "Item Code", there is the same "Item Name" - they always match.

    In Table A, I want to create a calculated column to "FINDS" or "LOOKs UP" the value that matches based on TableA(Item Code) = TableB(Item Code) logic.

    If this was an aggregation, I would use the calculate(sum(xxx),filter(tableA, tableA ItemCode = tableB ItemCode pattern, but I don't want an aggregation, I just want the Item Name that matches to appear.

    I would use VLOOKUP, but I can't seem to make it work in PowerPivot.

    Thanks in advance.

    Tuesday, November 25, 2014 2:44 AM

Answers

  • Ideally, you do want your look up tables to have unique values.  You should consider using Power Query to accomplish this on a CSV:

    http://www.powerpivotpro.com/2014/11/flat-to-star-transformation-using-dax-query/

    However, if all item codes in table B always correspond to the exact same Item Name then you could use something like the following as either a Measure or Calculated Column:

    =LOOKUPVALUE(
         TableB[ItemName], 
         TableB[ItemCode], 
         FIRSTNONBLANK(
               TableA[ItemCode], 
               TableA[ItemCode]
         )
    )

    • Proposed as answer by greggyb Tuesday, November 25, 2014 6:24 PM
    • Marked as answer by Eric Vogelpohl Friday, November 28, 2014 2:43 AM
    Tuesday, November 25, 2014 6:18 PM
    Answerer
  • Mike is absolutely correct that Power Query is the appropriate solution here.

    Failing that, his measure should work without issue for you. An alternative that may work faster but is more fragile:

    =CALCULATE(
        VALUES( TableB[Item Name] )
        , TableB[Item Code] = TableA[Item Code]
    )

    Again, this is more fragile than Mike's LOOKUPVALUE() but should work as a calculated column in TableA.

    • Marked as answer by Eric Vogelpohl Friday, November 28, 2014 2:43 AM
    Tuesday, November 25, 2014 6:28 PM

All replies

  • Ideally, you do want your look up tables to have unique values.  You should consider using Power Query to accomplish this on a CSV:

    http://www.powerpivotpro.com/2014/11/flat-to-star-transformation-using-dax-query/

    However, if all item codes in table B always correspond to the exact same Item Name then you could use something like the following as either a Measure or Calculated Column:

    =LOOKUPVALUE(
         TableB[ItemName], 
         TableB[ItemCode], 
         FIRSTNONBLANK(
               TableA[ItemCode], 
               TableA[ItemCode]
         )
    )

    • Proposed as answer by greggyb Tuesday, November 25, 2014 6:24 PM
    • Marked as answer by Eric Vogelpohl Friday, November 28, 2014 2:43 AM
    Tuesday, November 25, 2014 6:18 PM
    Answerer
  • Mike is absolutely correct that Power Query is the appropriate solution here.

    Failing that, his measure should work without issue for you. An alternative that may work faster but is more fragile:

    =CALCULATE(
        VALUES( TableB[Item Name] )
        , TableB[Item Code] = TableA[Item Code]
    )

    Again, this is more fragile than Mike's LOOKUPVALUE() but should work as a calculated column in TableA.

    • Marked as answer by Eric Vogelpohl Friday, November 28, 2014 2:43 AM
    Tuesday, November 25, 2014 6:28 PM
  • Thanks all.
    Friday, November 28, 2014 2:44 AM