Forum Discussion

pjpreddy2's avatar
pjpreddy2
Frequent Visitor
8 years ago
Solved

relationships across multiple columns

Gang, I've got two tables in Power BI that I need to tie together, the first table has an ID Number (Table 1) that matches an ID Number in another table (Table 2), the issue is the originally Table2...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi pjpreddy2,

     

    You can first unpivot Table 2 in Query Editor mode.

     

    Then, you can refer to the Sell Date in Table 1 via LOOKUPVALUE function.

    Sell Date = LOOKUPVALUE('Table 2'[Sell Date],'Table 2'[ID Numb],'Table 1'[ID Numb])

     

    Best regards,

    Yuliana Gu

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

So, what are those other ID numbers? Are they also in Table1? If not, you could potentially use LOOKUPVALUE to essentially flag which one actually exists in Table1 and return that value. Then you could potentially create a relationship between them provided that you don't run into a circular dependency.

pjpreddy2's avatar
pjpreddy2
Frequent Visitor
8 years ago

Greg_Deckler, that's good question about the product ID's, you witnessing the marriage of two old systems where there were muliple product ID, en lieu of keeping 1 standard product ID instead of changing constantly. 

 

v-yulgu-msft, thank you for the solution, I forgot about unpivoting the data, this will be a big help and get me where I need to go.

 

Thanks guys!