Match one column OR another to a different table

[RandomKitten]

Basically, I have a table with data in two columns that I need to match to another table. The problem is, I need to conditionally match this table to another based on whether a column has data.

Table 1:

Device Name	Device Code
Product 1	ABC123
	XYZ123
Product 2	XYZ123
Product 1
Product 2	QRS123

Table 2:

Product	Code
Product 1	ABC123
Product 2	XYZ123

I need to check if there is a Device Code, and if so, match it to the Code in Table 2. If there is not, then match the Device Name to the Product in Table 2.  (Obviously there are other columns at play in both tables, but these are the two I need to line up.)

To complicate things further, it would be best if I could check if there is a match to the Device Code, and if there is no match (even if there is a Device Code in Table 1) then compare the Device Name. 

Is it possible to do this?

[v-cherch-msft]

Hi @RandomKitten 

You may try to add a column in table1 to check if it match or not.

Column =
IF (
    Table1[Device Code] <> BLANK (),
    IF (
        Table1[Device Code] IN VALUES ( Table2[Code] )
            || Table1[Device Name] IN VALUES ( Table2[Product] ),
        "Match",
        "Not match"
    )
)

Regards,

[RandomKitten]

Thank you, @v-cherch-msft.  However, the point of this is not to confirm whether a match exists, but to build a table relationship based on the match.  I apologize if I was unclear on this. 

Creating a "Match/No Match" column will not give me the link I need to establish a relationship to the other table so I can pull device information based on the product.

[v-cherch-msft]

Hi @RandomKitten 

You may link the two columns and try to use USERELATIONSHIP Function.Please have a look at below article.

Reference:https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Regards,