Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pjpreddy2
Frequent Visitor

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 IDID NumbBuy Date ID NumbID Numb2ID Numb3ID Numb4Sell Date
A123480001134861/1/2018 8000345618000113486231/29/2018
A1235862475962812/15/2016 7800862475962805667782/19/2018
A22344830575503797/8/2010 13483057550379762/20/2018
A4567401726122975201/5/2018 12784401726122975202/21/2018
B123410100267612/1/2018 8910100267611456782/22/2018
C12341148351283/1/2017 114835128890877325587111124/1/2018
D98767344441028/28/2017 300123498127344441023/15/2018
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @pjpreddy2,

 

You can first unpivot Table 2 in Query Editor mode.

1.PNG2.PNG

 

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])

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @pjpreddy2,

 

You can first unpivot Table 2 in Query Editor mode.

1.PNG2.PNG

 

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])

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.