Hi,
I've read
Grouped Running Totals in Power Query
https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-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
Hi Keivan,
Please upload a sample Excel file.
Mynda
Here is my sample file.
I need Running Average (Volatility) for the last 25 calendar days (Date) in each group (Stock).
Fast buffered.
Thanks
Keivan
Hi Keivan,
File isn't attached. Please be sure to click the 'Start Upload' button after selecting your file.
Mynda
file
I wish I call the topic
Grouped Running Average for Last 25 Days (Buffered ) in Power Query
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 < List.Count(values),
each try
if grouping{} = grouping{ + 1} then
if [j] = 25 then [j = 1, GRT = values{+1} , GRA = GRT/j, i = + 1]
else [GRT = [GRT] + values{+1} , GRA = GRT/j, i = + 1, j = [j] + 1]
else [GRT = values{+1} , GRA = GRT/j, i = + 1, j = 1]
otherwise [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
Hi Phill,
Thank you very much.
Keivan
no worries.
