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.

     

Daniff's avatar
Daniff
Helper I
4 years ago

Hello thanks for your reply. i want average only 2 selecion columns. i dont want average all columns. please help me.

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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.