power-query-m

Power Query（M Language）

概要

Power Query は、M 関数型言語を使用する Power BI のデータ変換エンジンです。データソースからデータモデルへの ETL（抽出、変換、読み込み）を処理します。パフォーマンスの高いデータ更新には、クエリ フォールディング、ステップの最適化、M 構文の理解が重要です。

クエリ フォールディング

クエリ フォールディングは、M のステップをネイティブなデータソースクエリ（SQL、OData など）に変換し、マッシュアップエンジンではなくデータソース側で計算を実行します。

フォールディングの確認方法：

1. 適用したステップでステップを右クリックし、［ネイティブ クエリの表示］を選択します。グレー表示になっている場合は、フォールディングが中断されています
2. クエリ診断（［ツール］>［診断の開始］）を使用して、送信されるクエリを確認します

フォールディングされるステップ（一般的な例）：

操作	SQL への変換
列の削除	SELECT（列リスト）
行のフィルター	WHERE 句
行の並べ替え	ORDER BY
グループ化	GROUP BY
列名の変更	列エイリアス
型の変更（基本）	CAST
クエリのマージ（データベース）	JOIN
上位 N 行	TOP / LIMIT
重複の削除	DISTINCT

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

操作	理由
カスタム列の追加（複雑なもの）	M 式を SQL に変換できません
ピボット／ピボット解除（一部の場合）	データソースの機能によって異なります
フォールディングできないデータソースとのマージ	データソース間の結合をプッシュできません
Table.Buffer	メモリ上に明示的に実体化します
カスタムステップの後に並べ替え	いったん中断すると、以降のステップはフォールディングできません
日付／時刻の変換（一部）	データソースによって関数に違いがあります

基本ルール： フォールディング可能なステップは、フォールディングできないステップより前に配置します。フォールディングが中断されると、それ以降のステップはすべてマッシュアップエンジンで実行されます。

M 言語の基本

Let 式（クエリ構造）

Power Query のクエリはすべて、let...in 式です。

let
    Source = Sql.Database("server", "database"),
    Filtered = Table.SelectRows(Source, each [Status] = "Active"),
    Renamed = Table.RenameColumns(Filtered, {{"OldName", "NewName"}}),
    Typed = Table.TransformColumnTypes(Renamed, {{"Amount", type number}})
in
    Typed

データ型

M 型	説明
type text	文字列／テキスト
type number	小数
Int64.Type	整数（64 ビット整数）
type date	日付のみ
type datetime	日付と時刻
type datetimezone	日付、時刻、タイムゾーン
type duration	時間の長さ
type logical	ブール値（true/false）
type binary	バイナリデータ
type null	Null 値
Currency.Type	固定小数点数（小数点以下 4 桁）
Percentage.Type	パーセンテージ

一般的なテーブル関数

// Filter rows
Table.SelectRows(table, each [Column] > 100)

// Add column
Table.AddColumn(table, "NewCol", each [Col1] * [Col2], type number)

// Remove columns
Table.RemoveColumns(table, {"Col1", "Col2"})

// Select columns (keep only these)
Table.SelectColumns(table, {"Col1", "Col2", "Col3"})

// Rename columns
Table.RenameColumns(table, {{"Old1", "New1"}, {"Old2", "New2"}})

// Change types
Table.TransformColumnTypes(table, {{"Col1", type number}, {"Col2", type text}})

// Replace values
Table.ReplaceValue(table, "old", "new", Replacer.ReplaceText, {"Column"})

// Group by
Table.Group(table, {"GroupCol"}, {
    {"Sum", each List.Sum([Amount]), type number},
    {"Count", each Table.RowCount(_), Int64.Type}
})

// Merge (JOIN)
Table.NestedJoin(left, {"KeyCol"}, right, {"KeyCol"}, "Merged", JoinKind.LeftOuter)

// Expand merged columns
Table.ExpandTableColumn(merged, "Merged", {"Col1", "Col2"})

// Pivot
Table.Pivot(table, List.Distinct(table[PivotCol]), "PivotCol", "ValueCol")

// Unpivot
Table.UnpivotOtherColumns(table, {"KeepCol1", "KeepCol2"}, "Attribute", "Value")

// Sort
Table.Sort(table, {{"Col1", Order.Ascending}, {"Col2", Order.Descending}})

// Remove duplicates
Table.Distinct(table, {"KeyCol1", "KeyCol2"})

// Combine/Append tables
Table.Combine({table1, table2, table3})

// Buffer (force materialization)
Table.Buffer(table)

リスト関数

// Generate a sequence
{1..100}
List.Numbers(1, 100)
List.Dates(#date(2024,1,1), 365, #duration(1,0,0,0))

// Transform
List.Transform({1,2,3}, each _ * 2)

// Filter
List.Select({1,2,3,4,5}, each _ > 3)

// Aggregate
List.Sum(list), List.Average(list), List.Min(list), List.Max(list)

// Generate with custom logic (pagination pattern)
List.Generate(
    () => [Page = 0, Data = GetPage(0)],
    each [Data] <> null,
    each [Page = [Page] + 1, Data = GetPage([Page] + 1)],
    each [Data]
)

パラメーターと動的データソース

環境に応じた接続用のパラメーターを作成します。

// Define parameter in Power Query UI or M:
// Name: ServerName, Type: Text, Current Value: "prod-server.database.windows.net"

// Use in query:
let
    Source = Sql.Database(ServerName, DatabaseName),
    ...

動的データソースのパターン：

let
    BaseUrl = "https://api.example.com/v2/",
    Endpoint = BaseUrl & "data?page=",
    GetPage = (pageNum as number) =>
        let
            url = Endpoint & Number.ToText(pageNum),
            response = Json.Document(Web.Contents(url))
        in
            response[results],
    AllPages = List.Generate(
        () => [i = 1, res = GetPage(1)],
        each List.Count([res]) > 0,
        each [i = [i] + 1, res = GetPage([i] + 1)],
        each [res]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(Combined, Record.FieldValues,
        type table [id = Int64.Type, name = text, value = number])
in
    AsTable

エラー処理

// Try/otherwise pattern
let
    result = try SomeRiskyOperation() otherwise "default"
in
    result

// Try with error record inspection
let
    attempt = try Number.FromText("abc"),
    output = if attempt[HasError]
        then "Error: " & attempt[Error][Message]
        else attempt[Value]
in
    output

// Replace errors in a column
Table.ReplaceErrorValues(table, {{"Column1", null}, {"Column2", 0}})

// Remove error rows
Table.RemoveRowsWithErrors(table, {"Column1", "Column2"})

カスタムコネクタ

Power Query SDK を使用して、カスタム Power Query コネクタを作成します。

1. Power Query SDK（VS Code 拡張機能）をインストールします
2. DataConnector.pq ファイルを含む .mproj プロジェクトを作成します
3. 認証を含むデータソース関数を実装します
4. .mez ファイルとしてパッケージ化します
5. Documents\Power BI Desktop\Custom Connectors またはゲートウェイにデプロイします

基本的なコネクタの構造：

section MyConnector;

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = (url as text) =>
    let
        source = Web.Contents(url),
        json = Json.Document(source)
    in
        json;

MyConnector = [
    Authentication = [
        Key = [],
        OAuth = [...]
    ],
    Label = "My Custom Connector"
];

MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"My Connector", "Connect to My Service"}
];

パフォーマンスの最適化

手法	効果
フォールディング可能なステップを最初に配置する	高 — データソース側で処理を実行します
未使用の列を早い段階で削除する	高 — データ量を減らします
結合の前に早い段階でフィルターを適用する	高 — 行数を減らします
必要な場合を除き Table.Buffer を避ける	中 — 不要な実体化を防ぎます
フォールディングできない場合はネイティブクエリを使用する	高 — マッシュアップエンジンを回避します
ステージングクエリの［レポートの更新に含める］を無効にする	中 — 不要な更新を省略します
並列読み込みに Table.Partition を使用する	中 — 大きなテーブルを並列処理します
プライバシーレベルを正しく設定する	中 — 設定が正しくないとフォールディングが阻止されます

その他のリソース

参照ファイル

• references/m-patterns-cookbook.md — 一般的な M パターン：Web API のページネーション、増分読み込み、JSON のフラット化、CSV の処理、SharePoint フォルダーの結合