Forum Discussion
Connecting Tables with Multiple Columns
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.
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)
1 Reply
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)