Forum Discussion
Finding top 5s of multiple actors
Hello there everyone,
so I've joined some date sets/tables and am almost done with my assignment, I "only" need to determine the top 5 categories of the 5 actors who play the most roles. The latter part I got by grouping accordingly and using "keep rows". That won't work here with the top 5 categories of each actor, because now I need to cut out the in between instead of the entire lower part of the table (reference screenshot).
I am suppost to stick with power pivot and power query.
I'd appreciate suggestions and solutions.
Greetings Tim
If to imitate on such model
first you group by actor_id without aggregation
next sort each Data table by Qty (simplest by adding another column)
and keep Top5 in resulting table
Remove intermediate columns and expand latest one
10 Replies
Mmrtlm Difficult to judge on the basis of a screenshot alone. Can you upload your file?
With 365, besides
= LARGE(IF(actor=3,qty),{1,2,3,4,5})you could also have
= INDEX( SORT( FILTER(qty,actor=3) ,,-1), {1,2,3,4,5})- I'm on the most recent version of excel.
How would I implement this in power query?The formulas I posted are ordinary worksheet formulas which assume the data is already showing on the sheet. If you are in PowerQuery then similar steps would involve sorting the data, creating an index column, and filtering the data by the index.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"actor", type text}, {"qty", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"qty", Order.Descending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 6), Result = Table.RemoveColumns(#"Filtered Rows",{"Index"}) in Result
.png){kind=link}