Grouped Running Totals for Last 25 Days (Buffered ) in Power Query|Power Query|Excel Forum|My Online Training Hub

Hi Keivan

See attached file for solution.

As I don't have access to your source file I copied the data in the sheet into another table and created a query against that. This is the GroupedRunningAvg query.

I also modified your sample query to use the same code so once you open it on your PC it should work.

On my PC it's calculating the avg for 50,000 rows in 4-5 seconds.

let

GRAList = (values as list, grouping as list) as list =>

let

TheList = List.Generate
(
()=> [ GRT = values{0}, GRA = values{0}, i = 0, j = 1 ],

each [i] < List.Count(values),

each try
if grouping{[i]} = grouping{[i] + 1} then

if [j] = 25 then [j = 1, GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1]

else [GRT = [GRT] + values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = [j] + 1]

else [GRT = values{[i]+1} , GRA = GRT/j, i = [i] + 1, j = 1]

otherwise [i = [i] + 1]
,
each [GRA]
)
in

TheList,

Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Stock", type text}, {"Volatility", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Stock", Order.Ascending}, {"Date", Order.Ascending}}),

BufferedValues = List.Buffer(#"Sorted Rows"[Volatility]),
BufferedGrouping = List.Buffer(#"Sorted Rows"[Stock]),

GroupedRunningAverage = Table.FromList(GRAList(BufferedValues, BufferedGrouping), Splitter.SplitByNothing(), {"GRA"}, null, ExtraValues.Error),
Columns = List.Combine({Table.ToColumns(#"Sorted Rows"),Table.ToColumns(GroupedRunningAverage)}),
#"Converted to Table" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Sorted Rows"),{"Running Avg"}}))

in
#"Converted to Table"

Regards

Phil

no worries.