集計する前やその後に必要な処理があったりするわけで、Power Query で集計処理をしなければならないこともあるいうことだ。なら、可能な限り集計処理も素早く終えるような工夫が必要だ。

Excel ワークシートにテーブル:Sales (約 1,000,000 行 / 4列) がある。わずか1,000,000 行。大事なことなので2回。
で、Sales には社員ID列が含まれているので、別途用意したテーブルとマージし社員IDごとに数量列の合計を得たいとする。

let
    Source = Table.NestedJoin(
        Table1, {"社員ID"},
        Sales, {"社員ID"},
        "Sales", JoinKind.LeftOuter
    ),
    AggregatedSales = Table.AggregateTableColumn(
        Source,
        "Sales",
        {
            {"数量", List.Sum, "Sum of 数量"}
        }
    )
in
    AggregatedSales

おおむね 50 sec 程度で結果を得られた。遅いよね。

せっかくの Excel なのだからピボットテーブルでいいんじゃね？って話ではある。もしくは、Sales テーブルを参照する構造化参照 SUM( FILTER( Sales[数量], Sales[社員ID] = [@社員ID] ) ) で刹那で処理終わるよね。

まずは、なぜ遅いのかを探るとよいんだぜ

遅くなるのは仕方がないにしても、わずか 1,000,000 行(3回目)で遅くなっては困るのだ。なので、実際にどのような動作をしているのか見当つけるとよい。

例えば、仕訳けて集計したいとする社員IDをすべてではなく、1つづ増やしたときどうなるか。ここでは、社員ID が1 行の時、約 5 sec、2 行の時約 10 sec、3 行の時約 15 sec と行数に比例した時間を要していた。これは効率的な集計がされてないなと。おそらく、

let
    AddedSumOf数量 = Table.AddColumn(
        Table1,
        "Sum of 数量",
        each
            let
                Id = [社員ID],
                FilteredSales = Table.SelectRows(
                    Sales,
                    each [社員ID] = Id
                ),
                SumOfValue = List.Sum( FilteredSales[数量] )
            in
                SumOfValue
    )
in
    AddedSumOf数量

このクエリ同様の行ごと処理になってしまっているのだろうと。マージを示すステップを MashUp engine に課したとしてが妥当なものとして、行ごとの処理をしているのでしょう。
なぜ？だって、Sales テーブルの社員ID と Sales テーブルに関係なく社員IDと名付けた列を勝手に用意しただけだもの。
なので、テーブルの集計に使われるように情報を追加するとよいかなと。

できること

集計に使うキーを Mashup engine に伝える

集計用のテーブル各行がユニークであることを Mashup engine に伝えると、マージの内部処理が最適化され期待する集計が行われる。5 ~ 6 sec で結果が得られた。50 sec よりかなり改善である。

キーを割り当てる Table.AddKey

let
    Source = Table.NestedJoin(
        Table.AddKey( Table2, { "社員ID" }, true ),  // 👈ココ
        {"社員ID"},
        Sales, {"社員ID"},
        "Sales", JoinKind.LeftOuter
    ),
    AggregatedSales = Table.AggregateTableColumn(
        Source,
        "Sales",
        {
            {"数量", List.Sum, "Sum of 数量"}
        }
    )
in
    AggregatedSales

重複を削除する Table.Distinct

let
    Source = Table.NestedJoin(
        Table.Distinct( Table2 ), // 👈ココ
        {"社員ID"},
        Sales, {"社員ID"},
        "Sales", JoinKind.LeftOuter
    ),
    AggregatedSales = Table.AggregateTableColumn(
        Source,
        "Sales",
        {
            {"数量", List.Sum, "Sum of 数量"}
        }
    )
in
    AggregatedSales
// Table.Distinct でキーが設定される

グループ化で集計

そもそもマージで集計する必要はなかったのでは？これも、5 ~ 6 sec で結果が得られる。

let
    GroupedRows = Table.Group(
        Sales,
        {"社員ID"},
        {
            {"Sum of 数量", each List.Sum([数量]), Int64.Type}
        }
    )
in
    GroupedRows

集計される社員IDをフィルタすればよいし、場合によってはマージしてもよい。グループ化のあとにソートしても影響は少ないはずだし。

ソートを素早くできる Excel だからできること

もっと素早く結果が必要ならワークシート上で Sales テーブルにソートを仕掛けておけばよいのだ。
社員ID ごとで集計するから、社員ID が昇順もしくは降順であることを保証し、Table.Group 関数のロジックを変更する。これだと 2 ~ 3 sec でクエリの評価は完了する。

let
    GroupedRows = Table.Group(
        Sales, {"社員ID"},
        {
            {"Sum of 数量", each List.Sum([数量]), Int64.Type}
        },
        GroupKind.Local  // 👈ココ
    )
in
    GroupedRows

If the data is already sorted by the key columns, then a groupKind of GroupKind.Local can be provided. This may improve the performance of grouping in certain cases, since all the rows with a given set of key values are assumed to be contiguous.

ソート済みならパフォーマンスが向上する可能性があるって書いてあるよね。これに該当するということだ。

集計をサポートするデータソースでも都合がよい時がある

集計をサポートしクエリフォールディングによる効率的な処理が可能なデータソースであってもグループ化を使うことがある。
データソースに定義されたプライバシーレベルにより効率的な集計ができないことがあって、例えば、Sales テーブルが Azure SQL Database に配置されていて、異なるデータソースに集計のキーとなる社員IDを含むテーブルがある。このとき、マージで集計しようとすると Sales テーブル全行を取得することがある。すると、Mashup engine が集計処理を行うことになりパフォーマンスが超悪化する。せっかくAzure SQL Database 使ってるのにね。なので、Table.Goup 関数で集計済み結果を得てから処理をするめることがある。
Table.Group 関数は Azure SQL Database に対し GROUP BY を使用した集計済みの結果を要求するので結構使えるのです。

思ったこと🙄

マージはよく使われる処理だけど、クエリフォールディングが期待できないデータソースであるとき、とてもコストが高い処理なんですよ。