SOLVED

# Finding top 5s of multiple actors

Occasional Contributor

# 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

11 Replies

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

How would I implement this in power query?

# Re: Finding top 5s of multiple actors

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

best response confirmed by Mmrtlm (Occasional Contributor)
Solution

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

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}}),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 6),
Result = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
Result``````

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

I have no idea how you came up with this.

But it worked perfectly! Thank you so much!

And the excel file helped alot

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

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

# Re: Finding top 5s of multiple actors

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