1

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
LAP
  • 53
  • 2
  • 11
  • In your comment to horseyride, in his answer below, you say your column name is volume, with a lower case v. Your code above has Volume, with an upper case V. – Marc Pincince Mar 25 '19 at 17:45

2 Answers2

1

We don't know what your columns are, but assuming you have [product], [date] and [volume] in Source, this would average the last 50 days of [volume] for the identical [product] based on each [date], and place in a new column

AvgAmountAdded = Table.AddColumn(Source, "AverageAmount", (i) => List.Average(Table.SelectRows(Source, each ([product] = i[product] and [date]<=i[date] and [date]>=Date.AddDays(i[date],-50)))[volume]), type number)
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Source have multiple columns, however, for this concern, I think these are only the information needed, [product], [date] and [volume]. Applied above code and result is "Table". If expanded it shows as an error. I am trying to understand the code and figure it out the error but with no success yet. – LAP Mar 25 '19 at 17:30
  • Just tested, and works perfectly as written with column name [product] [date] [volume] -- note capitalization of each column name If your version does not work then post full code you are using as your use of the word "expand" is odd since there is nothing to expand in my code – horseyride Mar 25 '19 at 20:39
  • I inserted custom column then pasted your code. I changed tried to "AverageAmount" to "Raw" (hope I am doing it right) Below are the details in Formula Bar: = Table.AddColumn(#"Removed Columns1", "Custom", each Table.AddColumn(Source, "Raw", (i) => List.Average(Table.SelectRows(Source, each ([Product] = i[Product] and [date]<=i[date] and [date]>=Date.AddDays(i[date],-50)))[Volume]), type number)) I searching how to paste screenshot to provide more details... – LAP Mar 25 '19 at 21:39
  • You are duplicating code. Do Home..advanced editor... locate the spot you need to add the code and paste mine in. You do not add a custom column then paste my code in because my code is already adding a custom column. If you want to do it your way then add custom column and paste just the part starting with (i)=> and continuing through [volume]) – horseyride Mar 26 '19 at 11:36
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

LAP
  • 53
  • 2
  • 11