relationships across multiple columns

[pjpreddy2]

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

[v-yulgu-msft · Accepted solution]

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]

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]

@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!