Forum Discussion

MichaelF1's avatar
MichaelF1
Helper III
4 years ago
Solved

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

Need Help
  • luohen's avatar
    luohen
    4 years ago

    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

    Merge method

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

     

    Best Regards

7 Replies

  • luohen's avatar
    luohen
    Resolver II
    4 years ago

    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

    Merge method

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

     

    Best Regards

    • MichaelF1's avatar
      MichaelF1
      Helper III
      4 years ago

      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

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        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