How to calculate averages using power query in excel

[lawps]

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

[ZhangKun · Accepted 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.

[dufoq3]

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

[Omid_Motamedise]

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

[ZhangKun · Accepted 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.

[lawps]

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

[lawps]

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]

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]

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.

[lawps]

Hello and thanks for your reply - sorry I'm not sure what that means or how to do it!

[MattiaFratello]

Can you share en Execle file with no sensitive data pls?

[lawps]

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

[MattiaFratello]

Something like this could work?

[lawps]

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?

[MattiaFratello · Accepted solution]

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 😊

[lawps]

Thank you, that is helpful. 

[MattiaFratello]

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