Forum Discussion

Daniff's avatar
Daniff
Helper I
4 years ago
Solved

average without Zero - Power querry

Hello please help me.   I want caverage like this. i dont want considered number 0.   thanks    
Need Help
  • edhans's avatar
    edhans
    4 years ago

    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:

    1. It converts the current record to a list, so 0,1 for the first record, 2,9 for the second.
    2. It then keeps only the values in the list > 1. So the first list becomes 1, the second remains 2,9.
    3. It then divides the sum of the list by the count of items in the list for the average.
    4. In the case of a record having 0, 0, or all 0's, it will return 0 instead of null.
    5. 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.

     

edhans's avatar
edhans
Community Champion
4 years ago

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.

edhans's avatar
edhans
Community Champion
4 years ago

And... actually I remembered that Power Query has a coalesce function, so

 

List.Average(List.Select(Record.ToList(_), each _ > 0)) ?? 0

So if the average is null, that formula will report 0.