Two values from table lookup merging into primary table

[gemcityzach]

Hey folks, I have three (3) tables in total but each has unique values I need to bring back to table 1. I'd like to bring results from Tables 2 and 3 back into table 1 by this common key. Merge isn't working because it only allows me to handle one at a table, so in table 1 I end up with null values where table 2 or table 3 didn't match. Table 1 has a keys of the form 'Ennn' or 'Rnnn' where 'n' is a number.

Right now this operation is done in Excel using an XLOOKUP for the 'E id' in the 'E table' and if that isn't available, then it looks up the same value but in the 'R table'. It works but it's all manual and we're bringing all of our reporting togetgher into PowerBI so I'd like to solve this last little puzzle.

tbl1_ID	tbl1_field1	tbl1_field2
E123	blah	blah2
R313	blah again	double the blah

I need to enrich table one for each E or R value from the corresponding 'E' or 'R' table. They share a similar form as above but in the 'E' table the IDs would only be 'E123' or 'E534', etc and in 'R table' the ID values would be 'R343' or 'R343'. Both of those types show up in Table 1 ID.

Therefore, I need to bring back field1 and field2 from Table 2 and Table 3 where tbl1_ID is present in Table 2 or Table 3. But I want to avoid the null values if possible. Table2 or Table 3 values will always contain the same strings in either table.

tbl1_ID	tbl1_field1	tbl2_ID	tbl2_rating	tbl3_ID	tbl3_rating
E123	string	E123	High	null	null
R345	string	null	null	R345	Very Low

So it would handle nulls by just using either the outputs by Table 2 or Table 3 when there is a value 😉

tbl1_ID	tbl1_field1	rating	rating
E123	string	High	Low
R345	string	Mid	Very Low

[v-kongfanf-msft]

Hi @gemcityzach ,

What does your data model look like and can you provide relevant screenshot information and describe it?

Best Regards,
Adamk Kong

[gemcityzach]

The data is basically OR conditioned. There will *only* be a value1 and value2 in either Table 2 or Table 3 for any specific row in Table 1. So if there is a E123 row in Table1 then populate with value1/value2 with the corresponding value1/value2 from the 'E table'. And if the Table 1 row is an R123 row, then populate the Table1 value1/value2 with the value1/value2 from the 'R table'.

Right now what I'm doing is merging both Table 2 and Table 3 onto Table 1 so I have null values where there isn't an R record or E record for each row. And then two calculated columns that look for a missing value in E-value1/E-value2 and then return R-value1/R-value2 and vice verse. So then any table 1 row will also have a value1/value present. But that feels clunky.

E.g., = Table.AddColumn(#"Expanded table1", "value1", each if [#"table1_value1"] is null then [#"table2_value1"] else if [#"table2_value1"] is null then [#"table1_value1"] else "Not Assigned")