Excel PowerQuery with subtotal required to replace old VBA code that is too slow

[PeterMac65]

Hi,
In the recent past i was using VBA to step through approx 2000 rows and if the "Total" column was not ZERO i would copy to a new sheet the following columns:
OrderCode (used to perform a future sort)
Product Name
Quantity
Price
Unit of Measure
Total

I then sorted the data by OrderCode

And added a subtotal for each OrderCode.

What used to take < 30 seconds now takes up to 5 minutes.

I think I could achieve this using a Power Query instead. Could someone please point me in the right direction with particular focus in how to include a subtotal

Regards Peter

 

[alansidman]

Load some sample data and a mocked up solution using XL2BB. Do not post a picture as we cannot manipulate data in a picture.

 

[PeterMac65]

Hi Alan, I've loaded some sample data

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Job subcode	Order-Ref	Code	Description 1	Subtotal	Quantity	Suggest	Units	Price	Total	Column1	Exclude	Product	Title
2	3	P&G, on site costs & Insurance			$31,369.08
3	1007			Additional Design Time & QS Time		1		Sum	$680.00	$680.00				Provision - No Order
4										$0.00				Provision - No Order
5														Provision - No Order
6	1006	61		Not included by owner's request		1	1		$0.00	$0.00		Exclude	10 year Master Builders standard guarantee included	Provision - No Order
7														Provision - No Order
8			3.01	Master Builder 10 Year Standard Guarantee									$ 479,165.43	Provision - No Order
9	1006	61		Over $200,000 to $400,000		0	0	each	$1,130.43	$0.00			Masterbuild Over $200,000 to $400,000	Provision - No Order
10	1006	61		Over $400,000 to $600,000		1	1	each	$1,304.35	$1,304.35			Masterbuild Over $400,000 to $600,000	Provision - No Order
11	1006	61		Over $600,000 to $800,000		0	0	each	$1,521.74	$0.00			Masterbuild Over $600,000 to $800,000	Provision - No Order
12	1006	61		Over $800,000 to $1,000,000		0	0	each	$1,695.65	$0.00			Masterbuild Over $800,000 to $1,000,000	Provision - No Order
13	1006	61		Over $1,000,000 to $1,500,000		0	0	each	$1,913.04	$0.00			Masterbuild Over $1,000,000 to $1,500,000	Provision - No Order
14	1006	61		Over $1,500,000 to $2,000,000		0	0	each	$2,391.30	$0.00			Masterbuild Over $1,500,000 to $2,000,000	Provision - No Order
15	1006	61		$2,000,000+ Call us		0	0	each	$2,826.09	$0.00			Masterbuild Over $2,000,000	Provision - No Order
16														Provision - No Order
17	1006	62		Insurance by owner		1	1	note	$0.00	$0.00		Exclude	Builders risk insurance included	Provision - No Order
18	1006	62	3.02	Construction Work Insurance		179	179	m2	$7.50	$1,342.50			Construction Work Insurance	Provision - No Order
19														Provision - No Order
20				Architect										Provision - No Order
21	1007	51		Concept / sales plan		0	1	sum	$750.00	$0.00			Concept / sales plan	Provision - No Order
22	1007	52		Building consent submission		1	1	sum	$750.00	$750.00			Building consent submission	Provision - No Order
23	1007	52		Design (draft) - 1 storey		179	179	m2	$15.00	$2,685.00			Design (draft) - 1 storey	Provision - No Order
24														Provision - No Order
25			3.04	Temporary Services										Provision - No Order
26	1008	170		Temporary Power		1	1	each	$890.00	$890.00			Temporary Power	Temporary Power
27	1008	260		Three Phase Utility mains up to 20 metres		1	1	each	$1,300.00	$1,300.00			Three Phase Utility mains up to 20 metres	Services (Power, Water, Data)
28	1008	260		Power & Water		18	18	week	$40.00	$720.00			Power & Water	Services (Power, Water, Data)
29	1008	999		Temporary erosion & sediment control as an extra if required during construction		1	1	sum	$0.00	$0.00			Temporary erosion & sediment control as an extra if required during construction	Provision - No Order
30														Provision - No Order
31	1008			Plant and small tools		0	0	week	$0.00	$0.00			Plant and small tools	Provision - No Order
32														Provision - No Order
33			3.05	Scaffolding & Nets										Provision - No Order
34	1015	340		Scaffolding supplied and installed by owner as required		1	1	note	$0.00	$0.00		Exclude	As required, supplied and installed by XXX	Scaffolding
35	1015	340		Scaffolding - provisional sum		1	1	pcsum	$0.00	$0.00		Exclude	As required, supplied and installed by XXX	Scaffolding
36	1015	340		Erection/dismatle/cartage $45 x lm		66	66	sum	$45.00	$2,970.00			Scaffold Erection/dismatle/cartage	Scaffolding
37	1015	340		Weekly rental $2.4 x lm		5	5	week	$158.40	$792.00			Scaffold Weekly rental	Scaffolding
38	1015	340		Weekly rental Gable $7.2 x lm		3	3	week	$57.60	$172.80			Scaffold Weekly rental Gable	Scaffolding
39	1015	340		Weekly Inspection (depends on lm_gable)		8	8	week	$65.00	$520.00			Scaffold Weekly Inspection	Scaffolding
40														Provision - No Order
41	1015	350		Safety nets supplied and installed by owner as required		1	1	note	$0.00	$0.00		Exclude	As required, supplied and installed by XXX	Safety Nets
42	1014	350		Supply and installation of nets		179	179	m2	$4.00	$716.00			Scaffold Nets	Safety Nets
43														Provision - No Order
44			3.06	Site Sheds and Setup										Provision - No Order
45	1017			Container		18	18	week	$0.00	$0.00			Container	Provision - No Order
46				Portaloo										Provision - No Order
47	1017	180		Portaloo Delivery & Collection , hire and cleaning service (incl. consumables)		18	18	week	$44.00	$792.00			Portaloo	Site Port a Loo (Temporary Toilet)
48				Portaloo - alternative										Provision - No Order
49	1017	180		Portaloo weekly hire		0	0	week	$12.00	$0.00			Nova Portaloo weekly hire	Site Port a Loo (Temporary Toilet)
50	1017	180		Cleaning service (please provide fortnightly service)		0	0	fortnight	$30.00	$0.00			Nova Portaloo cleaning services	Site Port a Loo (Temporary Toilet)
51	1017	180		Portaloo delivery & collection		0	0	sum	$50.00	$0.00			Nova Portaloo delivery and collection	Site Port a Loo (Temporary Toilet)
52	1016	160		Temporary fences @ $0.56 per week per lm		18	18	weeks	$11.20	$201.60			Temporary Fences	Temporary Fencing
53	1016	160		Temporary Fence delivery		1	1		$50.00	$50.00			Temporary Fence delivery	Temporary Fencing
54														Provision - No Order
55			3.07	Waste Management										Provision - No Order
56	1020	290		9m³ Bin Delivered to site (to be charged by weight) - Initial delivery Location of Skip TBC by builder on site		1	1	bins	$380.00	$380.00			Clean up - Bins	Waste Management (Rubbish Skip)
57	1020	999		9m³ Bin Delivered to site (to be charged by weight) -Additional delivery		2	3	bins	$380.00	$760.00			Clean up - Bins	Provision - No Order
58	1020	999		Clean up - Labour ($2 x Selling area)		179	179	sum	$2.00	$358.00			Clean up - Labour	Provision - No Order
59														Provision - No Order
60	1021	980	3.08	Final Clean		179	179	m2	$3.00	$537.00			Final Clean	Final Clean
61														Provision - No Order
62			3.09	Others: Street Crossing, colour consultancy and XXX Mesh										Provision - No Order
63	1005			Cutdown and base up only		0	0	sum	$800.00	$0.00			Cutdown and base up only	Provision - No Order
64	1033	110		Colour consultancy		1	1	sum	$300.00	$300.00			Colour consultancy	Colour Consultant
65	1017	160		Temporary front fence supplied and installed by owner as required		1	1	note	$0.00	$0.00		Exclude	As required, supplied and installed by XXX	Temporary Fencing
66	1017	340		Scaffolding - provisional sum		1	1	pcsum	$0.00	$0.00		Exclude	As required, supplied and installed by XXX	Scaffolding
67	1017	999		XXX Fence mesh		1	1	sum	$300.00	$300.00			XXX Fence mesh	Provision - No Order
Sheet3

The result should look as below but with subtotals for each order-Ref

Book3
	A	B	C	D	E	F	G
1	Order-Ref	Title	Description 1	Quantity	Units	Price	Total
3	52	Provision - No Order	Building consent submission	1	sum	$750.00	$750.00
10	52	Provision - No Order	Design (draft) - 1 storey	179	m2	$15.00	$2,685.00
18	61	Provision - No Order	Over $400,000 to $600,000	1	each	$1,304.35	$1,304.35
22	62	Provision - No Order	Construction Work Insurance	179	m2	$7.50	$1,342.50
23	110	Colour Consultant	Colour consultancy	1	sum	$300.00	$300.00
26	160	Temporary Fencing	Temporary fences @ $0.56 per week per lm	18	weeks	$11.20	$201.60
27	160	Temporary Fencing	Temporary Fence delivery	1		$50.00	$50.00
28	170	Temporary Power	Temporary Power	1	each	$890.00	$890.00
36	180	Site Port a Loo (Temporary Toilet)	Portaloo Delivery & Collection , hire and cleaning service (incl. consumables)	18	week	$44.00	$792.00
37	260	Services (Power, Water, Data)	Three Phase Utility mains up to 20 metres	1	each	$1,300.00	$1,300.00
38	260	Services (Power, Water, Data)	Power & Water	18	week	$40.00	$720.00
39	290	Waste Management (Rubbish Skip)	9m³ Bin Delivered to site (to be charged by weight) - Initial delivery Location of Skip TBC by builder on site	1	bins	$380.00	$380.00
42	340	Scaffolding	Erection/dismatle/cartage $45 x lm	66	sum	$45.00	$2,970.00
47	340	Scaffolding	Weekly rental $2.4 x lm	5	week	$158.40	$792.00
52	340	Scaffolding	Weekly rental Gable $7.2 x lm	3	week	$57.60	$172.80
53	340	Scaffolding	Weekly Inspection (depends on lm_gable)	8	week	$65.00	$520.00
56	350	Safety Nets	Supply and installation of nets	179	m2	$4.00	$716.00
57	980	Final Clean	Final Clean	179	m2	$3.00	$537.00
58	999	Provision - No Order	9m³ Bin Delivered to site (to be charged by weight) -Additional delivery	2	bins	$380.00	$760.00
60	999	Provision - No Order	Clean up - Labour ($2 x Selling area)	179	sum	$2.00	$358.00
64	999	Provision - No Order	XXX Fence mesh	1	sum	$300.00	$300.00
67		Provision - No Order	Additional Design Time & QS Time	1	Sum	$680.00	$680.00
Sheet3 (2)

Regards Peter