PowerQueryのTable.Bufferを解説｜遅いクエリを速くする方法

Power Queryを使っていると、「処理が遅い」「同じデータを何度も読み込んでいる気がする」と感じたことはありませんか？

その原因のひとつが、Power Queryの遅延評価という仕組みです。

この記事では、その遅延評価による無駄な再計算を防ぎ、クエリを高速化するための関数 Table.Buffer をわかりやすく解説します。

遅延評価とは？

Power Queryは「必要になるまで実行しない」＝遅延評価という方法で処理がされます。

これには不要な処理をスキップしてメモリ効率が良いというメリットがあります。

しかし、必要と判断される度に処理が実行されるため、同じデータを複数回参照するようなクエリでは複数回処理が行われてしまうことがあります。

例えば、以下の式の場合にはAとBの処理で2回Sourceが使われています。

let
    File=Excel.Workbook(File.Contents("Data.xlsx"), null, true),
    Source = File{[Item="Data",Kind="Table"]}[Data],
    A = Table.SelectRows(Source, each [Category] = "A"),
    B = Table.SelectRows(Source, each [Category] = "B"),
    RowCount = #table({"Category", "Count"},{{"A", Table.RowCount(A)}, {"B", Table.RowCount(B)}})
in
    RowCount

このときPowerQueryでは「Aを求めるとき」と「Bを求めるとき」で2回同じデータを読み込むことになり

その分だけ処理が増えるので処理速度の低下につながります。

そんなときに活躍するのがTable.Bufferです！

ドノ

owerQueryは必要なときに処理を実行するので、2回必要ならば2回同じ処理を実行してしまいます

Table.Bufferとは？

Table.Buffer は処理結果をメモリ上にキャッシュ（保持）する関数です。

つまり「このテーブルはもう確定したデータだから、以降のステップでは再処理せずにそのまま使ってね」とPowerQuery側に指示しているイメージです。

構文は下記のように（）内にテーブルデータを入れます。指定したテーブルをメモリ上に保持します。

Table.Buffer(table)

基本的な使い方

具体例を用いて説明します。

下記のようなCategory列のAとBのそれぞれの行数をカウントする処理を考えます。

上記の処理を下記のような式で行ったとします。

let
    File=Excel.Workbook(File.Contents("Data.xlsx"), null, true),
    Source = File{[Item="Data",Kind="Table"]}[Data],
    A = Table.SelectRows(Source, each [Category] = "A"),
    B = Table.SelectRows(Source, each [Category] = "B"),
    RowCount = #table({"Category", "Count"},{{"A", Table.RowCount(A)}, {"B", Table.RowCount(B)}})
in
    RowCount

これは下記のような手順で処理をしています。

File：Excelファイルの読み込み
Source：Dataテーブルの読み込み
A：SourceテーブルをCategoryがAの行だけにフィルタ
B：SourceテーブルをCategoryがBの行だけにフィルタ
RowCount：AテーブルとBテーブルの行数カウント値を用いてテーブル化

この処理ではAとBで2回Sourceデータの読み込み処理を実行しています。

この状態では同じ処理を2回行うことで無駄に時間がかかってしまいます。

これに対して、以下のようにTable.Bufferを挟むと、Sourceの処理結果をメモリに保持することができます。

let
    File=Excel.Workbook(File.Contents("Data.xlsx"), null, true),
    Source = File{[Item="Data",Kind="Table"]}[Data],
    Buffered = Table.Buffer(Source),
    A = Table.SelectRows(Buffered, each [Category] = "A"),
    B = Table.SelectRows(Buffered, each [Category] = "B"),
    RowCount = #table({"Category", "Count"},{{"A", Table.RowCount(A)}, {"B", Table.RowCount(B)}})
in
    RowCount

これによりAとBでSourceの読み込み処理は行われず、2回行われていたSourceの処理が1回で済みので処理速度を向上させることができます！

使用手順

では次に実際にPowerQuery上でTable.Bufferを利用する手順を説明します。

上記のDataテーブルを利用した処理を例として用います。

STEP

元データの準備

まずは元データを準備します。これにTable.Buffer処理を追加していきます。

STEP

処理ステップの追加

Table.Buffer処理を入れたい箇所の一つ前のステップを右クリックして、「後にステップを挿入」を選択します。

STEP

Table.Buffer処理の追加

数式バーに式を入力します。

テーブル名の部分はそれぞれの状況に合わせて変更が必要です。ここではSourceとしています。

Table.Buffer(テーブル名)

以上で、Table.Bufferの適用は完了です。

これでSourceの処理は一度だけ実行されるようになり処理の高速化が可能となります。

効果があるケース・ないケース

Table.Bufferは使いようによっては処理速度を向上させることができますが、場合によってはメモリを圧迫して処理を遅くしてしまう場合もありますので注意してください。

効果があるケースとないケースを下記の通りです。

ケース	効果	理由
同じテーブルを複数回参照する	〇	再計算を防げる
外部データソースを何度も呼び出す	〇	クエリ回数を減らせる
単純な1回の処理	△	メモリ使用量が増えるだけで効果が薄い
大規模データ（数百万行）	△	メモリ不足による処理速度低下のリスクあり