Forum Discussion

cdk319's avatar
cdk319
New Member
2 years ago
Solved

Top N for each ID in Power Query

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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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

     

     

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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