Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
3 years ago

Need help grouping and averaging

hi,

 

I have a table as follows(btw name is irrevelant I think in this example, so thinking of removing that column):

 

Project     Name       Point

ABC         Test1          30

ABC         Test2          10

ABC         Test3          10

ABC         Test4          20

ABC         Test5          30

DEF          Test1          20

GHI          Test1         10

GHI          Test2          20

 

I want in Powerquery M to group them by Project and get an average, so I have the following result:

 

(ABC would be 100/5 rows, DEF would be 20/1 Row, GHI would be 30/2

Project     Avg

ABC         20

DEF          20

GHI          15

 

Thanks!

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Use a simple Group By

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCkktLjEE0sYGSrE6yIJGQNoQXdAYm6AJkDZCFzRFmOni6oZkEVSlu4cnkqAhuqARVGUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Name = _t, Point = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Name", type text}, {"Point", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Avg", each List.Average([Point]), type nullable text}})
in
    #"Grouped Rows"
  • Kmcdonald's avatar
    Kmcdonald
    Helper III
    3 years ago

    Hi EaglesTony,

     

    I got the desired result by a simple group by and using an average aggregation.

     

    This is how I started:

     

    Then from the "Transform" tab I chose "Group by".

     

     

    Then I chose the criteria below.

     

     

    Then I got the final result below.