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
MichaelF1
Helper II
Helper II

Power Query - Add column from related table

Hi everyone, 

I'm trying to add a column from one related table to another in Power Query.

So in table 1 I have:

IDCust_RefCust_PostCode
1Customer001AB21 4EF
2Customer002BC65 R56
3Customer003

CD32 Y76

 

And in table 2 I have

IDCust_RefContact_Name
1Customer001Dave
2Customer002Dee
3Customer003Dozy

 

I want to add the Cust_PostCode column from table 1 to table 2, matching Cust_Ref from both tables, so table 2 has 4 columns.

 

I know how to do this using Merge Queries but this seems to take a very large amount of processing power, so I need to find another way if possible.

 

Thanks in advance 🙂

 

Michael

2 ACCEPTED SOLUTIONS
luohen
Resolver II
Resolver II

Hi @MichaelF1 ,

You can achieve it by the following methods:
1. Power Query: Add a custom column as below in Table2

 

= Table.AddColumn(#"Changed Type", "Cust_PostCode", each Table1[Cust_PostCode]{List.PositionOf(Table1[Cust_Ref],[Cust_Ref])})

 

yingyinr_0-1660197404798.png

In addition, you can refer the following blog to achieve it, there are two methods(merge method and add a custom column method) include in this blog.

VLOOKUP in Power Query Using List Functions

Merge methodMerge method

Add a custom column methodAdd a custom column method

2. DAX: Create a calculated column as below to get it

 

Column = 
CALCULATE (
    MAX ( 'Table1'[Cust_PostCode] ),
    FILTER ( 'Table1', 'Table1'[Cust_Ref] = 'Table2'[Cust_Ref] )
)

 

yingyinr_1-1660197628665.png

Best Regards

View solution in original post

Hi @MichaelF1 ,

You can download my sample pbix file from this link to get more details about creating custom column in Power Query Editor...

Add a custom column in Power BI Desktop

Best Regards

View solution in original post

7 REPLIES 7
luohen
Resolver II
Resolver II

Hi @MichaelF1 ,

You can achieve it by the following methods:
1. Power Query: Add a custom column as below in Table2

 

= Table.AddColumn(#"Changed Type", "Cust_PostCode", each Table1[Cust_PostCode]{List.PositionOf(Table1[Cust_Ref],[Cust_Ref])})

 

yingyinr_0-1660197404798.png

In addition, you can refer the following blog to achieve it, there are two methods(merge method and add a custom column method) include in this blog.

VLOOKUP in Power Query Using List Functions

Merge methodMerge method

Add a custom column methodAdd a custom column method

2. DAX: Create a calculated column as below to get it

 

Column = 
CALCULATE (
    MAX ( 'Table1'[Cust_PostCode] ),
    FILTER ( 'Table1', 'Table1'[Cust_Ref] = 'Table2'[Cust_Ref] )
)

 

yingyinr_1-1660197628665.png

Best Regards

Thank you very much, I didn't know about the add column method, I'll check it out 🙂

Hi @MichaelF1 ,

You can download my sample pbix file from this link to get more details about creating custom column in Power Query Editor...

Add a custom column in Power BI Desktop

Best Regards

Thanks @luohen , that's very helpful 🙂

amitchandak
Super User
Super User

@MichaelF1 , In power query you have to use merge .

 

In DAX you can use related or relatedtable with maxx or sumx etc

 


refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Hi @amitchandak ,

Thank you for the reply. I was hoping to do it in PQ rather than DAX. My problem is that I keep getting the 'resources exceed' error in Power BI so I was hoping to shift some of the heavy lifting to PQ, but Merge seems unworkable due to the length of tiome it takes.

Do you by any chance have any tips or links to improving the performance of DAX measures?

Thanks again,

Michael

@MichaelF1 , My viewpoint is, that if data is large do not do it in power bi. Bring it from the source.

 

But try the same in dataflow on service.  Try merge there

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.

Top Solution Authors