Take top n results from table in power query, where n is dynamic based on a an if function

Question

I want to use Power Query to extract by field(field is [Project]), then get the top 3 scoring rows from the master table for each project, but if there are more than 3 rows with a score of over 15, they should all be included. 3 rows must be extracted every time as minimum.

Essentially I'm trying to combine Keep Rows function with my formula of "=if(score>=15,1,0)"

Setting the query to records with score greater than 15 doesn't work for projects where the highest scores are, for example, 1, 7 and 15. This would only return 1 row, but we need 3 as a minimum.

Setting it to the top 3 scores only would omit rows in a table where the highest scores are 18, 19, 20

Is there a way to combine the two function to say "Choose the top 3 rows, but choose the top n rows if there are n rows with score >= 15

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2019-08-28T16:07:36

As far as I understand you try to do following (Alexis Olson proposed very same):

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    group = Table.Group(Source, {"Project"}, {"temp", each Table.SelectRows(Table.AddIndexColumn(Table.Sort(_, {"Score", 1}), "i", 1, 1), each [i]<=3 or [Score]>=15)}),
    expand = Table.ExpandTableColumn(group, "temp", {"Score"})
in
    expand

Or:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    group = Table.Group(Source, {"Project"}, {"temp", each [a = Table.Sort(_, {"Score", 1}), b = Table.FirstN(a, 3) & Table.SelectRows(Table.Skip(a,3), each [Score]>=15)][b]}),
    expand = Table.ExpandTableColumn(group, "temp", {"Score"})
in
    expand

Or:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    group = Table.Group(Source, {"Project"}, {"Score", each [a = List.Sort([Score], 1), b = List.FirstN(a,3)&List.Select(List.Skip(a,3), each _ >=15)][b]}),
    expand = Table.ExpandListColumn(group, "Score")
in
    expand

Note, if there are more columns in the table you want to keep, for first and second variants you may just add these columns to last step. For last variant you haven't such option and the code should be modified.

Take top n results from table in power query, where n is dynamic based on a an if function

2 Answers