Power Query Column Average

[johankent30]

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 below

[OwenAuger · Accepted solution]

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

[anvikuttu]

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.

Let me know if you have any questions.

Thank you...

Vj