cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Keep top N row for each "category"

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

1 REPLY 1
Highlighted
Helper I
Helper I

Re: Keep top N row for each "category"

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors