Forum Discussion
Solved
How to calculate averages using power query in excel
Hello! I am new to Power Query! I want to calculate the average (mean) of the grades for each student across each of their modules. (This data is psuedonymised).  But I want to make it so tha...
It can be done, but I don't recommend you to do it now because it requires writing some code, and I believe writing code is not a good thing for a novice.
If it is just to solve your problem, the pivot table can do it.
It's a matrix in Power BI, you can find it in the visuals, and you can drag and drop what you want to visualize, please check below:
If this helped you, you can mark my post as solution or give a thumb up 😊
dufoq3
Community Champion
Community ChampionHi lawps, another solution:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZCtDsMwDITfJbggie380OGxwqogbAUrmPr+mm2SgDpFJ+u7O1m3bS4gpRLd4l6/dl6sEBEKa6huXwyOrDHPOXmbS3+yOcqdwM5n4UU5Uq0IfL+P8Tl/D7WZHffJxIpGUmtNWDokjJTFvH6P6zM0QzG5voXBzpNwsrkMQtnm0AclghClb+U929mGhuBnjtJ3NRy6fJg5dIdpRxWlp08zb7n/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"LAST NAME" = _t, #"module code" = _t, FINAL_GRADE_CODE = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"module code", Int64.Type}, {"FINAL_GRADE_CODE", type number}, {"ID", Int64.Type}}),
GroupedRows = Table.Group(ChangedType, {"ID"}, {{"T", each
Table.InsertRows(_,
Table.RowCount(_),
{ List.Accumulate(Table.ColumnNames(_), Table.First(_), (s,c)=> Record.TransformFields(s, {{c, (x)=> null}})) & [LAST NAME = "AVERAGE", FINAL_GRADE_CODE = List.Average([FINAL_GRADE_CODE])] }
), type table}}),
T = Table.Combine(GroupedRows[T])
in
T