Need help grouping and averaging

[EaglesTony]

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!

[Kmcdonald]

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.

[Vijay_A_Verma]

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"