
Active Member

November 24, 2020

Hi,
I've read
Grouped Running Totals in Power Query
https://www.myonlinetraininghu.....ower-query
I need exactly the same technic except that I need the running total only sums the last 25 days of data (in its own group).
Please consider that we have a Date column too.
I need the buffered technic to be exists for fast result. (I do not need Query Folding).
Thank you in advance,
Keivan


October 5, 2010

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
1 Guest(s)
