Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
lawps
Regular Visitor

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).

lawps_0-1738620083781.png

But I want to make it so that a new line is inserted under each set of the same student, like this:

lawps_1-1738620231987.png

Is this possible? Many thanks! Paula

2 ACCEPTED SOLUTIONS
ZhangKun
Super User
Super User

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.

View solution in original post

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:

MattiaFratello_0-1738740382321.png

 

If this helped you, you can mark my post as solution or give a thumb up 😊

View solution in original post

16 REPLIES 16
dufoq3
Super User
Super User

Hi @lawps, another solution:

 

Output

dufoq3_0-1738854205056.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Omid_Motamedise
Super User
Super User

You can sovle this using group by and then combine its result by your data, see the attached file.

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
ZhangKun
Super User
Super User

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.

Oh thank you so much! I will try with the pivot table. Agree, I would not be confident with writing code

lawps
Regular Visitor

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

Akash_Varuna
Community Champion
Community Champion

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
lawps
Regular Visitor

I'm wanting to do it using power query editor - is this Power BI? This: 

lawps_0-1738670396575.png

 

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

https://we.tl/t-FECe1xdXS3

Something like this could work?

 

MattiaFratello_0-1738682853785.png

 

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:

MattiaFratello_0-1738740382321.png

 

If this helped you, you can mark my post as solution or give a thumb up 😊

Thank you, that is helpful. 

MattiaFratello
Solution Supplier
Solution Supplier

Hi Paula, you want to do it in Excel or in Power BI?

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.