Custom Sorting in Power Query

Today, we are going to discuss about numerous methods in Power Query to do custom sorting.

Source data includes columns for ID, Group, and Score.

We need to sort the table in descending order according to the total scores for each group.

Method-1:

let

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

 Result = Table.Sort(Source, {each List.Sum(Table.SelectRows(Source, (f) => f[Group] = [Group])[Score]),1})

in

 Result

This is a straightforward approach with the help of Table.Sort function. But this is comparatively slower than next 2 methods.

Method-2 and Method-3 are suggested by Oleksandr Mynka.

Sortby Groupsum 3 Methods.xlsx

Download XLSX • 21KB

Method-2:

let

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

groups = List.Distinct(Source[Group]),

totals = List.Buffer(List.Transform(groups,(gr_name)=> List.Sum(Table.SelectRows(Source, (x)=>x[Group]=gr_name)[Score]))),

dic = Record.FromList(totals,groups),

Result = Table.Sort(Source , {{each Record.Field(dic,[Group]),1}, {"Score",1}})

in

Result

Method-3:

let

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

gr = Table.Group(Source, "Group", {"Score", each List.Sum([Score])}),

tbl = Table.Buffer(Table.AddIndexColumn(Table.Sort(gr,{"Score", 1}),"idx")[[Group],[idx]]),

join = Table.Join(Source,"Group",tbl,"Group",JoinKind.Inner),

srt = Table.Sort(join,{{"idx", Order.Ascending}}),

res = Table.RemoveColumns(srt,"idx")

in

res