JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Doing Subtototals with power Query
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
VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Banned - Rules violations
maybe better is PivotTable?
Banned - Rules violations
something like
[Table="width:, class:head"]N_CPTE [/td][td=bgcolor:#D9E1F2]Date [/td][td=bgcolor:#D9E1F2]Libelle [/td][td=bgcolor:#D9E1F2]Sum of MvtD [/td][td=bgcolor:#D9E1F2]Sum of MvtC [/td][/tr]202600
[/td][td]01/01/2018
[/td][td]Transaction 1[/td][td]0.000
[/td][td]0.000
[/td][/tr]202600
[/td][td]01/01/2018
[/td][td]Transaction 2[/td][td]0.000
[/td][td]3,000,000.000
[/td][/tr]202600 Total [/td][td][/td][td][/td][td]0.000
[/td][td]3,000,000.000
[/td][/tr]222000
[/td][td]01/01/2018
[/td][td]Transaction 3[/td][td]0.000
[/td][td]0.000
[/td][/tr]222000
[/td][td]01/01/2018
[/td][td]Transaction 4[/td][td]0.000
[/td][td]300,000.000
[/td][/tr]222000 Total [/td][td][/td][td][/td][td]0.000
[/td][td]300,000.000
[/td][/tr]242000
[/td][td]01/01/2018
[/td][td]Transaction 5[/td][td]0.000
[/td][td]0.000
[/td][/tr]242000
[/td][td]01/01/2018
[/td][td]Transaction 6[/td][td]0.000
[/td][td]3,876,234.171
[/td][/tr]242000
[/td][td]21/03/2018
[/td][td]Transaction 7[/td][td]0.000
[/td][td]134,565.780
[/td][/tr]242000 Total [/td][td][/td][td][/td][td]0.000
[/td][td]4,010,799.951
[/td][/tr]262
[/td][td]01/01/2018
[/td][td]Transaction 8[/td][td]0.000
[/td][td]0.000
[/td][/tr]262
[/td][td]01/01/2018
[/td][td]Transaction 9[/td][td]0.000
[/td][td]919,010.790
[/td][/tr]262
[/td][td]21/03/2018
[/td][td]Transaction 10[/td][td]919,010.790
[/td][td]0.000
[/td][/tr]262 Total [/td][td][/td][td][/td][td]919,010.790
[/td][td]919,010.790
[/td][/tr]426
[/td][td]01/01/2018
[/td][td]Transaction 11[/td][td]0.000
[/td][td]0.000
[/td][/tr]426
[/td][td]01/01/2018
[/td][td]Transaction 12[/td][td]30,132.758
[/td][td]0.000
[/td][/tr]426
[/td][td]26/06/2018
[/td][td]Transaction 13[/td][td]1,272.150
[/td][td]0.000
[/td][/tr]426
[/td][td]31/12/2018
[/td][td]Transaction 14[/td][td]17,798.400
[/td][td]0.000
[/td][/tr]426 Total [/td][td][/td][td][/td][td]49,203.308
[/td][td]0.000
[/td][/tr]4442
[/td][td]01/01/2018
[/td][td]Transaction 15[/td][td]0.000
[/td][td]0.000
[/td][/tr]4442
[/td][td]01/01/2018
[/td][td]Transaction 16[/td][td]594,264.996
[/td][td]0.000
[/td][/tr]4442 Total [/td][td][/td][td][/td][td]594,264.996
[/td][td]0.000
[/td][/tr]4488
[/td][td]01/01/2018
[/td][td]Transaction 17[/td][td]0.000
[/td][td]0.000
[/td][/tr]4488
[/td][td]01/01/2018
[/td][td]Transaction 18[/td][td]287,445.752
[/td][td]0.000
[/td][/tr]4488 Total [/td][td][/td][td][/td][td]287,445.752
[/td][td]0.000
[/td][/tr]4562
[/td][td]01/01/2018
[/td][td]Transaction 19[/td][td]0.000
[/td][td]0.000
[/td][/tr]4562
[/td][td]01/01/2018
[/td][td]Transaction 20[/td][td]56,781.878
[/td][td]0.000
[/td][/tr]4562
[/td][td]10/05/2018
[/td][td]Transaction 21[/td][td]2,794.514
[/td][td]0.000
[/td][/tr]4562
[/td][td]10/05/2018
[/td][td]Transaction 22[/td][td]2,794.512
[/td][td]0.000
[/td][/tr]4562
[/td][td]10/05/2018
[/td][td]Transaction 23[/td][td]3,007.536
[/td][td]0.000
[/td][/tr]4562
[/td][td]02/08/2018
[/td][td]Transaction 24[/td][td]2,805.785
[/td][td]0.000
[/td][/tr]4562
[/td][td]02/08/2018
[/td][td]Transaction 25[/td][td]2,805.786
[/td][td]0.000
[/td][/tr]4562 Total [/td][td][/td][td][/td][td]70,990.010
[/td][td]0.000
[/td][/tr]4564
[/td][td]01/01/2018
[/td][td]Transaction 26[/td][td]0.000
[/td][td]0.000
[/td][/tr]4564
[/td][td]01/01/2018
[/td][td]Transaction 27[/td][td]32,064.008
[/td][td]0.000
[/td][/tr]4564
[/td][td]14/02/2018
[/td][td]Transaction 28[/td][td]6,354.429
[/td][td]0.000
[/td][/tr]4564
[/td][td]12/07/2018
[/td][td]Transaction 29[/td][td]74.738
[/td][td]0.000
[/td][/tr]4564
[/td][td]01/12/2018
[/td][td]Transaction 30[/td][td]1,964.400
[/td][td]0.000
[/td][/tr]4564 Total [/td][td][/td][td][/td][td]40,457.574
[/td][td]0.000
[/td][/tr]362200
[/td][td]01/01/2018
[/td][td]Transaction 31[/td][td]0.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]01/01/2018
[/td][td]Transaction 32[/td][td]0.000
[/td][td]10,554.990
[/td][/tr]362200
[/td][td]22/01/2018
[/td][td]Transaction 33[/td][td]0.000
[/td][td]571.769
[/td][/tr]362200
[/td][td]25/01/2018
[/td][td]Transaction 34[/td][td]156.459
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/01/2018
[/td][td]Transaction 35[/td][td]109.628
[/td][td]0.000
[/td][/tr]362200
[/td][td]06/02/2018
[/td][td]Transaction 36[/td][td]883.409
[/td][td]0.000
[/td][/tr]362200
[/td][td]21/02/2018
[/td][td]Transaction 37[/td][td]0.000
[/td][td]571.769
[/td][/tr]362200
[/td][td]23/02/2018
[/td][td]Transaction 38[/td][td]265.770
[/td][td]0.000
[/td][/tr]362200
[/td][td]23/02/2018
[/td][td]Transaction 39[/td][td]176.099
[/td][td]0.000
[/td][/tr]362200
[/td][td]20/03/2018
[/td][td]Transaction 40[/td][td]0.000
[/td][td]571.769
[/td][/tr]362200
[/td][td]21/03/2018
[/td][td]Transaction 41[/td][td]0.000
[/td][td]34,445.010
[/td][/tr]362200
[/td][td]23/03/2018
[/td][td]Transaction 42[/td][td]175.898
[/td][td]0.000
[/td][/tr]362200
[/td][td]23/03/2018
[/td][td]Transaction 43[/td][td]265.448
[/td][td]0.000
[/td][/tr]362200
[/td][td]06/04/2018
[/td][td]Transaction 44[/td][td]7,500.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]13/04/2018
[/td][td]Transaction 45[/td][td]7,500.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]19/04/2018
[/td][td]Transaction 46[/td][td]7,500.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/04/2018
[/td][td]Transaction 47[/td][td]0.000
[/td][td]571.769
[/td][/tr]362200
[/td][td]25/04/2018
[/td][td]Transaction 48[/td][td]175.697
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/04/2018
[/td][td]Transaction 49[/td][td]265.124
[/td][td]0.000
[/td][/tr]362200
[/td][td]26/04/2018
[/td][td]Transaction 50[/td][td]7,500.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/05/2018
[/td][td]Transaction 51[/td][td]0.000
[/td][td]571.769
[/td][/tr]362200
[/td][td]25/05/2018
[/td][td]Transaction 52[/td][td]175.490
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/05/2018
[/td][td]Transaction 53[/td][td]264.795
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/06/2018
[/td][td]Transaction 54[/td][td]0.000
[/td][td]961.445
[/td][/tr]362200
[/td][td]25/06/2018
[/td][td]Transaction 55[/td][td]264.462
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/06/2018
[/td][td]Transaction 56[/td][td]175.283
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/07/2018
[/td][td]Transaction 57[/td][td]0.000
[/td][td]961.445
[/td][/tr]362200
[/td][td]24/07/2018
[/td][td]Transaction 58[/td][td]175.071
[/td][td]0.000
[/td][/tr]362200
[/td][td]24/07/2018
[/td][td]Transaction 59[/td][td]264.126
[/td][td]0.000
[/td][/tr]362200
[/td][td]31/07/2018
[/td][td]Transaction 60[/td][td]3,750.000
[/td][td]0.000
[/td][/tr]362200
[/td][td]23/08/2018
[/td][td]Transaction 61[/td][td]0.000
[/td][td]961.445
[/td][/tr]362200
[/td][td]24/08/2018
[/td][td]Transaction 62[/td][td]174.857
[/td][td]0.000
[/td][/tr]362200
[/td][td]24/08/2018
[/td][td]Transaction 63[/td][td]263.786
[/td][td]0.000
[/td][/tr]362200
[/td][td]24/09/2018
[/td][td]Transaction 64[/td][td]0.000
[/td][td]1,177.904
[/td][/tr]362200
[/td][td]25/09/2018
[/td][td]Transaction 65[/td][td]263.441
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/09/2018
[/td][td]Transaction 66[/td][td]174.642
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/10/2018
[/td][td]Transaction 67[/td][td]0.000
[/td][td]1,069.674
[/td][/tr]362200
[/td][td]24/10/2018
[/td][td]Transaction 68[/td][td]263.096
[/td][td]0.000
[/td][/tr]362200
[/td][td]24/10/2018
[/td][td]Transaction 69[/td][td]174.425
[/td][td]0.000
[/td][/tr]362200
[/td][td]22/11/2018
[/td][td]Transaction 70[/td][td]0.000
[/td][td]1,069.674
[/td][/tr]362200
[/td][td]23/11/2018
[/td][td]Transaction 71[/td][td]262.745
[/td][td]0.000
[/td][/tr]362200
[/td][td]23/11/2018
[/td][td]Transaction 72[/td][td]174.203
[/td][td]0.000
[/td][/tr]362200
[/td][td]17/12/2018
[/td][td]Transaction 73[/td][td]0.000
[/td][td]1,069.674
[/td][/tr]362200
[/td][td]25/12/2018
[/td][td]Transaction 74[/td][td]262.389
[/td][td]0.000
[/td][/tr]362200
[/td][td]25/12/2018
[/td][td]Transaction 75[/td][td]173.979
[/td][td]0.000
[/td][/tr]362200 Total [/td][td][/td][td][/td][td]39,730.314
[/td][td]55,130.102
[/td][/tr]Grand Total [/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]2,001,102.743
[/td][td=bgcolor:#D9E1F2]8,284,940.843
[/td][/tr]
Yes I have an idea how to do it with subtotals functions and Pivot tables but I have to do it with power query
Banned - Rules violations
if you want exactly result like you show in your file I can say only
good luck
Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444
