Top N for each ID in Power Query

[cdk319]

Hello,

I need to return 3 records for each person in a table. I really don't care if it's Top 3 or Last 3 or random. Each ID number (person) can have 1 to unlimited records (probably more like 15).

Basic setup is:

ResourceID	OrderID	Date	Other info/fields
ABC-1	12301	1/1/24
ABC-1	12302	1/2/24
ABC-2	12303	1/3/24
ABC-3	12304	1/4/24
ABC-3	12305	1/5/24
ABC-3	12306	1/6/24
ABC-3	12307	1/7/24

So I want 3 OrderIDs for each ResourceID, or 2 or 1. I tried grouping.. I'm stuck. Help!

[ronrsnfld · Accepted solution]

Table.Group and then take the first three rows using a custom aggregation

let 
    Source = Table.FromColumns({
        {"ABC-1","ABC-1","ABC-2","ABC-3","ABC-3","ABC-3","ABC-3"},
        List.Numbers(12301,7),
        List.Dates(#date(2024,1,1),7,#duration(1,0,0,0))},
        type table[ResourceID=text, OrderID=Int64.Type, Date=date]),
    
    #"Grouped Rows" = Table.Group(Source, {"ResourceID"}, {
        {"3 Rows", each Table.FirstN(_,3), type table [ResourceID=text, OrderID=number, Date=date]}}),
    
    #"Expanded 3 Rows" = Table.ExpandTableColumn(#"Grouped Rows", "3 Rows", {"OrderID", "Date"})
in 
    #"Expanded 3 Rows"

Results