Forum Discussion
Conditional Simple Moving Average
Hi all, I am trying to build a simple moving average for a table containing 500 stocks and daily data for multiple years. I have built a small test set with 3 stocks with 2 years of data. Some 1500 rows only. I am getting results but the time it takes and MBs it takes is exploding already on this test. Code below. All runs quick and flawless untill list.average. Removing non necessary columns does not help. Tried a few other methods but all with similar slow result. Any help is appreciated.
let
Source = Excel.Workbook(File.Contents("C:\test.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ticker", type text}, {"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"dividend", type number}, {"split", Int64.Type}, {"adj_open", type number}, {"adj_high", type number}, {"adj_low", type number}, {"adj_close", type number}, {"adj_volume", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"ticker", Order.Ascending}, {"date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ticker"}, {{"Count", each _, type table [ticker=nullable text, date=nullable date, open=nullable number, high=nullable number, low=nullable number, close=nullable number, volume=nullable number, dividend=nullable number, split=nullable number, adj_open=nullable number, adj_high=nullable number, adj_low=nullable number, adj_close=nullable number, adj_volume=nullable number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "tickerIndex", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"ticker"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"ticker", "date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume"}, {"ticker", "date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume"}),
#"Grouped ticker" = Table.Group(#"Expanded Count", {"ticker"}, {{"Details", each _, type table [ticker=nullable text, date=nullable date, open=nullable number, high=nullable number, low=nullable number, close=nullable number, volume=nullable number, dividend=nullable number, split=nullable number, adj_open=nullable number, adj_high=nullable number, adj_low=nullable number, adj_close=nullable number, adj_volume=nullable number, tickerIndex=nullable number]}}),
#"Added dateIndex" = Table.AddColumn(#"Grouped ticker", "TableWithDateIndex", each Table.AddIndexColumn([Details], "DateIndex", 1, 1, Int64.Type)),
#"Removed Columns1" = Table.RemoveColumns(#"Added dateIndex",{"ticker", "Details"}),
#"Expanded TableWithDateIndex" = Table.ExpandTableColumn(#"Removed Columns1", "TableWithDateIndex", {"ticker", "date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume", "tickerIndex", "DateIndex"}, {"ticker", "date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume", "tickerIndex", "DateIndex"}),
#"Added Index1" = Table.AddIndexColumn(#"Expanded TableWithDateIndex", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each #"Added Index1"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Custom][adj_close]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [DateIndex] < 3 then null else List.Range([Custom.1], [Index] - 3, 3)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"ticker", type text}, {"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"dividend", Int64.Type}, {"split", Int64.Type}, {"adj_open", type number}, {"adj_high", type number}, {"adj_low", type number}, {"adj_close", type number}, {"adj_volume", Int64.Type}, {"tickerIndex", Int64.Type}, {"DateIndex", Int64.Type}, {"Index", Int64.Type}, {"Custom.2", type any}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Custom", each List.Average([Custom.2]))
in
#"Added Custom3"
5 Replies
Hi, Jan-Jaap72
According to your M code , it seems you want to get the average of the previous_3_row values in for the [adj_close] column.
And for your M code , you create mult-table and columns in it .
For your need , you can try to use this M code to see if there is a improvement for performance.
let Source = Excel.Workbook(File.Contents("C:\test.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ticker", type text}, {"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"dividend", type number}, {"split", Int64.Type}, {"adj_open", type number}, {"adj_high", type number}, {"adj_low", type number}, {"adj_close", type number}, {"adj_volume", Int64.Type}}), Custom1 = Table.Group(#"Changed Type","ticker",{"test",(x)=> Table.AddIndexColumn( x, "Index", 1, 1, Int64.Type) }), #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume", "Index"}, {"date", "open", "high", "low", "close", "volume", "dividend", "split", "adj_open", "adj_high", "adj_low", "adj_close", "adj_volume", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded test", "Custom", (x)=> try List.Average( List.Range(#"Expanded test"[adj_close],x[Index]-3,3)) otherwise null ) , #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"dividend", type number}, {"split", Int64.Type}, {"adj_open", type number}, {"adj_high", type number}, {"adj_low", type number}, {"adj_close", type number}, {"adj_volume", Int64.Type}, {"Custom", type number}, {"Index", Int64.Type}}) in #"Changed Type1"Then we can get this in the Power Query Editor:
And also we can keep the Index column and apply the data to the Desktop and we can click "New Column" to create a calculated column :
Column = var _t = FILTER('Query1' , 'Query1'[ticker] = EARLIER('Query1'[ticker]) && 'Query1'[Index]<=EARLIER(Query1[Index]) && 'Query1'[Index]>EARLIER(Query1[Index])-3) return AVERAGEX(_t ,[adj_close])We can also get this in Desktop:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you Aniya for your reply. I tested your code and learning from it. The output is good, but it remains as slow as before. I also have tried Table.Buffer, also didn't help sofar. Please see attached a data sample of 3 tickers and 10 days of data (i am normally testing with 500 days of data which already makes it very very slow). Eventually I am using close to 10 years of data, and over 500 tickers.
ticker
date
open
high
low
close
volume
dividend
split
adj_open
adj_high
adj_low
adj_close
adj_volume
A
2020-01-02
85.9
86.35
85.2
85.95
1410486
0
1
84.07880746
84.51926687
83.39364837
84.12774739
1410486
A
2020-01-03
84.67
85.33
84.5
84.57
1118322
0
1
82.87488507
83.5208922
82.70848929
82.7770052
1118322
A
2020-01-06
84
84.82
83.6
84.82
1992491
0
1
82.21908995
83.02170487
81.82757047
83.02170487
1992491
A
2020-01-07
83.96
85.26
83.94
85.08
1722896
0
1
82.179938
83.4523763
82.16036203
83.27619253
1722896
A
2020-01-08
85.96
86.47
85.2
85.92
1847585
0
1
84.13753538
84.63672271
83.39364837
84.09838343
1847585
A
2020-01-09
86.46
87.7
86.17
87.27
1912699
0
1
84.62693472
85.8406451
84.3430831
85.41976166
1912699
A
2020-01-10
87.72
88.235
87.32
87.59
1417028
0
1
85.86022107
86.3643024
85.4687016
85.73297724
1417028
A
2020-01-13
87.81
88.32
86.7384
87.46
1630160
0
1
85.94831296
86.44750029
84.89943228
85.60573341
1630160
A
2020-01-14
87.27
88.21
86.7
87.99
1675243
0
1
85.41976166
86.33983243
84.86184641
86.12449672
1675243
A
2020-01-15
87.63
89.11
87.55
88.62
1630364
0
1
85.77212919
87.22075125
85.6938253
86.74113989
1630364
AAL
2020-01-02
28.98
29.295
28.65
29.09
6275633
0
1
28.87842271
29.19231861
28.54957939
28.98803715
6275633
AAL
2020-01-03
28.27
28.29
27.34
27.65
14020066
0
1
28.17091132
28.19084122
27.24417105
27.55308447
14020066
AAL
2020-01-06
27.19
27.4901
27.08
27.32
6008794
0
1
27.09469681
27.39374494
26.98508237
27.22424115
6008794
AAL
2020-01-07
27.56
27.68
27.06
27.22
6197079
0
1
27.46339993
27.58297932
26.96515247
27.12459166
6197079
AAL
2020-01-08
27.1
28.09
27.07
27.84
10497296
0
1
27.00501227
27.99154224
26.97511742
27.74241851
10497296
AAL
2020-01-09
28.09
28.23
27.71
27.95
6870153
0
1
27.99154224
28.13105152
27.61287417
27.85203295
6870153
AAL
2020-01-10
27.96
27.99
27.25
27.32
8108419
0
1
27.8619979
27.89189274
27.15448651
27.22424115
8108419
AAL
2020-01-13
27.32
27.53
27.155
27.39
5841463
0
1
27.22424115
27.43350508
27.05981949
27.29399579
5841463
AAL
2020-01-14
27.91
28.4
27.35
27.53
8890948
0
1
27.81217315
28.30045566
27.254136
27.43350508
8890948
AAL
2020-01-15
27.45
28.06
27.32
27.58
5689926
0
1
27.35378549
27.96164739
27.22424115
27.48332983
5689926
AAP
2020-01-02
160.47
160.95
157.53
159.41
944940
0
1
149.4922233
149.9393864
146.7533492
148.5047381
944940
AAP
2020-01-03
158.03
159.63
157.25
159.42
566975
0
1
147.2191441
148.7096878
146.492504
148.514054
566975
AAP
2020-01-06
157.74
158.89
156.4325
156.8
879926
0
1
146.948983
148.0203114
145.7309293
146.0732886
879926
AAP
2020-01-07
156.9
157.4
152.409
154.94
1132189
0
1
146.1664476
146.6322425
141.9826775
144.3405314
1132189
AAP
2020-01-08
154.85
156.05
153.14
153.16
825747
0
1
144.2566884
145.3745962
142.6636697
142.6823015
825747
AAP
2020-01-09
153.78
153.98
150.25
152.87
1177403
0
1
143.2598872
143.4462052
139.971375
142.4121405
1177403
AAP
2020-01-10
153.18
153.5
148.61
149.01
1416121
0
1
142.7009333
142.9990421
138.4435677
138.8162036
1416121
AAP
2020-01-13
145.33
147
142.76
145.26
2138699
0
1
135.387953
136.943708
132.9937671
135.3227417
2138699
AAP
2020-01-14
145.59
149.08
144.34
148.9
1568390
0
1
135.6301663
138.8814149
134.465679
138.7137287
1568390
AAP
2020-01-15
149.58
151.49
147.82
150.21
1208806
0
1
139.3472098
141.1265465
137.7076117
139.9341115
1208806
Thank you Aniya for your reply. I tested your code and learning from it. The output is good, but it remains as slow as before. I also have tried Table.Buffer, also didn't help sofar. Please see attached a data sample of 3 tickers and 10 days of data (i am normally testing with 500 days of data which already makes it very very slow). Eventually I am using close to 10 years of data, and over 500 tickers.
ticker
date
open
high
low
close
volume
dividend
split
adj_open
adj_high
adj_low
adj_close
adj_volume
A
2020-01-02
85.9
86.35
85.2
85.95
1410486
0
1
84.08
84.52
83.39
84.13
1410486
A
2020-01-03
84.67
85.33
84.5
84.57
1118322
0
1
82.87
83.52
82.71
82.78
1118322
A
2020-01-06
84
84.82
83.6
84.82
1992491
0
1
82.22
83.02
81.83
83.02
1992491
A
2020-01-07
83.96
85.26
83.94
85.08
1722896
0
1
82.18
83.45
82.16
83.28
1722896
A
2020-01-08
85.96
86.47
85.2
85.92
1847585
0
1
84.14
84.64
83.39
84.1
1847585
A
2020-01-09
86.46
87.7
86.17
87.27
1912699
0
1
84.63
85.84
84.34
85.42
1912699
A
2020-01-10
87.72
88.24
87.32
87.59
1417028
0
1
85.86
86.36
85.47
85.73
1417028
A
2020-01-13
87.81
88.32
86.74
87.46
1630160
0
1
85.95
86.45
84.9
85.61
1630160
A
2020-01-14
87.27
88.21
86.7
87.99
1675243
0
1
85.42
86.34
84.86
86.12
1675243
A
2020-01-15
87.63
89.11
87.55
88.62
1630364
0
1
85.77
87.22
85.69
86.74
1630364
AAL
2020-01-02
28.98
29.3
28.65
29.09
6275633
0
1
28.88
29.19
28.55
28.99
6275633
AAL
2020-01-03
28.27
28.29
27.34
27.65
14020066
0
1
28.17
28.19
27.24
27.55
14020066
AAL
2020-01-06
27.19
27.49
27.08
27.32
6008794
0
1
27.09
27.39
26.99
27.22
6008794
AAL
2020-01-07
27.56
27.68
27.06
27.22
6197079
0
1
27.46
27.58
26.97
27.12
6197079
AAL
2020-01-08
27.1
28.09
27.07
27.84
10497296
0
1
27.01
27.99
26.98
27.74
10497296
AAL
2020-01-09
28.09
28.23
27.71
27.95
6870153
0
1
27.99
28.13
27.61
27.85
6870153
AAL
2020-01-10
27.96
27.99
27.25
27.32
8108419
0
1
27.86
27.89
27.15
27.22
8108419
AAL
2020-01-13
27.32
27.53
27.16
27.39
5841463
0
1
27.22
27.43
27.06
27.29
5841463
AAL
2020-01-14
27.91
28.4
27.35
27.53
8890948
0
1
27.81
28.3
27.25
27.43
8890948
AAL
2020-01-15
27.45
28.06
27.32
27.58
5689926
0
1
27.35
27.96
27.22
27.48
5689926
AAP
2020-01-02
160.47
160.95
157.53
159.41
944940
0
1
149.49
149.94
146.75
148.5
944940
AAP
2020-01-03
158.03
159.63
157.25
159.42
566975
0
1
147.22
148.71
146.49
148.51
566975
AAP
2020-01-06
157.74
158.89
156.43
156.8
879926
0
1
146.95
148.02
145.73
146.07
879926
AAP
2020-01-07
156.9
157.4
152.41
154.94
1132189
0
1
146.17
146.63
141.98
144.34
1132189
AAP
2020-01-08
154.85
156.05
153.14
153.16
825747
0
1
144.26
145.37
142.66
142.68
825747
AAP
2020-01-09
153.78
153.98
150.25
152.87
1177403
0
1
143.26
143.45
139.97
142.41
1177403
AAP
2020-01-10
153.18
153.5
148.61
149.01
1416121
0
1
142.7
143
138.44
138.82
1416121
AAP
2020-01-13
145.33
147
142.76
145.26
2138699
0
1
135.39
136.94
132.99
135.32
2138699
AAP
2020-01-14
145.59
149.08
144.34
148.9
1568390
0
1
135.63
138.88
134.47
138.71
1568390
AAP
2020-01-15
149.58
151.49
147.82
150.21
1208806
0
1
139.35
141.13
137.71
139.93
1208806
Hi , Jan-Jaap72
Thanks for your quick response!
According to your description, your current loading speed is still very slow. I'm so sorry, I've tried my best to help you reduce the steps in Power Query. You can try dropping unneeded columns before calculating to see if that improves performance.
And also you can try to use the dax to replace the M code .
Column 2 = RANKX( FILTER('Query1','Query1'[ticker]=EARLIER(Query1[ticker])) , CALCULATE( MAX('Query1'[date]),ALLEXCEPT('Query1','Query1'[date]) ) , [date],ASC,Dense)Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
Check if a DAX measure runs faster.
