Finding top 5s of multiple actors

Occasional Contributor

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

11 Replies




Can you imagine doing this with large formula within the spreadsheet? If you don't work with Office365 or 2021 enter formula with ctrl+shift+enter. With Power Query i can't help you unfortunately.


With 365, besides 

= LARGE(IF(actor=3,qty),{1,2,3,4,5})

you could also have

I'm on the most recent version of excel.
How would I implement this in power query?

@Mmrtlm Difficult to judge on the basis of a screenshot alone. Can you upload your file?

best response confirmed by Mmrtlm (Occasional Contributor)


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



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.


    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"})


@Peter Bartholomew 

Perhaps I misunderstood the question. My guess we need top 5 categories for each of actors, not top 5 categories for all selected actors.

@Sergei Baklan 

I have no idea how you came up with this.

But it worked perfectly! Thank you so much!


And the excel file helped alot

No, it was me that had read the requirement too superficially. I like the table.FirstN.

@Mmrtlm , you are welcome.


In general you may combine all transformations of grouped tables in one function instead of adding new columns for each transform, but that's cosmetic.

@Peter Bartholomew , without the file we always make some guesses. Never know which one is correct.