How to calculate averages using power query in excel

Question

I want to calculate the average of the grades for each student across all 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

Answer 1

Hi @Paula Sutcliffe

Please check whether following steps are helpful, there would be an extra column to get the average numbers:

• Copy and paste the original query to create a new one.
• Then select the Table1(2), click Home tab > Group By. The grouping by is set to: ID and Last Name. Choose "Average" for Operation and select "Final Grade Code" column.
• You will get following results.
• Find "Append Queries" in Home tab, click it and choose " Append Queries as New". Append these 2 queries.
• Sort ID and other columns to get the order that you need.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

No real need for three separate queries or adding a new column. You can do all in one go. For example, the M-code below takes the blue table ("Table1") to the green one.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

GroupAverage = Table.Group(Source, {"ID"}, {{"Grade", each List.Average([Grade]), type number}}),

AddLabel = Table.AddColumn(GroupAverage, "Custom", each [ID]&" Average"),

Remove = Table.RemoveColumns(AddLabel,{"ID"}),

Rename = Table.RenameColumns(Remove,{{"Custom", "ID"}}),

Append = Table.Combine({Rename, Source}),

Sort = Table.Sort(Append,{{"ID", Order.Ascending}}),

ReorderCols = Table.ReorderColumns(Sort,{"ID", "LastName", "Module", "Grade"}),

ChangedType = Table.TransformColumnTypes(ReorderCols,{{"Grade", Int64.Type}})

in

ChangedType