Relate tables based on more than one column

[Hircon]

Hi,

I have two tables I'd like to relate to each other based on product number. The difficulty is that many products have two different product numbers, divided into two seperate columns in the second table. The relevant columns look something like this:

Table1: includes ticket information

Ticket_ID	Product
1	5001
2	8463
3	484436
4	800006
5	9401

Table2: includes product information, including current product number and old product number where it exists

Product	Old Product	Description
5001	800006	Apple
8463	486624	Banana
5648	484436	Orange
9401	null	Mango
4810	110011	Lemon

I'd like to either combine these tables in power query or relate them in the model so I can display the product properties along with the Ticket data regardless of whether the ticket comes in with the new product number or the old one. How would I best do this?

Desired result:

Ticket_ID	Product	Description
1	5001	Apple
2	8463	Banana
3	484436	Orange
4	800006	Apple
5	9401	Mango

[FreemanZ]

hi @Hircon 

In Power Query, on table2, 

1. select Description column and
2. unpivot other columns,
3. add an index column, 

you shall get the desired result table

[Shravan133 · Accepted solution]

1. Load Tables into Power Query:

   • Load both Table1 and Table2 into Power Query.

2. Duplicate and Unpivot Table2:

   • In Table2, duplicate it and then unpivot it to have all product numbers (new and old) in a single column:
   • Go to the Transform tab, select the Product and Old Product columns, then choose Unpivot Other Columns.
   • This will create a new column, called Attribute, with values "Product" and "Old Product," and another column called Value with the product numbers.
   • Rename the Value column to Product Number.

3. Remove Nulls in Product Number:

   • Filter out any rows in the new Product Number column where the value is null.

4. Merge Tables:

   • Now, go to Table1 and select Merge Queries.
   • Choose Table1 as the primary table and the transformed Table2 as the table to merge with.
   • In the merge dialog, select Product from Table1 and Product Number from the transformed Table2.
   • Use a Left Outer Join to ensure all ticket information is retained, and click OK.

5. Expand Merged Table:

   • After merging, expand the columns from Table2 in Table1 to bring in the Description.
   • You’ll see columns for the product Description for both new and old product numbers.

6. Finalize:

   • Remove any unnecessary columns, rename as needed, and load the table back to the model.

Result

The resulting table should look like this:

Ticket_ID Product Description

1	5001	Apple
2	8463	Banana
3	484436	Orange
4	800006	Apple
5	9401	Mango

[Hircon]

The unpivot was exactly the step I was missing. Thank you!