Doing Subtototals with power Query

[hannouech]

Hi,

I have a table containing data that is refreshbale every month, my question is: is there a solution to do subtotal with power query?

This is a link to the file and what I need

https://www.cjoint.com/c/IAwk15tz46s

Thanks very much

 

[sandy666]

maybe better is PivotTable?

 

[sandy666]

something like

N_CPTE	Date	Libelle	Sum of MvtD	Sum of MvtC
202600​	01/01/2018​	Transaction 1	0.000​	0.000​
202600​	01/01/2018​	Transaction 2	0.000​	3,000,000.000​
202600 Total			0.000​	3,000,000.000​
222000​	01/01/2018​	Transaction 3	0.000​	0.000​
222000​	01/01/2018​	Transaction 4	0.000​	300,000.000​
222000 Total			0.000​	300,000.000​
242000​	01/01/2018​	Transaction 5	0.000​	0.000​
242000​	01/01/2018​	Transaction 6	0.000​	3,876,234.171​
242000​	21/03/2018​	Transaction 7	0.000​	134,565.780​
242000 Total			0.000​	4,010,799.951​
262​	01/01/2018​	Transaction 8	0.000​	0.000​
262​	01/01/2018​	Transaction 9	0.000​	919,010.790​
262​	21/03/2018​	Transaction 10	919,010.790​	0.000​
262 Total			919,010.790​	919,010.790​
426​	01/01/2018​	Transaction 11	0.000​	0.000​
426​	01/01/2018​	Transaction 12	30,132.758​	0.000​
426​	26/06/2018​	Transaction 13	1,272.150​	0.000​
426​	31/12/2018​	Transaction 14	17,798.400​	0.000​
426 Total			49,203.308​	0.000​
4442​	01/01/2018​	Transaction 15	0.000​	0.000​
4442​	01/01/2018​	Transaction 16	594,264.996​	0.000​
4442 Total			594,264.996​	0.000​
4488​	01/01/2018​	Transaction 17	0.000​	0.000​
4488​	01/01/2018​	Transaction 18	287,445.752​	0.000​
4488 Total			287,445.752​	0.000​
4562​	01/01/2018​	Transaction 19	0.000​	0.000​
4562​	01/01/2018​	Transaction 20	56,781.878​	0.000​
4562​	10/05/2018​	Transaction 21	2,794.514​	0.000​
4562​	10/05/2018​	Transaction 22	2,794.512​	0.000​
4562​	10/05/2018​	Transaction 23	3,007.536​	0.000​
4562​	02/08/2018​	Transaction 24	2,805.785​	0.000​
4562​	02/08/2018​	Transaction 25	2,805.786​	0.000​
4562 Total			70,990.010​	0.000​
4564​	01/01/2018​	Transaction 26	0.000​	0.000​
4564​	01/01/2018​	Transaction 27	32,064.008​	0.000​
4564​	14/02/2018​	Transaction 28	6,354.429​	0.000​
4564​	12/07/2018​	Transaction 29	74.738​	0.000​
4564​	01/12/2018​	Transaction 30	1,964.400​	0.000​
4564 Total			40,457.574​	0.000​
362200​	01/01/2018​	Transaction 31	0.000​	0.000​
362200​	01/01/2018​	Transaction 32	0.000​	10,554.990​
362200​	22/01/2018​	Transaction 33	0.000​	571.769​
362200​	25/01/2018​	Transaction 34	156.459​	0.000​
362200​	25/01/2018​	Transaction 35	109.628​	0.000​
362200​	06/02/2018​	Transaction 36	883.409​	0.000​
362200​	21/02/2018​	Transaction 37	0.000​	571.769​
362200​	23/02/2018​	Transaction 38	265.770​	0.000​
362200​	23/02/2018​	Transaction 39	176.099​	0.000​
362200​	20/03/2018​	Transaction 40	0.000​	571.769​
362200​	21/03/2018​	Transaction 41	0.000​	34,445.010​
362200​	23/03/2018​	Transaction 42	175.898​	0.000​
362200​	23/03/2018​	Transaction 43	265.448​	0.000​
362200​	06/04/2018​	Transaction 44	7,500.000​	0.000​
362200​	13/04/2018​	Transaction 45	7,500.000​	0.000​
362200​	19/04/2018​	Transaction 46	7,500.000​	0.000​
362200​	22/04/2018​	Transaction 47	0.000​	571.769​
362200​	25/04/2018​	Transaction 48	175.697​	0.000​
362200​	25/04/2018​	Transaction 49	265.124​	0.000​
362200​	26/04/2018​	Transaction 50	7,500.000​	0.000​
362200​	22/05/2018​	Transaction 51	0.000​	571.769​
362200​	25/05/2018​	Transaction 52	175.490​	0.000​
362200​	25/05/2018​	Transaction 53	264.795​	0.000​
362200​	22/06/2018​	Transaction 54	0.000​	961.445​
362200​	25/06/2018​	Transaction 55	264.462​	0.000​
362200​	25/06/2018​	Transaction 56	175.283​	0.000​
362200​	22/07/2018​	Transaction 57	0.000​	961.445​
362200​	24/07/2018​	Transaction 58	175.071​	0.000​
362200​	24/07/2018​	Transaction 59	264.126​	0.000​
362200​	31/07/2018​	Transaction 60	3,750.000​	0.000​
362200​	23/08/2018​	Transaction 61	0.000​	961.445​
362200​	24/08/2018​	Transaction 62	174.857​	0.000​
362200​	24/08/2018​	Transaction 63	263.786​	0.000​
362200​	24/09/2018​	Transaction 64	0.000​	1,177.904​
362200​	25/09/2018​	Transaction 65	263.441​	0.000​
362200​	25/09/2018​	Transaction 66	174.642​	0.000​
362200​	22/10/2018​	Transaction 67	0.000​	1,069.674​
362200​	24/10/2018​	Transaction 68	263.096​	0.000​
362200​	24/10/2018​	Transaction 69	174.425​	0.000​
362200​	22/11/2018​	Transaction 70	0.000​	1,069.674​
362200​	23/11/2018​	Transaction 71	262.745​	0.000​
362200​	23/11/2018​	Transaction 72	174.203​	0.000​
362200​	17/12/2018​	Transaction 73	0.000​	1,069.674​
362200​	25/12/2018​	Transaction 74	262.389​	0.000​
362200​	25/12/2018​	Transaction 75	173.979​	0.000​
362200 Total			39,730.314​	55,130.102​
Grand Total			2,001,102.743​	8,284,940.843​

 

[hannouech]

Yes I have an idea how to do it with subtotals functions and Pivot tables but I have to do it with power query

Thanks for any solution

 

[sandy666]

if you want exactly result like you show in your file I can say only

good luck