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: ID Cust_Ref Cust_PostCode 1 Customer001 AB21 4EF 2 Customer002 BC65 ...
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