Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LY18
Helper I
Helper I

Keep top N row for each "category"

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

 

 

1 ACCEPTED SOLUTION
LY18
Helper I
Helper I

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 😉

View solution in original post

2 REPLIES 2
jeremiah-ang
New Member

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
Helper I
Helper I

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 😉

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.