Forum Discussion
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
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})
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 ResultPerhaps 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.
Peter
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 have no idea how you came up with this.
But it worked perfectly! Thank you so much!
And the excel file helped alot
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.
.png){kind=link}