I have a table in power query “MASTER_Global Game Table” which has multiple columns in it. I have added an additional column which I would like to provide an average of another column for all rows that have matching values in another column. So….
| event | score | average |
|---|---|---|
| ev1 | 33 | |
| ev1 | 40 | |
| ev1 | 23 | |
| ev2 | 90 | |
| ev2 | 80 | |
| ev2 | 34 | |
| ev3 | 76 | |
| ev3 | 86 |
In the example above I would like to have the average column show the average score for the event in that row.
The result for this example would mean that “32” would appear in each of the ev1 rows under average.
Right click the event column and use Group By
Then click on the arrow atop the data column and expand the score column into rows
You could try getting group averages first and joining with it:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
groupedByEvent = Table.Group(Source, {"event"}, {{"Avg", each List.Average([score]), type any}}),
mergedWithGrouped = Table.NestedJoin(Source, {"event"}, groupedByEvent, {"event"}, "EventAverage", JoinKind.Inner),
expandedEventAverage = Table.ExpandTableColumn(mergedWithGrouped, "EventAverage", {"Avg"}, {"Avg"})
in
expandedEventAverage