Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vic24he
Frequent Visitor

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. 

 

 

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

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.

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

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

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? 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors