Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
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 😊
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
You can sovle this using group by and then combine its result by your data, see the attached file.
Oh thank you so much! I will try with the pivot table. Agree, I would not be confident with writing code
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
Hi @lawps , Try these please
Group by Student: Group the data by StudentID.
Calculate Average: Compute the average Grade for each student.
StudentID: Same student.
Module: "Average".
Grade: Average value.
I'm wanting to do it using power query editor - is this Power BI? This:
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!
Can you share en Execle file with no sensitive data pls?
Yes, thanks - here you go! Names / IDs are not real. It won't let me upload the file, so used WeTransfer
Something like this could work?
Oh yes, thanks. How did you do this? Is this a pivot table would this work with the original data which has over 3500 entries?
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 😊
Thank you, that is helpful.
Hi Paula, you want to do it in Excel or in Power BI?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |