powerquery filter TopN values for each year-CodePudding

i have a data set for each country and each year.

question : i want to filter Gross Amount topN for each year with PowerQuery.

i can get a result with this code only for one year but i need all years' data with top10(N) in one list.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Export Country", type text}, {"Gross Export", Int64.Type}, {"Share", type number}, {"Year", Int64.Type}, {"Imp/Exp", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Year", Order.Ascending}, {"Gross Export", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",10) 
in #"Kept First Rows"

Workaround : I created lists for each year separately and then merge them but it is a long shoot. Expected result in the sheet "Export_Top10"

Thank you for your help.

CodePudding user response：

Below is an example of grouping on [Year], and then within each group, sorting on [Amount] and adding an index. Then you expand the data and filter the index for the top X [Amount] numbers you are looking for. In the code I pick up the top 10, in the image, the top 3

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Amount", Order.Descending}}),"Index",1,1), type table}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Amount", "Index"}, {"Amount", "Index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Index] < 11))
in  #"Filtered Rows"

Sample before/after picking top 3:

CodePudding user response：

with the help of @horseyride i figured out the solution

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Export Country", type text}, {"Gross Export", Int64.Type}, {"Share", type number}, {"Year", Int64.Type}, {"Imp/Exp", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Gross Export", Order.Descending}}),"Index",1,1), type table}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Export Country", "Gross Export", "Share", "Imp/Exp", "Index"}, {"Export Country", "Gross Export", "Share", "Imp/Exp", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{{"Gross Export", type number}, {"Share", Percentage.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Index] <= 10),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
#"Removed Columns"