Finding top 5s of multiple actors

[Mmrtlm]

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

[#2]

@Mmrtlm 

=LARGE(IF($A$2:$A$28=$D$2,$B$2:$B$28),ROW(C1))

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.

[#3]

@Mmrtlm 

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

@Mmrtlm 

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

[#7]

@Mmrtlm 

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

[#8]

@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.

[#9]

@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

[#10]

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

[#11]

@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.

[#12]

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