我有每個國家和每年的資料集。
問題：我想用 PowerQuery 過濾每年的總金額 topN。
我只能使用此代碼獲得一年的結果，但我需要一個串列中包含 top10(N) 的所有年份的資料。
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"
解決方法：我分別為每一年創建了串列，然后將它們合并，但這是一個漫長的程序。作業表“Export_Top10”中的預期結果
感謝您的幫助。
uj5u.com熱心網友回復：
這是另一種也涉及分組的方法。
但是，我沒有對整個子表進行排序并添加索引列，而是對總出口串列進行排序，并僅選擇總出口 >= 比第十高的那些行。
請注意，此方法將在出現平局時回傳所有行。因此，如果在給定年份有兩個國家的出口并列，您可能會回傳 11 行而不是 10 行。
let
Source = #"Table1 (3)",
//Group by year and extract top 10
#"Grouped Rows" = Table.Group(Source, {"Year"}, {
{"Top Ten", (t)=> Table.SelectRows(t, each [Gross Export]> List.Sort(t[Gross Export],Order.Descending){10})}}),
//remove year column since we will expand it in the correct order in next step
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Year"}),
//expand the top ten table
#"Expanded Top Ten" = Table.ExpandTableColumn(#"Removed Columns", "Top Ten",
{"Export Country", "Gross Export", "Share", "Year", "Imp/Exp"}, {"Export Country", "Gross Export", "Share", "Year", "Imp/Exp"})
in
#"Expanded Top Ten"
示例結果僅顯示前3 個
uj5u.com熱心網友回復：
在@horseyride 的幫助下，我找到了解決方案
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"
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/341102.html