Excel VBA で Power Query の M コード（コマンドテキスト）を動的生成する方法

2025年7月27日

Power Query では、M 言語のスクリプト（コマンドテキスト）を VBA から組み立てて登録することで、CSV など外部データを柔軟に取り込めます。本記事では 配列と Join 関数 を活用し、流れの中で変わり得るパラメータ（ファイルパスやフィルター条件）を動的に差し込む手順を解説いたします。

サンプルシナリオ

データソース : data_utf8.csv（UTF‑8 形式、カンマ区切り）
要件 : ヘッダー行を昇格し、担当者列が「Hayashi Taro」に一致する行のみ抽出
ターゲットクエリ名 : CSV_Import_Query

サンプルコード

Sub CreateDynamicPowerQuery()

    Dim csvFullPath As String      ' CSV ファイルのフルパス
    Dim mScript()  As String       ' M コードを格納する可変長配列
    Dim queryName  As String       ' 登録するクエリ名
    
    ' 取り込み対象のファイルを組み立て
    csvFullPath = ThisWorkbook.Path & "\data_utf8.csv"
    
    ' クエリ名を設定
    queryName = "CSV_Import_Query"
    
    ' -------  M スクリプトを配列で構築  -------
    mScript = Array( _
        "let", _
        "    Source  = Csv.Document(File.Contents(""" & csvFullPath & """),", _
        "        [Delimiter = "","", Encoding = 65001, QuoteStyle = QuoteStyle.None]),", _
        "    Promote = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),", _
        "    Filter  = Table.SelectRows(Promote, each ([担当者名] = ""Hayashi Taro""))", _
        "in", _
        "    Filter" _
    )
    ' -----------------------------------------
    
    ' 配列を改行で連結
    Dim mCode As String
    mCode = Join(mScript, vbCrLf)
    
    ' 既存クエリがあれば上書き、なければ追加
    Dim pq As WorkbookQuery
    On Error Resume Next
    Set pq = ThisWorkbook.Queries(queryName)
    On Error GoTo 0
    
    If pq Is Nothing Then
        ThisWorkbook.Queries.Add Name:=queryName, Formula:=mCode
    Else
        pq.Formula = mCode
    End If
    
    MsgBox "Power Query が登録されました。", vbInformation

End Sub

コードのポイント

項目	説明
配列 + `Join`	コードの各行を配列に格納し、`Join` で改行を挟んで連結しています。保守性が高く、途中行の編集が容易です。
UTF‑8 エンコーディング	`Encoding = 65001` を指定し、文字化けを防止しています。
フィルター条件	`Table.SelectRows` で列 `[担当者名]` が `"Hayashi Taro"` に一致する行だけを抽出しています。条件を変数化すれば、担当者名を動的に切り替えられます。
二重登録の防止	`WorkbookQuery` オブジェクトの存在を確認し、同一名クエリがあれば `Formula` プロパティを更新します。

応用アイデア

目的	実装例
複数 CSV を一括登録	ファイル名をコレクションで管理し、`For Each` でクエリ名とパスをループ処理します。
日付別フォルダーを自動解決	`csvFullPath` を `Format(Date, "yyyymmdd")` で構築し、最新ファイルを参照させます。
複数条件フィルター	`each ([列A] > 0 and [列B] = ""Complete"")` のように条件式を拡張します。