calculating multiple averages by category and flattening a table

[yuvals907]

Dear forum,

i'm trying to transform a table like this:

student	subject	type	score
s-1	Math	homework	100
s-1	Math	quiz	90
s-1	Math	final exam	80
s-1	History	homework	80
s-1	History	quiz	70
s-1	History	final exam	60
s-2	...
s-2

into a flat table like this (1 line per student):

student	AVG Math	AVG History	AVG homework	AVG quiz	AVG final exams
s-1	90	70	90	80	70
s-2	...

i tried "group by" student and then got a nested table, and wasn't sure how to continue.

i do not want to add the new columns manually as the categories might change over time.

Thanks a lot!

[Vijay_A_Verma · Accepted solution]

Use below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKtY1VNJR8k0syQBSGfm5qeX5RdlApqGBgVKsDpp8YWlmFZCyxCKVlpmXmKOQWpGYC+RYICvwyCwuyS+qRDUehwqoBebYZVHsMIOpMcLqfktLTGmo6RammFIoRpsgG43V+SamWFWUpBaXoDsNh/PNkU0IyKgszkwuRrXDDLsKqB3m5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student = _t, subject = _t, #"type" = _t, score = _t]),
    Custom1 = Table.FromColumns({Source[student], List.Transform(Source[subject], each _ & " Avg"), List.Transform(Source[score], Number.From)}, {"subject","temp","score"}) & Table.FromColumns({Source[student], List.Transform(Source[type], each _ & " Avg"), List.Transform(Source[score], Number.From)}, {"subject","temp","score"}),
    #"Grouped Rows" = Table.Group(Custom1, {"subject", "temp"}, {{"Avg", each List.Average([score]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[temp]), "temp", "Avg")
in
    #"Pivoted Column"

[yuvals907]

Thank you so much Vijay,

It's great to see that the community is still stronger than google & ChatGPT together 😀