Faster Alternative to Conditional Running Total than List.Sum

[takoyaki]

Hello everyone, I'm trying to add a column that will sum up values in another column based on a few conditions. The code I currently have uses List.Sum. I have to do a similar conditional running total calculation several times in my query along with a few other transformations. The query currently takes about 7.5 minutes to evaluate and I suspect that this code is the culprit. Is there a better way I could handle the code below?

I am aware that there are ways to use a custom function based on List.Generate but I'm not sure how to customize the custom function to exclude values that don't meet desired conditions.

ConditionalRT = Table.AddColumn(Source, "Conditional RT", (x)=> List.Sum(Table.SelectRows(Source, each _[Condition1] = x[Condition1] and _[Condition2] < x[Condition2] and _[Condition3] = "Condition3")[Values]), type number),

Thank you again in advance for your help.

 

[JGordon11]

About how many rows are in Source?

 

[takoyaki]

About how many rows are in Source?

Hi JGordan! Originally there are about 4,500. I group them immediately and for the rest of the query there are about 150 rows.

 

[JGordon11]

Edit

 

[takoyaki]

Edit

Thank you so much! I'm just wondering - it looks like the conditions you used are hard-coded constants. The conditions in my example compare the row's value to the entire column's value (for instance if the condition is for the Year, the goal is to sum up the column's values if they match the row's Year). The only constant condition in my example is Condition3. I am also curious about choosing 300 for lst1. Am I misunderstanding something or do I need to clarify my goal better?

 

[JGordon11]

If you write a TableAddColumn step that adds a helper column with [Value] if conditions are met and zero otherwise. That will be used in tbl step. I used dummy conditions to test the speed.
300 is not a maximum it is used to evaluate the data in 300 row chunks which improves the speed greatly

this is the amended code. you need to change the tbl step to whatever conditions you have.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.AddColumn(Source, "Helper", each if [Condition1]>3 and [Condition2]<12 and [Condition3] = "A" then [Value] else 0),
    RunningTotal = (lst as list) as list => 
        let 
            lst1 = List.Split(lst, 300),
            lst2 = List.Zip({lst1,{0..List.Count(lst1)-1}}),
            lst3 = List.Accumulate(lst2, {}, (ss,cc)=> ss & {List.Skip(List.Accumulate(cc{0}, (if cc{1} = 0 then {0} else {List.Last(List.Last(ss))}), (s,c)=> s & {List.Last(s) + c}))}),
            lst4 = List.Combine(lst3)
        in   
            lst4,
    lst = RunningTotal(tbl[Helper]),
    Result = Table.FromColumns(Table.ToColumns(Source) & {lst}, Table.ColumnNames(Source) & {"ConditionalRT"})
in
    Result

PQ Table_AddRunningTotal.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Condition1	Condition2	Condition3	Value		Condition1	Condition2	Condition3	Value	ConditionalRT
2	6	11	A	8000		6	11	A	8000	8000
3	17	4	B	1000		17	4	B	1000	8000
4	15	16	A	7000		15	16	A	7000	8000
5	10	7	B	3000		10	7	B	3000	8000
6	12	9	A	6000		12	9	A	6000	14000
7	2	5	B	4000		2	5	B	4000	14000
8	18	8	A	9000		18	8	A	9000	23000
9	3	1	B	2000		3	1	B	2000	23000
10	13	14	A	5000		13	14	A	5000	23000
Sheet2

 

[takoyaki]

If you write a TableAddColumn step that adds a helper column with [Value] if conditions are met and zero otherwise. That will be used in tbl step. I used dummy conditions to test the speed.
300 is not a maximum it is used to evaluate the data in 300 row chunks which improves the speed greatly

this is the amended code. you need to change the tbl step to whatever conditions you have.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.AddColumn(Source, "Helper", each if [Condition1]>3 and [Condition2]<12 and [Condition3] = "A" then [Value] else 0),
    RunningTotal = (lst as list) as list =>
        let
            lst1 = List.Split(lst, 300),
            lst2 = List.Zip({lst1,{0..List.Count(lst1)-1}}),
            lst3 = List.Accumulate(lst2, {}, (ss,cc)=> ss & {List.Skip(List.Accumulate(cc{0}, (if cc{1} = 0 then {0} else {List.Last(List.Last(ss))}), (s,c)=> s & {List.Last(s) + c}))}),
            lst4 = List.Combine(lst3)
        in  
            lst4,
    lst = RunningTotal(tbl[Helper]),
    Result = Table.FromColumns(Table.ToColumns(Source) & {lst}, Table.ColumnNames(Source) & {"ConditionalRT"})
in
    Result

PQ Table_AddRunningTotal.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Condition1	Condition2	Condition3	Value		Condition1	Condition2	Condition3	Value	ConditionalRT
2	6	11	A	8000		6	11	A	8000	8000
3	17	4	B	1000		17	4	B	1000	8000
4	15	16	A	7000		15	16	A	7000	8000
5	10	7	B	3000		10	7	B	3000	8000
6	12	9	A	6000		12	9	A	6000	14000
7	2	5	B	4000		2	5	B	4000	14000
8	18	8	A	9000		18	8	A	9000	23000
9	3	1	B	2000		3	1	B	2000	23000
10	13	14	A	5000		13	14	A	5000	23000
Sheet2

Thank you so much! I think this is definitely on the right track. I always appreciate your help!

 

[takoyaki]

If you write a TableAddColumn step that adds a helper column with [Value] if conditions are met and zero otherwise. That will be used in tbl step. I used dummy conditions to test the speed.
300 is not a maximum it is used to evaluate the data in 300 row chunks which improves the speed greatly

this is the amended code. you need to change the tbl step to whatever conditions you have.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.AddColumn(Source, "Helper", each if [Condition1]>3 and [Condition2]<12 and [Condition3] = "A" then [Value] else 0),
    RunningTotal = (lst as list) as list =>
        let
            lst1 = List.Split(lst, 300),
            lst2 = List.Zip({lst1,{0..List.Count(lst1)-1}}),
            lst3 = List.Accumulate(lst2, {}, (ss,cc)=> ss & {List.Skip(List.Accumulate(cc{0}, (if cc{1} = 0 then {0} else {List.Last(List.Last(ss))}), (s,c)=> s & {List.Last(s) + c}))}),
            lst4 = List.Combine(lst3)
        in  
            lst4,
    lst = RunningTotal(tbl[Helper]),
    Result = Table.FromColumns(Table.ToColumns(Source) & {lst}, Table.ColumnNames(Source) & {"ConditionalRT"})
in
    Result

PQ Table_AddRunningTotal.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Condition1	Condition2	Condition3	Value		Condition1	Condition2	Condition3	Value	ConditionalRT
2	6	11	A	8000		6	11	A	8000	8000
3	17	4	B	1000		17	4	B	1000	8000
4	15	16	A	7000		15	16	A	7000	8000
5	10	7	B	3000		10	7	B	3000	8000
6	12	9	A	6000		12	9	A	6000	14000
7	2	5	B	4000		2	5	B	4000	14000
8	18	8	A	9000		18	8	A	9000	23000
9	3	1	B	2000		3	1	B	2000	23000
10	13	14	A	5000		13	14	A	5000	23000
Sheet2

Hi JGordon11, I really appreciate all of your help. I've been trying to apply your solution to my scenario for the past few weeks and am not having any luck. I understand your intention with the helper column, but due to the way I am trying to sum my values I don't know of any way to filter my table before using your function. Here is an example of the result I am trying to produce, using Excel:

The SUMPRODUCT conditions being:

=SUMPRODUCT(([Year]=[@Year])*([Month]<[@Month])*([Value]))

I don't believe a helper column will be possible in this scenario. Doing this sum is very simple in Excel but very difficult in Power Query. The below M-code gets the desired result, but is very time consuming for the query to calculate.

ConditionalRT = Table.AddColumn(Source, "Conditional RT", (x)=> List.Sum(Table.SelectRows(Source, each _[Year] = x[Year] and _[Month] < x[Month])[Value]), type number)

I'm creating a tool that will be fed thousands of rows of data potentially and will be providing this tool for others on my team to use so I'd like it to be as efficient as possible.

 

[JGordon11]

I ran this on 20,000 rows and it took about 30 seconds

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Sort = Table.Sort(AddIndex,{{"Year", Order.Descending}, {"Month", Order.Descending}}),
    GroupLocal = Table.Buffer(Table.Group(Sort, {"Year"}, {{"All", each _}},0)),
    TransformAll = Table.TransformColumns(GroupLocal, {"All", each    
                    Table.AddColumn(_, "Conditional RT", (x)=> List.Sum(Table.SelectRows(_, (y)=> y[Month] < x[Month])[Value]), type number)}),
    Expand = Table.ExpandTableColumn(TransformAll, "All", {"Month", "Value", "Sumproduct", "Index", "Conditional RT"}),
    #"Re-Sort" = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    RemoveIndex = Table.RemoveColumns(#"Re-Sort",{"Index"})
in
    RemoveIndex

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Year	Month	Value	Sumproduct		Year	Month	Value	Sumproduct	Conditional RT
2	2022	5	1353	2337		2022	5	1353	2337	2337
3	2020	12	1599	4428		2020	12	1599	4428	4428
4	2020	7	1968	369		2020	7	1968	369	369
5	2021	7	1722	2952		2021	7	1722	2952	2952
6	2021	6	123	2829		2021	6	123	2829	2829
7	2021	5	984	1845		2021	5	984	1845	1845
8	2023	3	1476	0		2023	3	1476	0
9	2021	1	738	0		2021	1	738	0
10	2023	10	2091	5535		2023	10	2091	5535	5535
11	2023	3	2214	0		2023	3	2214	0
12	2023	4	615	3690		2023	4	615	3690	3690
13	2022	9	492	3690		2022	9	492	3690	3690
14	2020	1	369	0		2020	1	369	0
15	2021	11	861	4674		2021	11	861	4674	4674
16	2020	7	246	369		2020	7	246	369	369
17	2020	8	1845	2583		2020	8	1845	2583	2583
18	2022	4	2337	0		2022	4	2337	0
19	2021	3	1107	738		2021	3	1107	738	738
20	2023	5	1230	4305		2023	5	1230	4305	4305
21
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=SUMPRODUCT(([Year]=[@Year])*([Month]<[@Month])*([Value]))

 

[takoyaki]

I ran this on 20,000 rows and it took about 30 seconds

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Sort = Table.Sort(AddIndex,{{"Year", Order.Descending}, {"Month", Order.Descending}}),
    GroupLocal = Table.Buffer(Table.Group(Sort, {"Year"}, {{"All", each _}},0)),
    TransformAll = Table.TransformColumns(GroupLocal, {"All", each   
                    Table.AddColumn(_, "Conditional RT", (x)=> List.Sum(Table.SelectRows(_, (y)=> y[Month] < x[Month])[Value]), type number)}),
    Expand = Table.ExpandTableColumn(TransformAll, "All", {"Month", "Value", "Sumproduct", "Index", "Conditional RT"}),
    #"Re-Sort" = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    RemoveIndex = Table.RemoveColumns(#"Re-Sort",{"Index"})
in
    RemoveIndex

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Year	Month	Value	Sumproduct		Year	Month	Value	Sumproduct	Conditional RT
2	2022	5	1353	2337		2022	5	1353	2337	2337
3	2020	12	1599	4428		2020	12	1599	4428	4428
4	2020	7	1968	369		2020	7	1968	369	369
5	2021	7	1722	2952		2021	7	1722	2952	2952
6	2021	6	123	2829		2021	6	123	2829	2829
7	2021	5	984	1845		2021	5	984	1845	1845
8	2023	3	1476	0		2023	3	1476	0
9	2021	1	738	0		2021	1	738	0
10	2023	10	2091	5535		2023	10	2091	5535	5535
11	2023	3	2214	0		2023	3	2214	0
12	2023	4	615	3690		2023	4	615	3690	3690
13	2022	9	492	3690		2022	9	492	3690	3690
14	2020	1	369	0		2020	1	369	0
15	2021	11	861	4674		2021	11	861	4674	4674
16	2020	7	246	369		2020	7	246	369	369
17	2020	8	1845	2583		2020	8	1845	2583	2583
18	2022	4	2337	0		2022	4	2337	0
19	2021	3	1107	738		2021	3	1107	738	738
20	2023	5	1230	4305		2023	5	1230	4305	4305
21
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=SUMPRODUCT(([Year]=[@Year])*([Month]<[@Month])*([Value]))

You're incredible. I implemented your solution into my query and it's almost instantaneous.

Thank you so, so much!