Power Query のクエリが評価されるとき、ファイルデータソースからどのようにデータを読み込んでいるのか？を理解しておいたほうがいいよという話。

エディタの使い方とかPower Query の関数の使い方とかだけを勉強してない？それだけで目的を満たすならそれでもかまわないし、なんだかよくわからないけど効果があるおクスリはその人にとってはきっと良いおクスリなんだろうねと思うけれども。

使い方とか記述方法だけではなく、実際にどのような動作をしているのか勉強したほうがよいのにね。と思うのです。

まとめ とか 思ったこと🙄

得るべき結果を得られることは大前提としてですね、クエリの評価パフォーマンスについてよーく考えてほしいものだ。考えるべき/調べるべき/知るべきところがたくさんあるわけさ。それ、Power Query でやる必要ある？ということもありますからね。

ステップが多いのでパフォーマンスがよくない
と、思ってしまうのは仕方がない。だけど、調整してステップが多くなっても結果的にパフォーマンスがよくなることが思いのほかあるし、ストリーミング処理を継続できるステップが多くなってもパフォーマンスが著しく低下することはほとんどない。

理由がわからないしパフォーマンスがよくない
と、いうのはとても問題だ。根本的には、考えたロジックを込めたステップに問題があることが多いのだけど、どういうことが起きているのかよく調査するべきだ。ボタンポチポチだけなら、できるだけ早いステップで行方向/列方向の絞り込みを終え、それから重たい処理をするという心がけでパフォーマンス低下は起きにくいはずなのにだ。なんかよからぬことしてない？

パフォーマンスが低下する理由
にはいくつかあるのだけど、このポストでは過剰で必要以上にファイル読込が発生することがあるよ。ということ。取り上げたItem access 以外でも起きることだから、よく観察してよね。

ファイルの読み込みが複数回行われることは通常の範囲でも発生している。
多くの場合でそれに気づかないだけで、気にする必要もない。だって必要な動作だからね。Power Query の特徴/特性/戦略を生かせないしょっぱいステップでクエリを構成したときには容赦ないファイル読込が発生していてパフォーマンス超低下するっていうこと。クエリの性能を検証してますか？

PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #8 で話したこと

先般の虎の穴でお話ししたのは Item access。なぜかというと、如実に影響があり問題がわかりやすいから。

Item access とは

{"a","b","c"}{0}                        // "a" 
{1, [A=2], 3}{1}                        // [A=2] 
{true, false}{2}                        // error 
{"a","b","c"}{0}?                       // "a" 
{1, [A=2], 3}{1}?                       // [A=2] 
{true, false}{2}?                       // null 
#table({"A","B"},{{0,1},{2,1}}){0}      // [A=0,B=1] 
#table({"A","B"},{{0,1},{2,1}}){[A=2]}  // [A=2,B=1]  
#table({"A","B"},{{0,1},{2,1}}){[B=3]}  // error 
#table({"A","B"},{{0,1},{2,1}}){[B=1]}  // error

まぁ書いてある通りなわけ。

// SourceList
{ 1 .. 2147483647 }

// Query1
SourceList{0}

// Query2
SourceList{2147483646}

行数が多くても問題なく結果を得られる。が、うかつにロジックに組み込むのは注意が必要だ。
というか、そうそう使うことないオペレータではあるし、利用した時のサイドエフェクトまで把握していないのはとてもよくない。

実際に Item access 使ってみる

データソースとして使われることが多いようだし、いわゆる フラットファイルデータベースでやらかしてるケースが多いように見受けられるので。

• CSV ファイル
• Excel (*.xlsx) のテーブル

を例として取り上げる。CSV ファイルといってもフォーマットを限定するものではなく、改行文字などを区切りとしてテーブルでいうところの行を指し、区切り記号で各フィールドに分割できるプレーンテキストを用いたフォーマットということだ。固定長フォーマットではないから、ファイルの先頭から読み込む必要がある。必要なデータ(行)がテーブルの先頭部分に存在しているのであれば、ファイル終端まで読み込む必要がないということでもある。

Excel ワークシート上のテーブルについても、Power Query の中の人が見たとき CSV と同様な扱いをしていて、このあたりの検証は Process monitor とか使うなり、実データを触れているとわかりそうなものだ。

起こる問題 - なんか遅くね？

Item access を使って、ボタンポチポチではできないことができそうだねと思って使ってみると、なんか遅くね？って。

CSV もしくは Excel のテーブル、できるだけ多くの行を用意してもらって、"SourceTable" というクエリ名で読み込んだ(table)としましょう。このとき、行(record)を参照する Item access operator( "{ }" )を使用する。

テーブルの最初の行

SouceTable{0}

100万行目

SouceTable{999999}

結果を得るまでのパフォーマンスが違うよね。理由は簡単だ。

• 最初の方の行を得るにはファイルの先頭部分だけを読み込む
• 最後の方の行を得るにはファイルの先頭から後方まで読み込む

フラットファイルデータベースから読み込むテーブル(SouceTable)で、

• SouceTable{n} はファイルの先頭から n 行目を認識するまで読み込む
• SouceTable{ [ColumName = n] } はファイルの先頭からすべてを読み込み、結果が1行であることも評価する

最近のシステムはスペックがよいから、もう少し目立つようにすると、

// Query3
let
    Source = Table.FromColumns( { { 1 .. 5 } } ),
    AddedItemAccess = Table.AddColumn(
        Source,
        "Item access",
        each SourceTable{0}[Column1]
    )
in
    AddedItemAccess

// Query4
let
    Source = Table.FromColumns( { { 1 .. 5 } } ),
    AddedItemAccess = Table.AddColumn(
        Source,
        "Item access",
        each SourceTable{999999}[Column1]
    )
in
    AddedItemAccess

先頭行を得るためにはデータソースのファイル先頭部分だけ読み込むだけで済むから割と処理が早く済む。最後の行に近づくほど遅くなるのは仕方がないのだけど、ファイル読み込みを1度だけにすることでパフォーマンス改善は可能。Table.Buffer を選択肢にしませんよ。だって100万行で済むかどうかわからないから。

別ケースの問題 - 終わらないクエリ

Item access の使いどころというか、データソースからどのように読み込まれているのかを理解していないため、パフォーマンスが著しく低下するケース。直前の行の値を列として追加したいっていう内容。なぜ Power Query でやりたかったのかがわかりかねるけれども。お仕事なので対応してますけどね。
かいつまむと、

Column1
い
ろ
は
に
ほ
へ
と
...
...

このテーブルを

Column1	Column2
い
ろ	い
は	ろ
に	は
ほ	に
へ	ほ
と	へ
...	と
...	...

と、したいのだと。

問題が発生するクエリ

let
    Source = SourceTable,
    AddedIndex = Table.AddIndexColumn( Source, "Index", 0, 1, Int64.Type ),
    AddedColumn2 = Table.AddColumn(
        AddedIndex,
        "Column2",
        each Source[Column1]{ [Index] - 1 },
        Text.Type
    ),
    ReplacedErrors = Table.ReplaceErrorValues(
        AddedColumn2,
        { "Column2", null }
    ),
    RemovedOtherColumns = Table.SelectColumns(
        ReplacedErrors,
        { "Column1", "Column2" }
    )
in
    RemovedOtherColumns

気持ちはわかる、わかるよ。でも残念。
きっと、このクエリのパフォーマンスの悪さに驚いたでしょう。データソースであるファイルを先頭より順次読み込む手段しかないのだから、例えば 100行のソーステーブルだった場合、ファイルの読み込みが少なくとも 100 回発生し、5,050行分の読み込んでいるのだ。

ちょっと珍しいケースになるんだけど、パフォーマンスが低下する原因のほぼすべてがファイルの読み込みに依存するっていうことなんだよね。まぁ、面白いと思ってるのは私だけだね。

ファイル読込を繰り返すことを止めるためにTable.Buffer を使ってみては？と考える人がいるかもしれない。でもさ、Power Query のプロセスが効果的に利用できるメモリ量を超えない保証がない。そもそも、メモリを大量に消費するよりはローカルファイルを複数回読み込んだ方が得策となることが多いゆえのPower Query の動作。なので根本的な問題から解決すべきかなと。うかつに Item accessを使ったことが問題ということ。

別の解決案としてテーブルのマージでなんとかするパターンもありそう。Table.Buffer よりは効果的な可能性はある。とはいえ、多くの行をあらかじめ読み込む必要があるので、ここでは選択することはない。

ぼくのかんがえたさいつよくえり

let
    Source = SourceTable,
    ColumnsToTable = Table.FromColumns(
        {
            Source[Column1] & { Number.NegativeInfinity },
            { null } & Source[Column1]
        },
        type table [ Column1 = Text.Type, Column2 = Text.Type ]
    ),
    KeptFirstRows = Table.FirstN(
        ColumnsToTable,
        each [Column1] <> Number.NegativeInfinity
    )
in
    KeptFirstRows

特に解説はしません。次回の PBIJP Power Query 秘密特訓「虎の穴」参加して、わかったこと/わかりそうなこと/わからなかったことなど発表してください。そうすれば誰かが教えてくれるはず。

その他

• Power BI 勉強会 - connpass
• Power BI Desktop を使い始めるには
• Excel で Power Query を使い始めるには