Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
peterhui50
Helper III
Helper III

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!
Blog
Twitter
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!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.