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 i...
Need Help
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

    • vic24he's avatar
      vic24he
      Frequent Visitor
      3 years ago

      Conceptually I understand what's going on. Few questions: what does List.Buffer do here? Is it to help with performance or makes it not refresh? And would this method refresh faster than the 2 merge? 

       

      I'm afraid this method might be slower than the double merge. Since each Phone number is trying every combination in the list and then another list, doesnt this take longer?