Forum Discussion
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 that a new line is inserted under each set of the same student, like this:
Is this possible? Many thanks! Paula
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 😊
16 Replies
- Akash_Varuna
Super User
Hi lawps , Try these please
Group by Student: Group the data by StudentID.
Calculate Average: Compute the average Grade for each student.
- Add Average Row: Create a new row with:
StudentID: Same student.
Module: "Average".
Grade: Average value.
- Combine Data: Append the average row to the original data for each student.
- Sort Data: Ensure grades appear first, followed by the average row for each student.
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance
ZhangKunIt 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.
Oh thank you so much! I will try with the pivot table. Agree, I would not be confident with writing code
- MattiaFratello
Hi Paula, you want to do it in Excel or in Power BI?
I'm wanting to do it using power query editor - is this Power BI? This:
- MattiaFratello
Hi Paula, yes, this looks like Power BI. Instead of doing it within Power Query I think it's best to import the data in Power BI and do it through the visualizations.
Hello and thanks for your reply - sorry I'm not sure what that means or how to do it!
Thanks for your reply. Apologies if I haven't got the right terminology. I've just completed Excel: Power Query (Get & Transform) workshop, so it's using these techniques
- Omid_Motamedise
You can sovle this using group by and then combine its result by your data, see the attached file.
- dufoq3
Community Champion
Hi 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
