Power Query (M language) 50 day moving Average

Question

I have a list of products and would like to get a 50 day simple moving average of its volume using Power Query (M).

The table is sorted by product name and date. I add a custom column and applied the code below.

if [date] >= #date(2018,1,29)
then List.Average(List.Range(Source[Volume],[Volume]-1,-50))
else ""

Since it is already sorted by date and name, an if statement was applied with a date as criteria/filter. However, an error occurs that says

'Volume' column not found in the table.

I expect to have an added column in the power query with volume 50 day moving average per product. the calculation to be done if date is greater than or equal Jan 29, 2018.

Answer 1

Finally! found a solution. First, apply Index by product see this post for further details Then index again without criteria (index all rows) Then, apply below code

= Table.AddColumn(#"Previous Step", "Volume SMA(50)", each if [Index_byProduct] >= 50 then List.Average(List.Range(#"Previous Step"[Volume], ([Index_All]-50),50)) else 0),

For large dataset, Table.Buffer function is recommended after index-expand step to improve PQ calculation speed