Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |