Power BI 勉強会 #34 - Power Query の大事な話

このポストの内容ざっくりは、
チンすればお弁当は温まります。電子レンジを使うとき考えておいた方がよいのは、500 W で 2 分とか、お弁当がふたつならとかレシピ(手順や操作)ではなく、なぜチンするとお温まるのかということ。上手に使いこなすためにレシピを増やしていく。それはそれで構いませんが、原理を知っておくことが重要なのです。原理を知っていれば、適している使い方を新たに思いつくこともあるでしょう。少なくとも、やってはいけないことは確実かつ納得して理解できるはずなのです。

レシピをアレンジしたとしましょう。うまくいけばよい経験則になる可能性はあります。しかし、うまくいった / うまくいかなかった、それぞれの要因や原因がわからないままだということです。根拠の乏しい推測や思い込みのままでは、アレンジではなく要らぬことをしただけ。

Power Query を使いたいと思った多くの人には、手続き型から宣言型のパラダイムシフトへの順応が重要になります。どのような動作をするのか(原理)を理解しないままレシピだけ勉強したところで大した成果が残せないのではないでしょうか。

たとえば、Excel で培った知識や知恵は Power Query でもきっと役立つでしょう。しかし、Excel の常識を Power Query に持ち込んではいけないのです。重要なことは、持ち込んではいけない理由を知ることです。Power Query の関数をたくさん憶えれば、いまよりもっと使いこなせるということもないでしょう。

このポストは、私と Gemini 3 で議論を重ねた結果をまとめたものです。
AI に Power Query に関する問いかけをしてもトンチンカンな提案や回答をしてくることばかりで、到底納得できなかったことが原点です。
Gemini 3 には、私が知っていることとその根拠になり得るログやトレース情報などを提示、一次情報のみの参照を徹底しました。最終的に私が求める内容の正しさ(正確さ, 公平さ)を検討した内容になっています。

Power Query 基本の動作

このポストでは、Power Query の Mashup engine がクエリをどのように評価するのかということだけに注目します。Extract-Transform-Load の Transform のことを指し、かつ、その中心部分のみです。 たとえば、Power Query の重要な機能のひとつであるクエリフォールディングについては取り上げません。なぜなら、サポートされるデータソース, データソースごとの専用コネクタ, コネクタを使用するデータアクセス関数それぞれの組み合わせで特定の動作をするからです。

また、Power Query を使用できる環境(Power BI や Excel, データフローなどサービスの機能)による動作の違いも存在します。なので、環境ごとに異なる動作も取り上げません。このポストで整理した内容はすべてで共通になる基本の動作についてです。環境ごとの動作のギャップは使用するまでには知らなければならないですが、基本の動作を知る観点では複雑すぎる内容です。

なお、このポストには Power Query とは？という説明的な内容はありません。

Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query editor for applying transformations. Because the engine is available in many products and services, the destination where the data is stored depends on where Power Query is used. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.
Power Query は、データ変換とデータ準備エンジンです。 Power Query には、ソースからデータを取得するためのグラフィカル インターフェイスと、変換を適用するための Power Query エディターが付属しています。 エンジンは多くの製品やサービスで使用できるため、データの格納先は Power Query の使用場所によって異なります。 Power Query を使用すると、データの抽出、変換、読み込み (ETL) 処理を実行できます。

Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with desktop Analysis Services, Excel, and Power BI workbooks, in addition to many online services, such as Fabric, Power BI service, Power Apps, Microsoft 365 Customer Insights, and more. A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M formula language. The M language is a functional, case sensitive language similar to F#.

関数型言語の重要なコンセプト

まずは、一般的な理解として関数型言語の重要な概念を整理しておきます。このあと、関数型言語である Power Query を掘り下げます。

• 制約と安全性
  • 純粋性
    • 純粋関数: 副作用(外部状態の変更)がなく、引数が同じであれば常に同じ結果を返す(決定的である)。
    • 参照透過性: 外部(関数外の可変な状態)の影響を受けない特性。これにより、関数呼び出し(式)をその計算結果(値)に置き換えても、プログラム全体の動作が変わらないことを保証する。
  • 不変性: データが一度作成された後、その状態が変更されない
• 抽象化と再利用性
  • 第一級関数: 関数も値のひとつである
    • 高階関数: 関数を引数としたり戻り値が関数であることも可能(抽象度の高い関数)

Power Query の特徴

Power Query は (おおむね純粋)な関数型言語 です。
おおむね純粋とは、Power Query が関数型言語の純粋な原則(不変性や参照透過性)をベースにしつつ、データ接続や変換という命題を達成するために、外部データアクセスやストリーミング処理において非純粋な要素(副作用や非決定性)を意図的に許容します。

純粋関数型として振る舞い、最適化と安全性を維持しながら、外部データの変化やI/Oという現実へ対処するために、制御された範囲で例外を設けています。この例外とは、最新のデータを取得するという重要性と、ストリーミング処理などの合理的な戦略による最適化を優先することを指します。

1. 純粋性の実用的実装：非決定的な関数の許容

Power Query の式や値自体は純粋(不変かつ決定的)ですが、ETLという目的上、データソースにアクセスする "関数" が非決定的(外部の状態に依存)であるという現実があります。

• 実態: Web.Contents や Sql.Database、DateTime.LocalNow などの関数は、参照透過ではありません。これらは実行するタイミングや外部の状態によって、異なる結果(値)を返します。
• 実装: Mashup engine は、これらの "非決定的な関数" をデータ抽出(Extract)として許容します。抽出された値を変換するプロセスだけを純粋な関数として扱うことで、実用性と安全性を維持します。

2. 不変性の実用的実装：型と構造によるアプローチ

Power Query の "不変性" の実装は、データの型と構造によって厳密さが異なります。不変性という理想の実用的な実装(落としどころ)です。

• スカラ値(厳密な不変性): 数値やテキストなどのプリミティブ値は厳密に不変であり、参照透過性が保証されます。これにより、Mashup engine はキャッシュや定数畳み込みなどを行うことができます。
• ストリーミングセマンティクス: テーブル、リスト、およびバイナリについては、リソース制約の観点からストリーミングセマンティクスが適用されます。
  たとえば、テーブルが評価されるとき、行が順番に流れてくるストリーム(流れ)として扱うという意味です。Mashup engine はテーブル全体を読み込む必要はなく、テーブルの最小単位である行ごとで変換し次の処理に流します。ストリーミングによって、変換前(入力)と変換後(出力)のテーブル全体をそれぞれ管理する必要がありません。結果として、メモリ消費を合理的に削減しながら、"元のデータを書き換えない(不変性)"という論理的なルールを維持します。

3. 第一級関数 / 高階関数の実用的実装

関数を "値" として扱えるため、データ変換のロジックを部品化し、抽象度を高めることができます。

• 高階関数の利用: List.Transform や Table.SelectRows などの関数は、引数として "変換ロジック(関数)" を受け取ります。
• UIと拡張性: each _ > 100 のような式(変換関数)は、UI操作の裏側で生成され、高階関数に渡されています。これにより、標準的なUI操作と関数による高度な拡張性を両立しています

4. 実行メカニズム：純粋な依存関係定義と Mashup engine

扱う "値" が非純粋であっても、Power Query によって定義される "変換ロジック(式)" と "依存関係" 自体は純粋かつ決定的です。 Mashup engine はこの性質を利用して、以下のように動作します。

• 決定的な依存関係グラフと抽象化の展開: Mashup engine は、データの値に関わらず、コードの記述のみに基づいて不変の依存関係グラフを構築します
  抽象度の高い関数は、定義に基づいてよりプリミティブな変換関数に展開されて依存関係グラフに組み込まれます。たとえば Table.TransformColumnTypes は、Table.AddColumn → Table.SelectColumns → Table.RenameColumns という依存関係に展開されます。
  この構造の決定性により合理的なクエリプランを構築します。
• 遅延評価と最適化: 依存関係グラフは純粋であるため、Mashup engine はクエリの評価を始める前に構造上の最適化(不要なステップの削除や順序変更)を安全に行えます。また、遅延評価によりクエリの結果に必要なデータだけを評価することができます。
• ロジックの注入: Table.SelectRows のような汎用的な処理ノードに対し、ユーザーの定義した具体的なロジック(第一級関数)が注入され、そのノードの動作定義としてセットされます。

5. 実行メカニズムの具体例

Mashup engine がどのように依存関係グラフを構築し実行するかを具体例を挙げて示します。

構造と基本原理

Mashup engine は式の依存関係から動作(宣言された変換)を解釈するだけです。

<例1: let 式と依存関係>
let 式は手続き的なステップ実行に見えますが、実際には 式のネスト(依存関係) を人間が読みやすく記述するための糖衣構文といえます。以下の式は、Mashup engine にとっては全く同じ依存関係グラフとして解釈されます。

A. let を使用した記述(手続き型を模した記述)
let 式に記述された Source = ..., Filtered = ..., Result = ... の記述された順番には重要な意味はありません。

let  
    Source = SourceTable,  
    Filtered = Table.SelectRows(Source, each [Price] > 100),  
    Result = Table.SelectColumns(Filtered, {"Name", "Price"})  
in  
    Result

B. ネストした記述

Table.SelectColumns(  
    Table.SelectRows(  
        SourceTable,  
        each [Price] > 100  
    ),  
    {"Name", "Price"}  
)

C. record での記述
let 式と同様に Source = ..., Filtered = ..., Result = ... の記述されたフィールドの順番には重要な意味はありません。

[
    Source = SourceTable,  
    Filtered = Table.SelectRows(Source, each [Price] > 100),  
    Result = Table.SelectColumns(Filtered, {"Name", "Price"})  
][Result]

ストリーミング

ストリーミングはテーブル, リスト, バイナリに対する変換で不変性を維持するための実用的な実装です。変換前後データをメモリ上に保持せず、パイプラインを流れる状態として扱うことで、大きなメモリ消費を回避します。

<例2: リストにおける遅延評価とストリーミング>
リストなど構造化された値に対し、Mashup engine は最終的な要求に応じて必要なデータだけを評価します。これが Power Query の実用的な遅延評価とストリーミングの実装です。Excelのワークシート関数における先行評価とは対照的です。

• 比較: Excelの場合
  Excelで SUM( TAKE( SEQUENCE( 1048577 ), 10 ) ) を評価すると #VALUE! エラーになります。TAKE で要素を制限する前に SEQUENCE が制限を超えた配列を生成しようとする(遅延評価されない)ためと推測できます。
• Power Queryの場合
  たとえば、無限のリストを定義しても、エラーや OOM とはならず瞬時に評価結果を得ます。List.Sum( List.FirstN( List.Numbers( 1, Number.PositiveInfinity ), 10 ) )
  • 動作: List.FirstN は上流の List.Numbers の結果に対して最初の10個だけを要求します。
  • 結果: List.Numbers では無限の数を生成する定義になっていますが、下流からの要求が10なので、実際に生成されるのは10個だけです。
  • メリット: 中間結果としてリストをメモリに保持する必要がないため、メモリ不足(OOM)にならず、結果も瞬時に得られます。

<例3: テーブルの操作と行ごとのストリーミング>
Power Qurey によるテーブル操作の多くは、列単位ではなく行単位のストリーミングとして実行されます。ステップの結果がメモリ上に保持されることはありません。

let  
    SourceTable = #table( type table [Column1], {{1}, {2}, {3}} ),  
    AddedColumn2 = Table.AddColumn(  
        SourceTable,  
        "Column2",  
        each [Column1] * 10  
    ),  
    AddedColumn3 = Table.AddColumn(  
        AddedColumn2,  
        "Column3",  
        each [Column2] + 5  
    )  
in  
    AddedColumn3

• 行ごとの処理フロー: Mashup engine は以下のように処理を進めます。
  1. Row 1: Source (1) → AddColumn2 (1*10=10) → AddColumn3 (10+5=15) → 出力
  2. Row 2: Source (2) → AddColumn2 (2*10=20) → AddColumn3 (20+5=25) → 出力
• メリット: AddedColumn2 という中間結果をメモリに保持する必要がありません。行ごとの計算結果はバケツリレーのように次のステップへ渡されるだけです。これにより、大きなテーブルであってもリソース消費を抑えて変換を行うことができます。

最適化とクエリプラン

Mashup engine は、依存関係グラフの解析によって合理的なクエリプランへの変更や参照関係にあるクエリを統合することができます。

<例4: 複数のクエリにまたがる参照とを依存関係>
セクション内で定義された複数のクエリ(名前付きの式)が互いに参照し合う場合でも、Mashup engine はそれらを単一の依存関係グラフとして統合し、実行します。

Query1 =
let  
    Source = #table({"Column1"}, {{1}, {2}, {3}})  
in
    Source

Query2 =
let  
    Source = Query1,  
    AddedColumn2 = Table.AddColumn(Source, "Column2", each [Column1] * 10)  
in
    AddedColumn2

Query3 =
let  
    Source = Query2,  
    AddedColumn3 = Table.AddColumn(Source, "Column3", each [Column2] + 5)  
in
    AddedColumn3

• 式の置換と統合: Query3 が評価されるとき、Source = Query2 という参照は、Query2 の式(ロジック)そのものとして解釈されます。同様に Query2 内の Query1 も展開されます。
• 単一の実行パイプライン: 結果として、Query1 のソース生成から Query3 の最終列追加までが、途切れのないひとつのストリームとして実行されます。Query2 の結果が再利用可能なキャッシュされたりしてストリーミングが分断されることはありません。

<例5: 抽象化された関数の展開と不要な計算のスキップ>
Mashup engine は、抽象度の高い関数をプリミティブな関数に分解して依存関係を高度に解決します。その結果、下流で不要となった列に対する処理は、上流の関数内部での定義であっても実行されません。

let  
    SourceTable = #table(  
        type table [Column1, Column2, Column3],  
        {{1, "A", #date(2020, 1, 1)}
        ,{2, "B", #date(2020, 2, 1)}
        ,{3, "C", #date(2020, 3, 1)}}  
    ),  
    ChangedDataTypes = Table.TransformColumnTypes(  
        SourceTable,  
        {{"Column1", type number}
        ,{"Column2", type text}
        ,{"Column3", type date}}  
    ),  
    SelectedColumns = Table.SelectColumns(  
        ChangedDataTypes, {"Column1", "Column3"}  
    )  
in  
    SelectedColumns

• 分解と展開: Table.TransformColumnTypes は、内部的に各列ごとの変換(列追加→選択→リネーム等のプリミティブな操作)に分解されて依存関係グラフに組み込まれます。
• 依存関係の解決: 最終ステップ Table.SelectColumns が要求するのは Column1 と Column3 だけです。
• 最適化: Mashup engine は依存関係を整理し、Column2 の変換結果は最終出力に不要であると判断します。そのため、Column2 に対するデータ型変換(type text)の処理自体が行われません。

<例6: 操作の並べ替えによる最適化>
Power Query は宣言的であるため、Mashup engine は宣言された順序とは異なる順序で変換を実行することがあります。

記述されたクエリ(ソート → フィルタ)

let  
    Source = #table( {"Column1"}, {{1}, {2}, {3}} ),  
    SortedTable = Table.Sort(Source, {{"Column1", Order.Descending}}),  
    SelectedRows = Table.SelectRows(SortedTable, each ([Column1] <> 2))  
in  
    SelectedRows

実際の動作(フィルタ → ソート)

let  
    Source = #table( {"Column1"}, {{1}, {2}, {3}} ),  
    // フィルタリングが先に実行される(データ量が減るため効率的)
    SelectedRows = Table.SelectRows(Source, each ([Column1] <> 2)),  
    SortedTable = Table.Sort(SelectedRows, {{"Column1", Order.Descending}})  
in  
    SortedTable

• 最適化の原理: Table.SelectRows(フィルタ)と Table.Sort(ソート)は互いに副作用がなく可換であるため、Mashup engine は安全に順序を入れ替えることができます。これにより、コストの低い順序(先にデータを減らすフィルタリング)を選択してクエリプランを構築します。

宣言的思考への転換

最適化の副作用として発生する手続き型の直感に反するケース。

<例7: 手続き的思考のピットフォール(ソート後の重複排除)>
Excelなどの手続き的なツールでは "ソートしてから重複排除" を行えば "最新の行を残す" ことができますが、Power Queryでは意図通りに動作しないことがあります。これは Mashup engine による最適化が影響します。

記述されたクエリ(日付でソート → IDで重複排除)

let  
    Source = ...,   
    // 最新の日付を上にソート  
    Sorted = Table.Sort(Source, {{"Date", Order.Descending}}),  
    // IDごとに重複を排除(期待値: 最新の行が残る)  
    Distinct = Table.Distinct(Sorted, {"ID"})  
in  
    Distinct

Mashup engine による最適化(重複排除 → ソート)
Mashup engine は "Table.Distinct は行の順序に依存しない(どの行を残すか保証しない)" かつ "先にデータ量を減らした方がソートには合理的" と判断し、ソートよりも先に重複排除を実行する可能性があります。結果として、どの行が残るかは不定になります。

推奨されるアプローチ(グループ化と最大値の抽出)
"順序に依存して行を残す" という手続き的なアプローチではなく、 "各グループの中で最大の値を持つ行を取得する" という宣言的なアプローチをとるべきです。なお、Table.Buffer はデータをメモリに読み込むため、データ量大小関係なく避けたほうがよい選択肢です。

Table.Group(  
    Source,  
    {"ID"},  
    // グループ内の最大(最新)の行を取得  
    {{"LatestRow", each Table.MaxN(_, "Date", 1), type table}}  
)

Mashup engine は "IDごとにグループ化し、Dateが最大の行を特定する" という意図を正確に理解できるため、常に期待する結果を得られます。

<例8: Table.Buffer のスコープとキャッシュの誤解>
共通の参照元となるクエリで Table.Buffer を使用すれば、その結果がキャッシュされ、他のクエリ間で再利用される(読み込みが1回で済む)という誤解が多いです。

シナリオ:

• QueryA: Table.Buffer( ... )
• QueryB: QueryA を参照
• QueryC: QueryA を参照

期待:
QueryA がの結果はキャッシュされ、QueryB と QueryC はそれを共有するという期待。

実際の動作(独立性):
Power Query の評価はクエリごとに独立したコンテキスト(統合された依存関係グラフ)を持ちます。QueryB の評価時に確保されたバッファは、QueryC の評価時には存在しません(QueryC のために再度実行・確保されます)。

<例9: クエリのマージとキャッシュの選択>
テーブルのマージ(Table.NestedJoin)では、結合するクエリの結果は合理的な選択が自動的に適用されます。

最適化の仕組み: Table.NestedJoin は内部的に Table.Join を JoinAlgorithm.Dynamic モードで呼び出しています。Mashup engine は2つのテーブルの特性(スキーマや行数など統計情報)に基づき、以下の3つのパターンから最適なキャッシュ戦略を自動的に選択します。

• 左側をキャッシュ: 左のテーブルが小さければ、それをメモリに読み込んでハッシュテーブルを構築し、右側をストリーミングします。
• 右側をキャッシュ: 右のテーブルが小さければ、それをメモリに読み込んでハッシュテーブルを構築し、左側をストリーミングします。
• 両方をキャッシュ: 両方のテーブルの状態や結合キーの特性に応じて、両方をメモリに読み込んでハッシュテーブルを構築し、マッチングを行います。

宣言的アプローチの推奨: ほとんどの場合で "左と右、どちらをキャッシュすべきか" を検討する必要はありません。合理的な最適化を仕向けるだけで十分です。マージより前に必要な行と列だけに制限しておくだけです。また、必要がないなら左右両方のクエリの結果をキャッシュしなければならない結合の種類を選択しないことです。

6. 注意事項: 基本動作と実環境における複雑性

これまでに挙げた具体例は、Mashup engine が実装上目指している基本的な動作原理を示したものです。しかし、実際の開発や運用環境においては、これら最適化やストリーミングが常に保証されるわけではありません。

• 最適化のベストエフォート性: Mashup engine による最適化(順序変更や不要部分の削除)は、データソースの機能(クエリフォールディングの可否)や、使用する関数の特性、クエリの複雑さに依存します。記述の違いが最適化を阻害し、異なる動作をすることがあります。
• 環境要因による変動: 動作するホスト環境(Power BI Desktop, Excel, Power BI データフローなど)、プライバシーレベル(ファイアウォール設定)などにより動作は変化します。
• 検証の必要性: これまでに挙げた動作例は "仕組みを理解するためのメンタルモデル" として捉えるべきです。実際のパフォーマンスチューニングにおいては、理論だけでなく、トレース情報やクエリ診断(Query Diagnostics)などのツールを使用して実際の挙動を計測・検証することが不可欠です。

まとめ: 宣言的思考への転換

関数型パラダイムに基づくPower Queryでは、従来の "変換の順序" に依存する思考をやめる必要があります。

Power Query は、関数型言語の理論とデータ処理の命題を実用的に実装した言語です。手続き型思考から宣言的思考へと転換することが最も重要です。 Mashup engine が最適化しやすいように『何をしたいか』を明確に定義(仕向ける)し、『どう処理するか』という内部動作の制御はエンジンに委ねる という考え方が適しています。複雑な動作まで制御しようとすることは、失敗の始まりです。

その他