power-query

セマンティックモデルの Power Query

セマンティックモデルのインポートパーティションで、Power Query M 式を作成、検証、テストします。正しい M コードの記述、クエリ フォールディングの維持、式の検証、実際のデータソースに対する実行テストを扱います。

パーティション式

セマンティックモデルの各インポート テーブルには、更新時に読み込むデータを定義する M 式を含むパーティションがあります。この式は通常、データソースに接続し、テーブルまたはビューに移動して、変換を適用します。

パーティション式の構造

let
    Source = Sql.Database(#"SqlEndpoint", #"Database"),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data, {"InternalId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns", {{"Amount", Currency.Type}})
in
    #"Changed Type"

主な要素：

• パラメーター：#"SqlEndpoint"、#"Database" は、モデルレベルで定義された共有 M パラメーターです
• ナビゲーション：Source{[Schema="dbo", Item="Orders"]}[Data] は、特定のテーブルに移動します
• ステップ：各ステップは、let...in の連鎖で名前付き変数として定義されます
• 引用符付き識別子：スペースを含むステップ名には、#"Step Name" 構文を使用します

式の抽出

# Get partition expression from TMDL via fab
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/tables/<Table>.tmdl'].payload"

# Get shared M parameters
fab get "<Workspace>.Workspace/<Model>.SemanticModel" -f \
  -q "definition.parts[?path=='definition/expressions.tmdl'].payload"

M 式の作成

クエリ フォールディング

クエリ フォールディングは、パフォーマンスにおいて最も重要な概念です。M エンジンは、互換性のあるステップをネイティブのデータソース クエリ（例：SQL）に変換します。フォールディングが中断されると、以降のステップはマッシュアップ エンジンで実行され、最初にすべてのデータがメモリに読み込まれます。

通常フォールディングされるステップ（SQL ソースの場合）：

• Table.SelectColumns / Table.RemoveColumns → SELECT
• Table.SelectRows → WHERE
• Table.Sort → ORDER BY
• Table.FirstN → TOP
• Table.Group → GROUP BY
• Table.RenameColumns → AS エイリアス

フォールディングされる場合とされない場合があるステップ（データソースによって異なります）：

• Table.TransformColumnTypes -- SQL Server ソースでは、テキストから数値または日付への変換でフォールディングが頻繁に中断されます。より確実にフォールディングできる代替手段として、明示的な変換関数（例：Number.From）を指定した Table.TransformColumns を使用してください。

フォールディングを中断するステップ：

• カスタム M 関数を使用した Table.AddColumn（SQL に変換できません）
• Table.Buffer（マテリアライズを強制します。メモリのオーバーヘッドなしにフォールディングを停止するには、Table.StopFolding を使用してください）
• Table.LastN（サブクエリなしでは SQL に相当するものがありません）
• 異なるデータソース間の Table.Combine（同じ SQL Server 内でのデータベース間フォールディングは、EnableCrossDatabaseFolding を使用すれば可能です）
• M 固有のロジックを含む複雑な each 式
• フォールディングを中断するステップの後に続くすべてのステップ

ベストプラクティス： フォールディング互換のステップ（フィルター、列の選択、型の指定）を早い段階で適用し、カスタム列や M のみの変換は、フォールディング可能な処理をすべて終えた後に追加してください。

列の削除と行のフィルター処理

使用しない列を削除し、できるだけ早い段階で行をフィルター処理してください。

let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    // Filter and select BEFORE any custom transforms
    #"Filtered" = Table.SelectRows(Data, each [Status] <> "Cancelled"),
    #"Selected" = Table.SelectColumns(#"Filtered", {"OrderId", "Date", "Amount", "CustomerId"})
in
    #"Selected"

これらのステップは、次の SQL にフォールディングされます：SELECT OrderId, Date, Amount, CustomerId FROM dbo.Orders WHERE Status <> 'Cancelled'

型の処理

• Table.TransformColumnTypes を早い段階で適用してください（SQL の CAST にフォールディングされます）
• 明示的な M 型を使用してください：Int64.Type、type text、type date、Currency.Type、type logical
• 大規模なデータセットでの暗黙的な型推論は避けてください

命名規則

• ステップ名には変換内容がわかる名前を付けてください：#"Removed Duplicates"、#"Filtered Active"
• #"Custom1" や #"Step1" のような汎用的な名前は避けてください
• スペースを含むステップには、引用符付き識別子 #"Name" を使用してください（Power Query の規約）

M 式の検証

M 式の構文が正しく、期待どおりの結果が得られることを検証する方法は 2 つあります。

1. Power Query API 経由で実行する（推奨）

実際のデータに対して式を実行してテストします。これにより、構文、データソースへの接続、変換の正しさを一度に検証できます。

同梱の examples/execute_m.py で実行するワークフロー全体：

1. ワークスペースにランナー データフローを作成するか、既存のものを再利用します
2. ランナーにデータソース接続をバインドします
3. 式をセクション ドキュメントでラップし、パラメーターをインライン化します
4. POST /v1/workspaces/{wsId}/dataflows/{dfId}/executeQuery 経由で実行します
5. Arrow レスポンスを解析してデータを検証します

MASHUP='section Section1;
shared SqlEndpoint = "myserver.database.windows.net";
shared Database = "MyDB";
shared Result = let
    Source = Sql.Database(SqlEndpoint, Database),
    Data = Table.FirstN(Source{[Schema="dbo",Item="Orders"]}[Data], 10)
in Data;'

curl -s -o result.bin -X POST ".../executeQuery" \
  -H "Authorization: Bearer ${TOKEN}" -H "Content-Type: application/json" \
  -d "$(jq -n --arg m "$MASHUP" '{queryName:"Result",customMashupDocument:$m}')"

手順とエラー処理については、references/validation.md を参照してください。

2. XMLA / TOM 経由でパーティションを保存する

式をモデルに書き戻します。Analysis Services は保存時に M 構文を検証します。クエリは実行されませんが、次のような構造上のエラーを検出できます。

• let／in の欠落または不一致
• 未定義のステップ参照
• 無効な関数呼び出し
• TransformColumnTypes 内の型の不一致

# Edit the TMDL partition source directly and deploy via fab import,
# or use the XMLA endpoint with Tabular Editor or SSMS to modify
# the partition expression on the deployed model.

式の形式に誤りがある場合、AS はエラーを返します。全クエリを実行するより高速ですが、実行時エラー（列名の誤り、データソースの問題など）は検出できません。

検証方法の選択

必要な内容	使用方法
データの完全な検証（列、型、値が正しいか）	API 経由で実行
簡易的な構文チェック	XMLA／TOM 経由でモデルに保存
ステップごとのデバッグ	in 句を途中で切って実行
パフォーマンスのテスト（クエリフォールディングの確認）	全データで実行し、所要時間を確認

パーティションのステップをプレビューする

let...in を途中で切ることで、変換チェーンの任意の時点のデータを確認できます。

-- See raw source data (all columns)
in Data;

-- See after column removal
in #"Removed Columns";

-- See final result
in #"Changed Type";

大きなテーブルの行数を制限するには、in の前に Table.FirstN(stepName, 100) を追加します。手順の詳細については、references/validation.md を参照してください。

よくあるパターン

増分更新パーティション

増分更新パーティションでは、RangeStart パラメーターと RangeEnd パラメーターを使用します。

let
    Source = Sql.Database(#"SqlEndpoint", #"Database"),
    Data = Source{[Schema="dbo", Item="Orders"]}[Data],
    #"Filtered" = Table.SelectRows(Data, each
        [OrderDate] >= #"RangeStart" and [OrderDate] < #"RangeEnd")
in
    #"Filtered"

テスト時は、RangeStart と RangeEnd に具体的な日付の値を直接指定してください。

Lakehouse ソース

let
    Source = Lakehouse.Contents(null),
    Data = Source{[Id="lakehouse-guid"]}[Data],
    Table = Data{[Id="table-name", ItemKind="Table"]}[Data]
in
    Table

ネイティブクエリを使用した SQL

M では表現できない複雑な SQL の場合：

let
    Source = Sql.Database("server", "db"),
    Data = Value.NativeQuery(Source, "SELECT * FROM dbo.MyView WHERE Year = 2024", null, [EnableFolding=true])
in
    Data

EnableFolding=true を指定した Value.NativeQuery を使用すると、ネイティブクエリに続く M のステップでクエリフォールディングが可能になります。

参考資料

• references/validation.md -- executeQuery API、ステップのプレビュー、エラー処理を含む、詳細な検証ワークフロー
• references/best-practices.md -- クエリフォールディングのガイダンス、フォールディングを妨げる要素の一覧、アンチパターン、パフォーマンスのヒント
• examples/execute_m.py -- Fabric API 経由で M 式を実行する Python スクリプト（CLI ツール）
• examples/preview_partition.py -- 任意のステップでパーティションデータをプレビューする Python スクリプト（fab get と execute_m.py を使用）
• Power Query M リファレンス
• クエリフォールディングのガイダンス