cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peterhui50
Helper II
Helper II

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

I have a table in PQ like this,

peterhui50_0-1642210013927.png

 

Then I grouped it using Group by and All Rows.

peterhui50_1-1642210043378.png

 

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 - 

peterhui50_2-1642210113227.png

 

I got an error

peterhui50_3-1642210150210.png

 

The error message is 

peterhui50_4-1642210167972.png

 

 

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.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors