Excel Power query - is it possible at all to resolve such Pivot/Merge/Union

[mirusev]

It is two files based query - I do not know how to call it - Pivot/Union or other...
The first source is at rows 1 to 6, the second one is from 8 to 16 and the wanted result is at rows 18 to 30. Could it be done with Power query? Any hint of the relations/methods needed will be of great help - I did it partially but not as a whole solution to get that result.



Here is the table:

sample-sales-nal.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Art ID	Name	Qty	Sale price	Delivery price	Supplier	Category					Sales data
2	5849	Product 01	1	0.89	0.60	Suppl 001	Cat01
3	043281	Product 02	1	28.99	22.04	Suppl 002	Cat01
4	023689	Product 03	1	1.69	1.14	Suppl 003	Cat02
5	023691	Product 04	2	3.38	2.29	Suppl 004	Cat03
6	023690	Product 05	1	1.49	1.14	Suppl 005	Cat03
7
8	Art ID	Name	Unit	Stock	Delivery price	Sale price	Markup	Subgroup	Group	Category		Stock data
9	5849	Product 01	pcs.	1	0.601111	0.89	48.05918	Subgr01	Group01	Cat01
10	5850	Product 101	pcs.	0	0.601111	0.95	58.0407	Subgr01	Group01	Cat101
11	5851	Product 102	pcs.	1	0.601111	0.89	48.05918	Subgr01	Group01	Cat101
12	025575	Product 103	pcs.	0	1.239857	1.2	-3.21465	Subgr01	Group01	Cat101
13	025576	Product 104	pcs.	0	1.240072	2.49	100.7948	Subgr01	Group01	Cat101
14	025577	Product 105	pcs.	0	1.28	2.49	94.53125	Subgr01	Group01	Cat101
15	025578	Product 106	pcs.	-1	1.28	2.49	94.53125	Subgr01	Group01	Cat101
16	043275	Product 107	pcs.	3	55.8	72.99	30.80645	Subgr01	Group01	Cat103
17
18	Art ID	Name	Unit	Stock	Delivery price	Sale price	Markup	Subgroup	Group	Category	Qty	Sale price	Delivery price	Supplier	Merge/Unite/Pivot ?????
19	5849	Product 01	pcs.	1	0.601111	0.89	48.05918	Subgr01	Group01	Cat01	1	0.89	0.60	Suppl 001
20	5850	Product 101	pcs.	0	0.601111	0.95	58.0407	Subgr01	Group01	Cat101
21	5851	Product 102	pcs.	1	0.601111	0.89	48.05918	Subgr01	Group01	Cat101
22	025575	Product 103	pcs.	0	1.239857	1.2	-3.21465	Subgr01	Group01	Cat101
23	025576	Product 104	pcs.	0	1.240072	2.49	100.7948	Subgr01	Group01	Cat101
24	025577	Product 105	pcs.	0	1.28	2.49	94.53125	Subgr01	Group01	Cat101
25	025578	Product 106	pcs.	-1	1.28	2.49	94.53125	Subgr01	Group01	Cat101
26	043275	Product 107	pcs.	3	55.8	72.99	30.80645	Subgr01	Group01	Cat103
27	043281	Product 02								Cat01	1	28.99	22.04	Suppl 002
28	023689	Product 03								Cat02	1	1.69	1.14	Suppl 003
29	023691	Product 04								Cat03	2	3.38	2.29	Suppl 004
30	023690	Product 05								Cat03	1	1.49	1.14	Suppl 005
sales

 

[mirusev]

To become better at PQ, you may wish to get the book, "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available on Amazon.

Thank you, Alan! I am far away from Amazon, but will try my best to get it

 

[sandy666]

Why Amazon?

M is for (DATA) MONKEY

www.mrexcel.com

 

[mirusev]

I've managed to find it, but you're right, it's easier here for sure

 

[Bo_Ry]

Create Table Sales and Stock

Ribbon Data > Get data > From Table
Power Query Home > Advanced editor

Paste below code

let
    Stock = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    Sales = Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Sales"]}[Content],{{"Delivery price", "Delivery price1"}, {"Sale price", "Sale price1"}}),
    #"Appended Query" = Table.Combine({Stock, Sales}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Appended Query", {"Art ID", "Name", "Category"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Unit", Order.Descending}, {"Category", Order.Ascending}})
in
    #"Sorted Rows"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
34	Art ID	Name	Category	Unit	Stock	Delivery price	Sale price	Markup	Subgroup	Group	Qty	Sale price1	Delivery price1	Supplier
35	5849	Product 01	Cat01	pcs.	1	0.601111	0.89	48.059177	Subgr01	Group01	1	0.89	0.601111	Suppl 001
36	25578	Product 106	Cat101	pcs.	-1	1.28	2.49	94.53125	Subgr01	Group01
37	25577	Product 105	Cat101	pcs.	0	1.28	2.49	94.53125	Subgr01	Group01
38	25575	Product 103	Cat101	pcs.	0	1.239857	1.2	-3.214645	Subgr01	Group01
39	5850	Product 101	Cat101	pcs.	0	0.601111	0.95	58.040695	Subgr01	Group01
40	5851	Product 102	Cat101	pcs.	1	0.601111	0.89	48.059177	Subgr01	Group01
41	25576	Product 104	Cat101	pcs.	0	1.240072	2.49	100.794793	Subgr01	Group01
42	43275	Product 107	Cat103	pcs.	3	55.8	72.99	30.806452	Subgr01	Group01
43	43281	Product 02	Cat01								1	28.99	22.044	Suppl 002
44	23689	Product 03	Cat02								1	1.69	1.1425	Suppl 003
45	23691	Product 04	Cat03								2	3.38	2.286	Suppl 004
46	23690	Product 05	Cat03								1	1.49	1.143	Suppl 005
Sheet1

 

[mirusev]

Create Table Sales and Stock

Ribbon Data > Get data > From Table
Power Query Home > Advanced editor

Thanks! Appreciated! MrExcel is a great forum with such members