Power Query - Relationship Query

[haddy]

Hi,

Apologies if this has (probably) been raised, but i wasn't sure what you'd call it.

I've got three spreadsheets/tables;
Sales

Sale	ProductItem	Quantity	Cost
1​	ProductA	1​	50​
2​	ProductB	1​	50​
3​	ProductA	2​	100​

ProductDefine

ProductItem	Product	Quantity
ProductA	X1	2​
ProductA	X2	2​
ProductA	X3	2​
ProductB	X1	1​
ProductB	X4	2​
ProductB	X5	1​

Stocking

Product	Quantity	Cost
X1	100​	50​
X2	50​	100​
X3	75​	25​
X4	20​	20​
X5	15​	25​

Barely have foundational understanding of how Power Query can work (other than watching Youtube videos make it look easy) - I'm trying to have the spreadsheet register that there's a link between ProductItem(Sales) and Product(ProductDefine), and once the link has been established I'm guessing I'll be able to use PowerPivot to show me the following (based on the above example);

Product	Quantity
X1	7​
X2	6​
X3	6​
X4	2​
X5	1​

Eventually I'd like the last table (Stocking) to have a link with the other two tables - I was thinking of using the 'Product' column.

So far I've come to dead end with 'duplicate values' between two tables. I'm lead to believe ProductItem is the issue?

Hope I've made sense

Thank you in advance

 

[haddy]

Sorry Admin - I realised I hadn't completed the thread title, and now I can't edit the post

'Power Query - Relationship Query'

 

[alansidman]

In your simplified example, can you explain the logic of how you arrived at the quantities in the solution Table. I cannot see how these quantities were arrived at with the data provided. Please clarify so that we can move onto a working solution.

 

[haddy]

Hi Alan and thank you for the reply.

The last table might be causing some confusion (Stocking) - please disregard for the solution on the bottom. As mentioned, I'd like to have it linked to the other tables, but I'm working with the first issue. (Though I won't say no to an all encompassing solution)

I'm trying to get the model to understand that there're multiple "sub-products" (defined in 'ProductDefine' table) to a ProductItem (in Sales). So in the above example ProductA consist of 3 sub-products with varying quantities - same with ProductB. There are some sub-products which both ProductItems (ProductA & B) use - in this example its X1.

Hope that helps

 

[alansidman]

It would be helpful to understand the relationship between tables if you showed us a mocked up solution. Obviously, the one you have provided bears no relationship to the tables shown. So I ask again, so that I am clear. Show us a mocked up solution using the example tables you have provided.

 

[alansidman]

If this is the solution you are looking for, then advise and I will provide you with mcode. If different, then please show what you want.

Book13
	A	B
1	Product	Sum X
2	X1	3
3	X2	2
4	X3	2
5	X4	2
6	X5	1
Sheet3

Book13
	A	B	C	D
1	Product	Sum X	Table3.Quantity	Table3.Cost
2	X1	3	100	50
3	X2	2	50	100
4	X3	2	75	25
5	X4	2	20	20
6	X5	1	15	25
Sheet6

 

[haddy]

I don't believe so Alan. I think I'm confusing you with the third table (Stocking) - I've attached a mockup which hopefully explains what I'm trying to figure out.

This example may help your understanding as well;
Imagine ProductA is a food box which contains (or defined to include) 2 apples (X1), 2 oranges (X2) and 2 bananas (X3).
ProductB is another food box also with 1 apple (X1), 2 melons (X4) and 1 strawberry (X5).

If I order 3 ProductAs and 1 ProductB - I should end up with 7 apples (X1), 6 oranges (X2), 6 bananas (X3), 2 melons (X4) and 1 strawberry (X5).

Edit: I adjusted the column header in ProductDefine (Quantity to ProductQuantity) assuming they should be different if ProductItem should be the key between the two tables (?)

 

[pjmorris]

If you want a link between two data tables its easiest to do so in PowerPivot where you can define the relationship:

from PowerPivot Ribbon, select Manage then 'Diagram View' drag between the linked fields.
then create a pivot table and put the relevant fields in columns/rows and values sections.

I find this an easy way of joining data sets, but it does depend on the data.

HTH

 

[alansidman]

I really don't want to have to re-type all your data to give you an answer. Please use XL2BB to upload your data so that it can be manipulated without issue. We cannot manipulate data that are in pictures.

 

[haddy]

I really don't want to have to re-type all your data to give you an answer. Please use XL2BB to upload your data so that it can be manipulated without issue. We cannot manipulate data that are in pictures.

Hope this works as you've requested - first time using this.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	Sale	ProductItem	Quantity	Cost		ProductItem	Product	ProductQuantity		ProductItem	Product	ProductQuantity		Product	Quantity
5	1	ProductA	1	50		ProductA	X1	2		ProductA	X1	2		X1	7
6	2	ProductB	1	50		ProductA	X2	2		ProductA	X2	2		X2	6
7	3	ProductA	2	100		ProductA	X3	2		ProductA	X3	2		X3	6
8						ProductB	X1	1		ProductB	X1	1		X4	2
9						ProductB	X4	2		ProductB	X4	2		X5	1
10						ProductB	X5	1		ProductB	X5	1
11										ProductA	X1	2
12										ProductA	X2	2
13										ProductA	X3	2
14										ProductA	X1	2
15										ProductA	X2	2
16										ProductA	X3	2
Sheet1