Forum Discussion

vic24he's avatar
vic24he
Frequent Visitor
3 years ago

Performing two merges and get the result based on conditional?

So I have ABC table that needs to lookup the address from two other tables. I want to bring in the address from table1 if the address was found in table1. I want to bring in the address from table2 if table1 did not find an address. So currently my solution is:

 

1. Merge ABC and table1 to and bring in the address from table1. 

2. Merge ABC and table2 to bring in address from table2. 

3. Add a conditional column to use if addressFromTable1 is null, then use addressFromTable2, else use addressFromTable1. 


My question is, is there an easier solution? I dont want to have to do two merges if I dont need to, there are a lot of steps and things going on. 

 

 

Need Help

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    There can be various alternative approaches. I would like to know the key field on which you are performing merge between ABC and Table 1 / 2 and the result field. Then I can supply the right approach to you.

    • vic24he's avatar
      vic24he
      Frequent Visitor
      3 years ago

      They all share the same unique identifier. Let's say its phone number. 

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        3 years ago

        Use below code

        let
    Source = Excel.CurrentWorkbook(){[Name="ABC"]}[Content],
    T1Address = List.Buffer(Table1[Address]),
    T1Phone = List.Buffer(Table1[Phone]),
    T2Address = List.Buffer(Table2[Address]),
    T2Phone = List.Buffer(Table2[Phone]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try try T1Address{List.PositionOf(T1Phone, [Phone])} otherwise T2Address{List.PositionOf(T2Phone, [Phone])} otherwise null)
in
    #"Added Custom"

        Sample file is attached