Forum Discussion
average without Zero - Power querry
Hello please help me.
I want caverage like this. i dont want considered number 0.
thanks
Try this Daniff
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}), #"Added Average" = Table.AddColumn( #"Changed Type", "Average", each let varList = List.Select(Record.ToList(_), each _ > 0), varAverage = List.Sum(varList) / List.Count(varList) in if varAverage = null then 0 else varAverage ) in #"Added Average"It returns this:
Here is what it does:- It converts the current record to a list, so 0,1 for the first record, 2,9 for the second.
- It then keeps only the values in the list > 1. So the first list becomes 1, the second remains 2,9.
- It then divides the sum of the list by the count of items in the list for the average.
- In the case of a record having 0, 0, or all 0's, it will return 0 instead of null.
- This doesn't care how many columns you have. It just keeps the same logic over the entire record, so all columns.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
8 Replies
Try this Daniff
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}), #"Added Average" = Table.AddColumn( #"Changed Type", "Average", each let varList = List.Select(Record.ToList(_), each _ > 0), varAverage = List.Sum(varList) / List.Count(varList) in if varAverage = null then 0 else varAverage ) in #"Added Average"It returns this:
Here is what it does:- It converts the current record to a list, so 0,1 for the first record, 2,9 for the second.
- It then keeps only the values in the list > 1. So the first list becomes 1, the second remains 2,9.
- It then divides the sum of the list by the count of items in the list for the average.
- In the case of a record having 0, 0, or all 0's, it will return 0 instead of null.
- This doesn't care how many columns you have. It just keeps the same logic over the entire record, so all columns.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.Hello, many thanks, dont have any solution more easy? Thanks
Not that I can think of. This is just a relatively simple but compound formula in the Custom Formula box
I could do it without variables, but then I am repeading stuff many times and it start to look like a hideous Excel formula. This is the same thing, but impossible to read and uneditable.
if List.Sum(List.Select(Record.ToList(_), each _ > 0)) / List.Count(List.Select(Record.ToList(_), each _ > 0)) = null then 0 else List.Sum(List.Select(Record.ToList(_), each _ > 0)) / List.Count(List.Select(Record.ToList(_), each _ > 0))There is no List.Average(math here, IgnoreZeros) function though.
Daniff - Vijay_A_Verma reminded me about List.Average - I have never used it in practice, but it does make it simpler.
But the above will return null if all are 0. That may be desirable. If not, then you still want the if/then/else construct, so it only slightly simplifies the formula.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}), #"Added Average" = Table.AddColumn( #"Changed Type", "Average", each let varAverage = List.Average(List.Select(Record.ToList(_), each _ > 0)) in if varAverage = null then 0 else varAverage ) in #"Added Average"If null is ok when all are zero, the either Vijay_A_Verma 's function or just List.Average(List.Select(Record.ToList(_), each _ > 0)) will work.
Use below formula in a custom column to calculate average without 0
= List.Average(List.RemoveItems(Record.ToList(_),{0}))See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUTIAYjOlWJ1oMAuCYTxjOM8UyDIECRkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, V3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Average(List.RemoveItems(Record.ToList(_),{0})), type number) in #"Added Custom"Hello thanks for your reply. i want average only 2 selecion columns. i dont want average all columns. please help me.
Daniff you need to supply sample data. You only gave two columns so the solutions provided average those columns. If you have 30 columns, you have to tell us how you want to identify the two columns. An embedded Record.SelectFields() will work, so you can add that yourself, or you an provide some good sample data per the links below and tell me which columns you want averaged.
You can try this, but if this doesn't specifically answer the question... then sample data. This only averages V1 and V2.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}), #"Added Average" = Table.AddColumn( #"Changed Type", "Average", each let varAverage = List.Average( List.Select( Record.ToList( Record.SelectFields(_, {"V1", "V2"}) ), each _ > 0) ) in varAverage ?? 0 ) in #"Added Average"
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
