はじめに
「Power Queryの処理が遅い(重い)」は、Power Queryを触り始めた方が感じるであろう、あるあるな悩みではないだろうか。
また、そんな初心者あるあるな悩みに対する解決策が１つにまとまったwebページは、現状ではほとんど見当たらない。
そこで、本記事ではその悩みに対する基本的な解決策について、可能な限り網羅的に例示する。
前提条件
以下のようなケースを前提とする。
- 従来のExcel作業に慣れている方が作業する
- データソースはcsvやexcelになることが多く、その他のコネクタを選択する機会が少ない
※以上のようなケースを前提とすることから、表現の厳密性を意図的に避けていることがある。
したがって、厳密な理解が必要な際には、公式ドキュメントを参照いただきたい。
総論
Power Queryは、従来のExcel作業イメージにとらわれると失敗することがある。
必要に応じてアンラーニングしなければならない。
その理由の１つとして、Power Queryとは、Power BI・Power Pivot(+Power Apps)に適切な形式のデータを渡すためのツールである。
つまり、Power Query単体ですべての作業を完結しようとする発想は、本来の仕様に沿った使用法ではない。
この点について、以下記事にて詳細に解説されている。
関係する記載の抜粋
※実務上は、Power Query単体ですべての作業を完結しても便益を得られる場合が一定ある
チェックすべきこと
データソースのファイル形式は最良か？
csvをデータソースにすると、excelに比べて処理速度が速くなる可能性が高い。
csv・excelしか選択肢がないのであれば、なるべくcsvを選択することを推奨する。
不要なマージをしていないか？
「クエリのマージ」は負荷が大きい処理になることが多いため、これにより速度が著しく下がるケースが散見される。
従来のExcel作業に慣れているが故に、Vlookup・Xlookup的感覚でついつい使ってしまうと思われる。
「クエリのマージ」を使用していて処理が遅くなっている場合には、以下記載について上から順に検討しよう。
そもそも本当に必要か？
テーブルを複数に分けたほうが適切ではないか？
従来のExcel作業に慣れていると、なんでも１つのテーブルにまとめようとする癖が身につくと思われる。
しかしながら、この考え方はPower Query利用時には捨てなければいけない。
なぜなら、Power BI・Power Pivot活用の大前提となる「スタースキーマ」と対立する考え方だからである。
スタースキーマを理解したうえで、テーブルを複数に分けたほうが適切なケースであると判断できた場合には、
「クエリのマージ」をやめて、Power BI・Power Pivot上でリレーションシップを作成しよう。
フィルターのみを目的としてマージしているか？
フィルターするのにマージ先のデータが必要なことを理由として、「クエリのマージ」をするケースが散見される。
しかしながら、このようなケースにおいては、List.Contains関数の利用により、「クエリのマージ」の回避が可能である。
具体的には、「フィルター対象のID一覧をリストに変換→List.contains関数でフィルター」といったイメージである。
本当に必要な場合は、ひと工夫しよう
Table.Addkey関数を利用することで、処理を高速化できることがある。
不要な行のソート(並び替え)をしていないか？
行のソートも、従来のExcel作業では頻出する操作であるため、安易に使ってしまいがちである。
しかしながら、Power Queryにおいてソートが本当に必要なケースはそう多くない。
また、ソートも「クエリのマージ」と同様に、処理速度を下げる要因になり得る。
よって、必要性がない場合は利用を避けるべきである。
不要な行・列を全て削除できているか？
従来のExcel作業には、「不要そうなデータも念のため消さない」という考え方がある。
主な理由として、以下が挙げられる。
- 一度削除したデータは元に戻せない
- 必要のないデータはグループ化で非表示すれば良い
この考え方は、Power Query利用時には捨てる必要がある。
なぜなら、不要な行・列を残すことが、処理速度を大きく下げる要因になり得るからである。
また、従来のExcel作業と違い、Power Queryでは一度消した行・列を復元することが容易である。
以上より、Power Queryでは、不要な(または不要と思われる)行・列を全て削除することが強く推奨される。
負荷が大きいオペレーションがステップの序盤に入っていないか？
「負荷が小さいオペレーションをステップ前半に配置して、負荷が大きいオペレーションをステップ後半に配置する」ことで、処理速度を向上させることが可能である。
具体例として、フィルターや列の削除などをステップ前半に配置して、型の変換をステップ後半に配置することで、処理速度向上が期待できる。
全部チェックしても遅い場合どうすべきか
データソースの行数に起因する可能性が高い。
このような場合には、クエリの作成作業時に限り、「最初の行を保持する」によって行数を制限することで、作成作業中の処理速度が向上する。
※作成作業完了後に行数制限を解除することになるため、あくまでも作業中のみ効果を発揮する策である。