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

 

[sandy666]

is this acceptable?

Art ID	Name	Unit	Stock	Delivery price	Sale price	Markup	Subgroup	Group	Category	Qty	Supplier
5849	Product 01	pcs.	1	0.601111	0.89	48.059177	Subgr01	Group01	Cat01	1	Suppl 001
5850	Product 101	pcs.	0	0.601111	0.95	58.040695	Subgr01	Group01	Cat101
5851	Product 102	pcs.	1	0.601111	0.89	48.059177	Subgr01	Group01	Cat101
25575	Product 103	pcs.	0	1.239857	1.2	-3.214645	Subgr01	Group01	Cat101
25576	Product 104	pcs.	0	1.240072	2.49	100.794793	Subgr01	Group01	Cat101
25577	Product 105	pcs.	0	1.28	2.49	94.53125	Subgr01	Group01	Cat101
25578	Product 106	pcs.	-1	1.28	2.49	94.53125	Subgr01	Group01	Cat101
43275	Product 107	pcs.	3	55.8	72.99	30.806452	Subgr01	Group01	Cat103
23689	Product 03			1.1425	1.69				Cat02	1	Suppl 003
23690	Product 05			1.143	1.49				Cat03	1	Suppl 005
23691	Product 04			2.286	3.38				Cat03	2	Suppl 004
43281	Product 02			22.044	28.99				Cat01	1	Suppl 002

 

[mirusev]

is this acceptable?

seems perfect to me how is the magic done? Is it easy to explain?

 

[sandy666]

here:

let
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Stock = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    Source = Table.Combine({Sales, Stock}),
    Grp = Table.Group(Source, {"Art ID", "Sale price", "Delivery price", "Category"}, {{"Count", each _, type table}}),
    LQty = Table.AddColumn(Grp, "Qty", each List.Distinct([Count][Qty])),
    ExQty = Table.TransformColumns(LQty, {"Qty", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LSupplier = Table.AddColumn(ExQty, "Supplier", each List.Distinct([Count][Supplier])),
    ExSupplier = Table.TransformColumns(LSupplier, {"Supplier", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LUnit = Table.AddColumn(ExSupplier, "Unit", each List.Distinct([Count][Unit])),
    ExUnit = Table.TransformColumns(LUnit, {"Unit", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LStock = Table.AddColumn(ExUnit, "Stock", each List.Distinct([Count][Stock])),
    ExStock = Table.TransformColumns(LStock, {"Stock", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LMarkup = Table.AddColumn(ExStock, "Markup", each List.Distinct([Count][Markup])),
    ExMarkup = Table.TransformColumns(LMarkup, {"Markup", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LSubgroup = Table.AddColumn(ExMarkup, "Subgroup", each List.Distinct([Count][Subgroup])),
    ExSubgroup = Table.TransformColumns(LSubgroup, {"Subgroup", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LGroup = Table.AddColumn(ExSubgroup, "Group", each List.Distinct([Count][Group])),
    ExGroup = Table.TransformColumns(LGroup, {"Group", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    LName = Table.AddColumn(ExGroup, "Name", each List.Distinct([Count][Name])),
    ExName = Table.TransformColumns(LName, {"Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Reorder = Table.ReorderColumns(ExName,{"Art ID", "Name", "Unit", "Stock", "Delivery price", "Sale price", "Markup", "Subgroup", "Group", "Category", "Qty", "Supplier", "Count"}),
    Sort = Table.Sort(Reorder,{{"Unit", Order.Descending}, {"Name", Order.Ascending}, {"Art ID", Order.Ascending}})
in
    Sort

 

[mirusev]

here:

Thank you very much!

I know, no shortcut to the knowledge but if you know where I could see it (the important parts) making it with the GUI will be great - some book, site, blog...?

And thanks again!

 

[sandy666]

Google => keywords: power query

 

[sandy666]

or

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

but not everything is with GUI

 

[mirusev]

I see, that I was afraid of... Thanks!

 

[alansidman]

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.

 

[sandy666]

I see, that I was afraid of... Thanks!

You are welcome

btw. about this book from post#9: there is not everything you need to learn but for a start it may be