Forum Discussion
Solved
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 latt...
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
I'm on the most recent version of excel.
How would I implement this in power query?
How would I implement this in power query?
.png){kind=link}
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
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
No, it was me that had read the requirement too superficially. I like the table.FirstN.
PeterPeterBartholomew1 , without the file we always make some guesses. Never know which one is correct.