cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Power Query Column Average

I am trying to calculate the average of a column in power query. The column does have some null values but I've tried replacing them and I still can't get List.Average() to work.. I am getting the error message that it can't convert the value to a list. Any thoughts on how to do this in M? Picture is attached belowerr.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Hi @johankent30 

I take it you want the same average value in every row of the new colum.

 

The code for the Added Custom step should be

= Table.AddColumn(#"Changed Type", "Custom", each List.Average(#"Changed Type"[CashFlow_Cust.Due_Days]))

Is that what you are seeing in the formula bar?

 

It's working in a test query I created similar to yours, with a mixture of numbers and nulls.

 

Regards,

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

Hi @johankent30 

I take it you want the same average value in every row of the new colum.

 

The code for the Added Custom step should be

= Table.AddColumn(#"Changed Type", "Custom", each List.Average(#"Changed Type"[CashFlow_Cust.Due_Days]))

Is that what you are seeing in the formula bar?

 

It's working in a test query I created similar to yours, with a mixture of numbers and nulls.

 

Regards,

Owen


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted

Hi,

I am a newbie to PowerQuery, the above solution is very good. However, I have a little more complex query on this.

Suppose I have Country column, Value column. Now I wanted to average the Value column not equal to 0, for each country in the country column.  Kindly see the below example.

anvikuttu_0-1600515234584.png

 

Let me know if you have any questions.

 

Thank you...

Vj

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors