【PowerQuery】大量データから特定の項目毎の金額上位○件のみ抽出する

（１） やりたいこと

下図例の通り、元データから特定の項目（勘定科目）毎に金額上位３件の明細を抽出します。今回は金額上位３件としましたが、絞り込む条件は金額下位〇件や金額〇〇円以上などある程度自由設定可能です。また、今回サンプルのレコード数は5万行としましたが、件数が増えても極力処理時間がかからないよう関数の使い方にも気を付けました。（もっと良い方法があるかもしれませんが）

（２）コード

詳細エディターのコードは以下の通りです。

元データからキーとなる勘定科目の抽出リストを一旦作成後、List.Accumulate関数で抽出リスト分の抽出処理を行い一つのテーブルとして出力しています。

let
    
    ソース = Excel.Workbook(File.Contents("C:\test\testData.XLSX"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

 

   

tableBuff = Table.Buffer(昇格されたヘッダー数),
    
    抽出リスト = List.Buffer( List.Sort( List.Distinct( tableBuff[勘定科目])
                             ,Order.Ascending)),
    

    抽出結果 = 
              List.Accumulate(
                 {1.. List.Count(抽出リスト)-1 },  
                       Table.FirstN(
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{0}
                                                            )
                                       ,{"金額",Order.Descending}
                                              )
                                        ,3),

                (x,y) => x &
                       Table.FirstN(
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{y}
                                                            )
                                       ,{"金額",Order.Descending}
                                              )
                                       ,3)

                                        )

in
    抽出結果

（３）適用したステップ毎の説明

元データのExcelファイルを取り込んで自動で一行目がヘッダーとして昇格されたところから簡単に説明します。（型の変更は省略）

前ステップ「昇格されたヘッダー数」テーブルに対してTable.Bufferを入れています。これで処理時間が若干短縮されます。

次に抽出リストを作ります。

前ステップ「tableBuff」の勘定科目列に対して、List.Distinct関数で重複を削除し、List.Sort関数で昇順に並び替え、最後にList.Buffer関数で囲っています。ここにList.Bufferを入れることで処理時間が大分短縮されます。

最後にList.Accumulate関数です。第一引数にList、第二引数にseed（初項）、第三引数にaccumulator（関数）を設定してList分の繰り返し処理を行います。

以下関数を説明します。（見にくくてすみません）

List.Accumulate(
                 {1.. List.Count(抽出リスト)-1 },   // ←Listは1から抽出リストの個数-1
                       Table.FirstN(　　　//←以下seed、tableBuffに対し抽出リストの
                             Table.Sort(　　//　1番目の勘定を降順Sortした結果の上位3明細
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{0}
                                                              )
                                       ,{"金額",Order.Descending}
                                               )
                                         ,3),

                (x,y) => x &                //←以下accumulator：seed(x)+繰り返し処理(y)の
                       Table.FirstN(　　//　結果を出力　
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{y}
                                                              )
                                       ,{"金額",Order.Descending}
                                               )
                                         ,3)

                             )

下図は抽出結果の画面です。

以下、最終行までスクロールした画面です。

以上となります。