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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sunb1
Frequent Visitor

getting value from one table to another by matching two columns (data tables not directly related)

Hello -- I'm new to Power BI and i've been working at this forever now and can't seem to get it. I have to data tables, one is a list of M&A deals and one is a list of fees charged. These two tables do not have a relationship with eachother in my query veiw, but they are both connected to teh same lookup tables. I need to get the fee data from Data table 2 by matching it two columns of Data table 1. How do i do that? Thanks for your help!!!!

1 ACCEPTED SOLUTION

To share upload pix or other file to file share site like drop box, one drive, etc. then share from that service and paste link here.

The file I listed connects data from two tables. You can have more than one bridge table. The measures don’t have to be numeric as in my examples. And if you need to lookup one value in the other table in prefer to perform a column calculation you can use RELATED or LOOKUPVALUE to get it

View solution in original post

6 REPLIES 6
sunb1
Frequent Visitor

Hi again - I"m not sure I'm understanding the solution here. Here are some sample data:

 

Table 1:

ACQUIRER        ACQUIRERcik        TARGET      TARGETcik          TransactionYear

ABC                         1                      FiveStar           6                            2018

FDD                         3                         OneStar         7                             2019

CCC                         5                         TwoStar          8                           2017

DDD                          2                      ThreeStar         9                              2016

ABC                         1                        FourStar            11                         2015

FDD                        3                            SixStar           12                         2014

DDD                       2                          SevenStar         13                        2013

 

 

Table 2: 

CompanyCIK                               Fees                      FeeYear

1                                                  25                          2018

12                                                30                              2017

12                                               40                               2014

1                                                   45                           2015

9                                                   15                          2015

 

 

You see, i want two separate columns for Fees from Table 2 added on as a columns in Table 1--one for fees of acquirer and one for fees of Target. In order to get that number, i need to match the "CIK" and Year columns.

Seward12533
Solution Sage
Solution Sage

While Generate has its uses, I see many people trying to force a EXCEL paradigm into Power BI and create actual tables to represent the data.  The power of PowerBI is that it can do most of this work for you when building visuals, Its all about Filter Context - see this tutorial I put together and see if it helps. 

 

https://1drv.ms/u/s!AuCIkLeqFmlhhJgzLZ6jPcWgLGu3Yw

 

 

Hello, I'm not sure I'm understanding the proposed approaches, and all my previous replies weren't posting properly. I'm trying to attach sample data, but it's not working. How do I do that?  Essentially, i want to add columns to table one that would give the fees of the acquirer and target in year of transaction (column 1) and fees "year of transaction +1" (column 2). The only way to do this is to match the KEY and the YEAR between tables 1 and 2. But I don't know how to do this in Power BI. If it helps, i have lookup tables that each of the tables are connected to but the data tables are not connected directly.

To share upload pix or other file to file share site like drop box, one drive, etc. then share from that service and paste link here.

The file I listed connects data from two tables. You can have more than one bridge table. The measures don’t have to be numeric as in my examples. And if you need to lookup one value in the other table in prefer to perform a column calculation you can use RELATED or LOOKUPVALUE to get it

Thanks Seward12533. My issue is that i need to calculate by matching TWO columns. Here's the sample file. As noted above, starting with Table 1, i need to match the CIK and the Year to teh CIK and Year in Table 2, and retrieve the fees. The two tables are connected via a bridge  lookup table.

Thanks in advance!!!

 

 https://www.dropbox.com/s/6cumx569vfh9dah/SAMPLE.xlsx?dl=0

Anonymous
Not applicable

Hi

 

You can use the GENERATE function to join these two tables.

 

Please share some sample data if you need more help.

 

Thanks
Raj

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.