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).

 

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!

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

     

     

1 Reply

  • 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