Power QueryのTable.Bufferはいつ使う？並べ替え後の重複削除で残る行がずれる・更新が遅いときの使い方

Power Queryで「更新日の新しい順に並べ替えてから、顧客IDで重複を削除する」と、顧客ごとに最新の行が残ることを期待します。ところが結果を見ると、古い行が残っていることがあります。こんなときに使われるのが Table.Buffer です。

この記事では次の内容を説明します。

• Table.Bufferで並べ替えの順序を固定する方法
• 並び順に頼らずに残す行を指定する方法
• 同じデータを何度も読むときの使いどころ
• 逆に遅くなる場面の見分け方

Excel本体やVBAでの重複削除は、別の記事で扱います。

並べ替え後の重複削除で残る行がずれる理由

Power Queryは、ステップを上から1つずつ完成させながら進むとは限りません。最終結果を出すために式をまとめて評価し、データソースによっては処理をデータベース側に任せます（クエリフォールディング）。

その過程で、並べ替えた順序が重複削除の時点まで保たれる保証はありません。重複の削除は「重複した行のうち1行を残す」処理で、直前に並べ替えた順の先頭を必ず残すとは限らないのです。

いつもずれるわけではありません。少ないデータで試すと期待どおりに見えても、データ量やデータソースが変わると結果が変わることがあります。動作に頼らない書き方にしておくのが安全です。

Table.Bufferで並び順を固定する

Table.Buffer は、指定したテーブルをその時点でメモリに読み込み、以降のステップに読み込み済みの内容を使わせる関数です。並べ替えた結果をバッファーしておけば、重複の削除はその順序で並んだテーブルを受け取ります。

画面操作から書き換える手順

1. 更新日の列を降順に並べ替える
2. 顧客IDの列を選び、「ホーム」タブの「行の削除」→「重複の削除」を選ぶ
3. 「適用したステップ」で重複の削除のステップを選び、数式バーの式を書き換える

書き換える前の式は次のような形です。

= Table.Distinct(#"並べ替えられた行", {"顧客ID"})

直前のステップを Table.Buffer( ) で囲みます。

= Table.Distinct(Table.Buffer(#"並べ替えられた行"), {"顧客ID"})

ステップ名は操作や環境によって違うことがあります。自分の式に書かれている名前をそのまま囲んでください。数式バーが表示されていないときは、「表示」タブで「数式バー」にチェックを入れます。

クエリ全体の例

let
    ソース = Excel.CurrentWorkbook(){[Name="顧客履歴"]}[Content],
    型設定 = Table.TransformColumnTypes(ソース, {{"顧客ID", type text}, {"更新日", type date}}),
    並べ替え = Table.Sort(型設定, {{"更新日", Order.Descending}}),
    固定 = Table.Buffer(並べ替え),
    重複削除 = Table.Distinct(固定, {"顧客ID"})
in
    重複削除

同じ日付の行があるときは2つ目の並べ替えキーを足す

同じ顧客で更新日が同じ行が複数あると、どれが残るかは決まりません。更新時刻や連番の列があれば、2つ目の並べ替えキーに加えます。

並べ替え = Table.Sort(型設定, {{"更新日", Order.Descending}, {"連番", Order.Descending}}),

並び順に頼らない方法：グループ化で残す行を指定する

Table.Bufferを使う方法は短く書けますが、「並べ替えた順の先頭を残す」という前提の上に成り立っています。残す行の条件を式の中に直接書きたい場合は、グループ化を使います。

let
    ソース = Excel.CurrentWorkbook(){[Name="顧客履歴"]}[Content],
    型設定 = Table.TransformColumnTypes(ソース, {{"顧客ID", type text}, {"更新日", type date}}),
    グループ = Table.Group(型設定, {"顧客ID"}, {{"最新行", each Table.Max(_, "更新日"), type record}}),
    展開 = Table.ExpandRecordColumn(グループ, "最新行", {"顧客名", "更新日", "金額"})
in
    展開

Table.Max は、指定した列の値が最大の行を1行返します。顧客IDごとに「更新日が最大の行」を取り出すので、並び順の影響を受けません。

ただし、最大の日付が同じ行が複数ある場合は、どれが選ばれるかの問題が残ります。その可能性があるデータでは、比較に使う列を一意に決まるものにします。

画面操作でも作れます。「変換」タブの「グループ化」で、操作に「すべての行」を選んでから、上の式の形に書き換えます。

結果が正しいか確かめる

どちらの方法でも、意図した行が残ったかは確認が必要です。

1. 行数を確かめる：結果の行数が、顧客IDの重複しない値の数と一致するか
2. 抜き取りで確かめる：行が多い顧客をいくつか選び、元データの最新日と結果の日付が同じか
3. 全件で照合する
   • 別のクエリで、顧客IDごとに更新日の最大値をグループ化（操作：最大）で出す
   • そのクエリを結果のクエリと顧客IDでマージする
   • 2つの日付が等しいかを判定する列を追加する
   • 等しくない行だけに絞り込み、0行なら一致

重複が残る原因が、大文字・小文字の違いや前後の空白の場合は、Table.Bufferでは直りません。キーの整え方は、重複削除の記事を参照してください。

同じデータを何度も読むときの使いどころ

Power Queryは、あるステップが式の中で何度も参照されると、そのたびに評価し直すことがあります。次のような場面では、バッファーで読み込みを1回にまとめると速くなる場合があります。

1つのクエリの中で、同じステップを複数回使う

前のステップを2か所から参照する場合や、自分自身の別のステップとマージする場合です。参照されるステップをバッファーします。

行ごとに別の一覧を参照するカスタム列や絞り込み

各行で同じ一覧を照合する処理では、一覧が行ごとに評価されることがあります。一覧は List.Buffer で固定します。

対象 = List.Buffer(対象コード表[商品コード]),
絞り込み = Table.SelectRows(明細, each List.Contains(対象, [商品コード]))

同じ要求を何度も送ると遅い、または回数制限があるデータソース

Webやデータベースなどがこれに当たります。

なお、バッファーの効果は、そのクエリを評価している間だけです。クエリAの最後にTable.Bufferを入れても、Aを参照するクエリBではAが改めて評価されます。バッファーした内容はクエリ間で共有されません。

逆に遅くなる・効かない場面

速くなったかを比べる方法

Table.Bufferの効果はデータやデータソースによって違うため、入れる前と後で比べて判断します。

• 同じデータで、Bufferありとなしの更新時間を比べる
• 2回目以降はキャッシュの影響で速くなることがあるので、何回か更新して比べる
• 細かく調べたい場合は、Windows版のPower Queryエディターの「ツール」タブにあるクエリ診断で、ステップごとの処理を確認できる（表示はバージョンで違うことがある）

速くならなければ、Table.Bufferは外しておきます。不要なバッファーは、データが増えたときにメモリ不足の原因になります。

使い分けのまとめ