習熟度レベル問わず参加者全員が勉強の成果や経過を発表し、全員でディスカッションしたりと濃い勉強時間を月一で過ごしている。今回(12/14)は見学参加ができるから、みんなどんな勉強しているのかな？とか、自分はどこまでできたのか？とか推し量ることができるかも知れない。
時間があるかわからないから、話すかもしれないことを綴っておく。
- よくある間違い/勘違い
- 勉強すべき内容
トピックはアラカルト
Excel でもないし、データベースでもない
たとえば、Excel ワークシート上で処理した結果と Power Query で処理した結果は一致するとは限らない。どちらかが間違っているのではなく、それぞれ正しく結果を返している。詳しく知らないものから疑いたい気持ちはわかるし、ネガティブに捉えた残念なひともいたでしょう。それでも Excel ではない新たな道具(機能)なのだから、勉強しなければ理解できるわけないよね。理解しないなら使わない選択があるし、使わなければならないなら理解しなければならない、道具(機能)なのだから。きっと勉強した内容には、使い方や機能で成しえることはどのようなことなのかなど含まれていたはずだよね。
Microsoft が発行しているドキュメントはよく読んでおくべきだ。多くの人が Power Query を使うときに充分な情報量だし、今すぐすべてを理解できる量や内容ではない。ここにリファレンスがあるということくらいは憶えておいて損はない。そういえばと思い出して読み返せばよいのだ。
クエリが評価される動作と仕組み
適用するステップは処理順ではない
多くの場合でさほど重要ではないでしょう。クエリで適用する処理を考えるとき合理的なものであればよいはずだ。適用するステップの順で処理されるのではなく、常にマッシュアップエンジンはクエリを内容を解釈し、どのような処理をすべきかを決定している。
必要になるまで評価しない
これも多くの場合でさほど気にする必要はないはずだ。でも、知らないのはよくない。知らないまま、あれこれいじったことろでうまくいくはずがない。
遅延評価といわれるものなのだけど、式/クエリの評価は必要になるまで評価を始めない。だって、処理したものの結局使わなかったっていうのは超無駄でしょ。
エンジンが利用するシステムリソースをふんだんに提供すれば評価パフォーマンスは向上する。しかし、システムリソースには限界があるから、"必要のないことをできるだけしない"という戦略がもっとも効果的に評価パフォーマンスを上げている。
ストリーミング
たとえば テーブルを処理するクエリがあって列を3つ追加するとき、
let
Source = SourceTable,
AddedColumn1 = Table.AddColumn( Source, "Column1", each 0 ),
AddedColumn2 = Table.AddColumn( AddColumn1, "Column2", each 0 ),
AddedColumn3 = Table.AddColumn( AddColumn2, "Column3", each 0 )
in
AddedColumn3
列を追加するステップは3つになる。
テーブルに列を追加するステップ(Table.AddColum 関数)は行ごとで処理(ストリーミング)されることになっていて、ここでは全行への列の追加を3回繰り返す動作をしない。各行ごとの処理で列が3つ追加されていく。だってそうしないとメモリがいくらあっても足りなくなるでしょ。
Power Queryで扱うことができるテーブルの行数には制限がない。
クエリ フォールディング
データソースからすべての行を取得し、マッシュアップエンジンがすべてを行を処理するというのは効率的ではないことが多い。なので、データソースがサポートする処理は可能な限りデータソース側で処理を任せることができる仕組み。Power Query で記述されたクエリのすべて、もしくはその一部はデータソースのネイティブな言語に変換され、データソースのリソースでその処理を実行できるようになる。
クエリ フォールディングがサポートされる変換は、データソースとコネクタに依存するものだから、使用するデータソースの仕様をよく理解し、プロファイラなどでその動作を確認する必要がある。少なくともクエリ フォールディングでもっとも期待する処理は、行と列の選択とテーブルのマージ。テーブルのグループ化やソートも可能な限りデータソースで実行できるとよいはずだ。
Power Query の関数の動作のうち、部分的にデータソースに依頼していることもある。たとえば、Table.AddRankColumn 関数ではデータソースに対しソート済みのテーブルを依頼していて、Rank 列は マッシュアップエンジンが追加するといった動作もある。
データは保存されない
クエリが評価されるとき、その途中経過や結果を保存することはないし、処理途上でキャッシュが行われたとしても異なるクエリで再利用することもない。
SourceQuery を参照する Query (1) と Query (2) あるとき、SourceQuery の評価結果は再利用されることはない。なので、SourceQueryを参照しているクエリごとで評価されることになる。
処理を速くすることはできない
どちらかというと、遅くなる原因をできるだけ排除する方針で検討するとよい。
評価パフォーマンスのチューニングするなら、エンジンやデータソースの動作やソースデータの特徴をよく理解することが必須だ。とはいえ、できれば素早く完了させたい。なら、パフォーマンスが低下している理由についてよく調べ、適切に対処すべきだ。やみくもに手を入れても効果的な成果は得られない。
パフォーマンスが低下する原因
データソースのスペックやネットワーク、システムに依存するのは当然。だが、最も影響しているのふたつだ。
メモリを消費しすぎ
Excel の場合、特に影響が出やすい。なぜなら、Power Query のプロセスごとで利用できるメモリ容量が小さいから。小さいといっても 256 MiB なのだけど。使用量を超えた分はページングされるので処理が遅くなる。ストリーミングだけの処理であれば著しくメモリを消費することはないのだけど、不用意なテーブルのマージやソートを仕掛けるとあっという間に消費しつくしてしまう。Task manager でもその挙動は確認できるのだから、よーく観察するとよい。
"Out of memory" errors (or OOMs) can be caused by doing too many memory intensive operations against very large tables. For example, the following M code produces an OOM because it attempts to load a billion rows into memory at once.
Table.Buffer(Table.FromList({1..1000000000}, Splitter.SplitByNothing()))
To resolve out of memory errors, optimize memory intensive operations like sorts, joins, grouping, and distincts by ensuring they fold to the source, or by removing them altogether where possible. Sorts, for example, are often unnecessary.
メモリの消費過多によるパフォーマンス低下は、メモリ不足による処理失敗の手前なのでは？
ソートやマージにはできるだけ対策をして臨むとよいだろうし、Table.Buffer で解決できないのは当然のこと。
データソースにアクセスしすぎ
たとえば、テーブルへのアイテムアクセスには充分に注意すべき。table{<n>} や table{[<record>]}で行を選択できるけれども。たとえば、たった 1000行のテーブルを出力するために1000回ソースデータを参照していることがある。出来るからと言って検証なく使うべきではない。
SharePoint リストを扱うときで著名なケースもある。
https://learn.microsoft.com/en-us/power-query/optimize-expanding-table-columns
Process monitor や Fiddler などを使って原因を探るべきだ。
Power Query の役割
Power Query は、データ変換エンジンおよびデータ準備エンジンです。 Power Query には、ソースからデータを取得するためのグラフィカル インターフェイスと、変換を適用するための Power Query エディターが付属しています。 エンジンは多くの製品やサービスで利用できるため、データが保存される宛先は、Power Query が使用された場所によって異なります。 Power Query を使用すると、データの抽出、変換、および読み込み (ETL) 処理を実行できます。
大事なところは最後に記されている。データソースとアプリケーションの間を取り持つのであるから、データソースで処理した方がよいことやアプリケーションで処理すべきことまで Power Query でサポートさせようとするのはよくない考えだ。
その他