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

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?