Connecting Tables with Multiple Columns

[Vishruti]

I have following two tables.

 

One is a master table which consists of a master list of Site names along with ID and Vendor Type.

The second table consists of product codes and 4 different types of Sites columns. The sites, in all of these 4 columns, comes from the master site table. 

How can I create relationship between these two tables based on Sites?

Eventually, I want plot the second data table in Table visual and use Vendor Type and Site Names from first table as a filters. 

[AmiraBedh · Accepted solution]

I think vefore creating a relationship, you should have a unique list of all site names. In your second table, you have multiple site columns (Site A, Site B, Site C, Site D). You might need to transform it into a format that has a single column for the site names to create a more straightforward relationship. You can achieve this by unpivoting the Site columns in the Query Editor so that you have a single column that lists the site name against each Product Code.

After that, you can create a relationship between the Site Name field from the master table anf the "Site" field in the transformed product table (Many to One)