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.
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
Solved! Go to 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 😉
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
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 😉
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |