Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi everyone,
I'm trying to add a column from one related table to another in Power Query.
So in table 1 I have:
ID | Cust_Ref | Cust_PostCode |
1 | Customer001 | AB21 4EF |
2 | Customer002 | BC65 R56 |
3 | Customer003 | CD32 Y76 |
And in table 2 I have
ID | Cust_Ref | Contact_Name |
1 | Customer001 | Dave |
2 | Customer002 | Dee |
3 | Customer003 | Dozy |
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
Solved! Go to Solution.
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])})
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
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] )
)
Best Regards
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
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])})
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
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] )
)
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
@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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
86 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |