Power Query - Change function from rolling sum, to moving 30 day average

Hi  , 

Try the following code:

 

= (RTColumnName as text, MyTable as table, ValueColumn as text, optional WindowSize as number) as table =>
let
Window = if WindowSize = null then 30 else WindowSize,
BuffValues = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RowCount = List.Count( BuffValues ),

RollingTotals =
List.Generate (
() => [ RT = BuffValues{0}, RowIndex = 0 ],
each [RowIndex] < RowCount,
each [
RT = [RT]
+ BuffValues{[RowIndex] + 1}
- ( if [RowIndex] + 1 >= Window then BuffValues{[RowIndex] + 1 - Window} else 0 ),
RowIndex = [RowIndex] + 1
],
each [RT] ),

RollingAverages =
List.Transform(
List.Zip( { RollingTotals, List.Numbers( 0, RowCount ) } ),
each _{0} / List.Min( { Window, _{1} + 1 } ) ),

#"Combined Table + RT" =
Table.FromColumns(
Table.ToColumns( MyTable )
& { Value.ReplaceType( RollingAverages, type {number} ) } ,
Table.ColumnNames( MyTable ) & { RTColumnName } )
in
#"Combined Table + RT"

This is the main information it gave me:

 

---

Key changes from the original:

• Instead of accumulating RT forever, each step also subtracts the value that's falling out of the trailing 30-row window (BuffValues{[RowIndex] + 1 - Window} once the window is full).
• RollingAverages divides each rolling sum by Window — except for the first 29 rows, where it divides by the actual number of rows seen so far (List.Min({Window, RowIndex+1})) so you're not artificially deflating the average at the start of the table.
• Added an optional WindowSize parameter (defaults to 30) so you can reuse this for 7-day, 90-day, etc. without editing the function body.

  One assumption baked in: this is a row-based window (30 rows = 30 days), which only equals a calendar 30-day window if MyTable is already sorted by date with exactly one row per day and no gaps. If your data can have missing days or multiple rows per day, let me know — the date-aware version needs to filter by an actual date range ([Date] - #duration(29,0,0,0) to [Date]) rather than a fixed row count, which is a

  different (heavier) pattern since it can't use the same O(1) sliding-window trick.

 

 

Hi howe-ch-6541
Here’s another approach to your query above for creating a dynamic 30-day rolling average in Power Query that you may want to try.

(inputtable as table, windowsize as number) =>
let
Index = Table.TransformColumns ( Table.AddIndexColumn ( inputtable , "Index" , 0 , 1 ) , { "Index" , each let 
ListNumbers = List.Transform ( List.LastN ( { 0.._ } , windowsize ) , each Number.From ( inputtable [Numbers] { _ } ) ),
ListAverage = Number.Round ( List.Average ( ListNumbers ) , 3 ),
ListCount = List.Count ( ListNumbers ),
ListCondition = if ListCount < windowsize then null else ListAverage
in
ListCondition } )

Thanks,