Forum Discussion
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.ID_Numb was stored as 12345,6789,12344,887766 and so on using text to colunns, I blew out the data, so that each ID_Numb would have its own column. The ID_Numbers are in no particular order and have never really been used (or needed to used) until now. SSSSOOOOO what I want to do is find a way to tie together table1 and table2 using the ID_Numb. See attached spreadsheet of what it actually looks like. The ulitmate goal is to tie the dates together so in one table, I can say ID_Numb (blank) was bought and sold on these dates.
| Table 1 | Table 2 | |||||||
| Product ID | ID Numb | Buy Date | ID Numb | ID Numb2 | ID Numb3 | ID Numb4 | Sell Date | |
| A1234 | 8000113486 | 1/1/2018 | 8000 | 34561 | 8000113486 | 23 | 1/29/2018 | |
| A1235 | 8624759628 | 12/15/2016 | 7800 | 8624759628 | 0 | 566778 | 2/19/2018 | |
| A2234 | 483057550379 | 7/8/2010 | 1 | 3 | 483057550379 | 76 | 2/20/2018 | |
| A4567 | 40172612297520 | 1/5/2018 | 12 | 7 | 84 | 40172612297520 | 2/21/2018 | |
| B1234 | 1010026761 | 2/1/2018 | 89 | 1010026761 | 1 | 45678 | 2/22/2018 | |
| C1234 | 114835128 | 3/1/2017 | 114835128 | 890 | 877325587 | 11112 | 4/1/2018 | |
| D9876 | 734444102 | 8/28/2017 | 300 | 1234 | 9812 | 734444102 | 3/15/2018 |
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
3 Replies
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.
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!
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
