Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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?
User | Count |
---|---|
8 | |
7 | |
5 | |
5 | |
4 |
User | Count |
---|---|
14 | |
13 | |
8 | |
6 | |
6 |