Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |