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]

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

Thanks pjmorris - I'm currently finding difficulty in linking as excel is telling there's too many duplicates when linking Sales and ProductDefine tables together with ProductItem as the 'primary key'

 

[alansidman]

I am sorry, I am unable to arrive at your prescribed solution. I am sure it is because I am unsure how and what you mean by multiplication of Table 1 to Table 2. Perhaps you could explain this logically as I am clearly not understanding it using the information provided.

 

[haddy]

I am sorry, I am unable to arrive at your prescribed solution. I am sure it is because I am unsure how and what you mean by multiplication of Table 1 to Table 2. Perhaps you could explain this logically as I am clearly not understanding it using the information provided.

Thanks Alan,
The foodbox explanation didn't help?

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).

I'm trying to get an understanding of how many Products (X1, X2...) I need, if quantity entries are adjusted at the Foodbox level (ProductItem - eg. ProductA, ProductB..).

 

[alansidman]

Your Box explanation made it clear. Thank you for your patience.

Step 1. Bring Sales table into PQ and close and load to a connection
Step 2. Bring Product Define table into PQ and close and load to a connection.
Step 3. Take the Sales table and do a Group By on the Product Item.
Step 4. Join the two tables with a Table Merge, Joining the two on the Product Item Field
Step 5. Expand the table to show Product and Product Quantity
Step 6. Multiply Product Sum by Product Quantity into a new column
Step 7. Using Group by, Sum the Multiplication Column for your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale", Int64.Type}, {"ProductItem", type text}, {"Quantity", Int64.Type}, {"Cost", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProductItem"}, {{"ProductSum", each List.Sum([Quantity]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ProductItem"}, Table2, {"ProductItem"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Product", "ProductQuantity"}, {"Table2.Product", "Table2.ProductQuantity"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Table2", "Multiplication", each [ProductSum] * [Table2.ProductQuantity], type number),
    #"Grouped Rows1" = Table.Group(#"Inserted Multiplication", {"Table2.Product"}, {{"X Sum", each List.Sum([Multiplication]), type number}})
in
    #"Grouped Rows1"

Book1
	A	B
16	Table2.Product	X Sum
17	X1	7
18	X2	6
19	X3	6
20	X4	2
21	X5	1
Sheet1

 

[haddy]

Your Box explanation made it clear. Thank you for your patience.

Step 1. Bring Sales table into PQ and close and load to a connection
Step 2. Bring Product Define table into PQ and close and load to a connection.
Step 3. Take the Sales table and do a Group By on the Product Item.
Step 4. Join the two tables with a Table Merge, Joining the two on the Product Item Field
Step 5. Expand the table to show Product and Product Quantity
Step 6. Multiply Product Sum by Product Quantity into a new column
Step 7. Using Group by, Sum the Multiplication Column for your expected results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale", Int64.Type}, {"ProductItem", type text}, {"Quantity", Int64.Type}, {"Cost", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProductItem"}, {{"ProductSum", each List.Sum([Quantity]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ProductItem"}, Table2, {"ProductItem"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Product", "ProductQuantity"}, {"Table2.Product", "Table2.ProductQuantity"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Table2", "Multiplication", each [ProductSum] * [Table2.ProductQuantity], type number),
    #"Grouped Rows1" = Table.Group(#"Inserted Multiplication", {"Table2.Product"}, {{"X Sum", each List.Sum([Multiplication]), type number}})
in
    #"Grouped Rows1"

Book1
	A	B
16	Table2.Product	X Sum
17	X1	7
18	X2	6
19	X3	6
20	X4	2
21	X5	1
Sheet1

Thanks Alan - took me a bit of time to understand how to apply this but it seems to work as intended. Just confirming - the workaround is not to use relationships between the two tables (ProductItem[Sales] and ProductItem[ProductDefine])?

 

[pjmorris]

haddy, my apologies - I simply read your first post and it sounded like a relationship would solve the problem. I've now tried that and came up with the same many-to-many issue. While that is fairly easy to solve I still didn't get the result I expected so I'm delighted that Alan was able to solve the problem. I never cease to learn - and there's much more to learn than I know I think.

 

[haddy]

haddy, my apologies - I simply read your first post and it sounded like a relationship would solve the problem. I've now tried that and came up with the same many-to-many issue. While that is fairly easy to solve I still didn't get the result I expected so I'm delighted that Alan was able to solve the problem. I never cease to learn - and there's much more to learn than I know I think.

Now add the basics/fundamentals and that's where I stand. You gotta start from somewhere I guess..
Appreciate your assistance though

 

[pjmorris]

too true