Power Query でテーブルを集計したいが遅かった。それには理由がある。だから、できることを考える。

集計する前やその後に必要な処理があったりするわけで、Power Query で集計処理をしなければならないこともあるいうことだ。なら、可能な限り集計処理も素早く終えるような工夫が必要だ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/fc2240b0-bc79-2668-340a-fbdcef7af02b.png)
Excel ワークシートに テーブル:Sales (約 1,000,000 行 / 4列) がある。わずか1,000,000 行。大事なことなので2回。
で、Sales には 社員ID列が含まれているので、別途用意したテーブルとマージし社員IDごとに数量列の合計を得たいとする。

```text
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 と行数に比例した時間を要していた。これは効率的な集計がされてないなと。おそらく、

```text
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 よりかなり改善である。

```text:キーを割り当てる&#x20;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
```

```text:重複を削除する&#x20;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 で結果が得られる。

```text
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 でクエリの評価は完了する。

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

https://docs.microsoft.com/en-us/powerquery-m/table-group

> 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 を使用した集計済みの結果を要求するので結構使えるのです。


# 思ったこと🙄
マージはよく使われる処理だけど、クエリフォールディングが期待できないデータソースであるとき、とてもコストが高い処理なんですよ。
# その他
https://powerbi.connpass.com/

http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981

http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)