Keep top N row for each "category"

[LY18]

Hi,

I try to do in powerbi desktop a table manipulation without succes for now 2 days

I have data organise with a type, a date, a results and basically, i want top keep for each "type" the last 5 results (by date) (so keep 5 rows for each type) 

i need to do it in the query editor as i will need after that to do some more operations on it ... (not only showing the results in the viewer)

i sorted the table by date and try to remove duplicate by type but it only keep the 1st row of each type ... I need the top 5.... ;-(

does anyone know a way to do it ?

many thanks in advance if you can help 

regards

Yann

[jeremiah-ang]

Old thread, but just to add it for future reference. 

1. Sort & Buffer (To retain sort order)

2. Group by "category" - Include all rows

3. Custom column - Table.FirstN([allRows], N)

4. Delete the "all rows" column

5. Expand the Custom column

[LY18 · Accepted solution]

Ok i finally managed to do it

if anyone in interested : here is how i made it: 

1- sort data by ascending Type and descending by date

2- add an index column

3- duplicate the table

4- in the duplicate : Group by Type and calculate the max of the index

5- merge the info "max" with the original table

6- add a column with the formula if index>max-5 then 1 else 0

7- filter only value with 1 

==> you got only the Top 5 rows for each "type"

maybe there is an easiest way to do it but it works 😉