PQ - Return header(s) of column(s) for maximum average

[sandy666]

MorningAfternoonEveningAvgHeader


9
4
2
8.75Morning


8
1
3


10
2
1


8
1
1


MorningAfternoonEveningAvgHeader


9
4
8
8.75Morning | Evening


8
1
8


10
2
11


8
1
8


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UNP = Table.UnpivotOtherColumns(Source, {}, "Header", "Value"),
GroupAvg = Table.Group(UNP, {"Header"}, {{"Avg", each List.Average([Value]), type number}}),
SortDsc = Table.Sort(GroupAvg,{{"Avg", Order.Descending}}),
Group = Table.Group(SortDsc, {"Avg"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Header", each [Count][Header]),
Extract = Table.TransformColumns(List, {"Header", each Text.Combine(List.Transform(_, Text.From), " | "), type text}),
Max = List.Max(Extract[Avg]),
FilterMax = Table.SelectRows(Extract, each [Avg] = Max)
in
FilterMax
tolerates duplicates, extra columns and rows