List.Select with List.Average what am I doing wrong?

[peterhui50]

I have a table in PQ like this,

Then I grouped it using Group by and All Rows.

Then I just want to run List.Select to select all List items in the column [Sales] that are greater than the average Sales in that Group.

so I did a new column with - 

I got an error

The error message is 

I can't figure out why I would be getting this error. Any help is appreciated.

I know there are multiple ways around this, but I really want to learn to use List more.

[OwenAuger · Accepted solution]

Hi @peterhui50 ,

In the Custom Column dialog box, change the code to

let avg = List.Average([Data][Sales]) in List.Select([Data][Sales], each _ > avg )

The rough explanation is that, within List.Select, a field reference like [Data] is interpreted as _[Data] which is the [Data] field of a particular item in the list provided as the first argument of List.Select, rather than the data field of the original row. 

More precisely, the 3rd argument of Table.AddColumn (which you create within the dialog box) is a function taking a single argument, being the currently iterated row of the table. Within List.Select, the 2nd argument is a function taking a single argument, which is the currently iterated item in the list. The "inner function" provided to List.Select cannot directly refer to the argument of the "outer function", unless we assign it to a variable which can be passed to the "inner function".

My code above preserves the average by assigning it to avg, and it can then be referenced by the "inner function".

Regards,

Owen