Conditional Simple Moving Average

[Jan-Jaap72]

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" 

[Ashish_Mathur]

Hi,

Check if a DAX measure runs faster.

[v-yueyunzh-msft]

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

[Jan-Jaap72]

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

[v-yueyunzh-msft]

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

[Jan-Jaap72]

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