[Excel VBA] How to Dynamically Generate Power Query M Code

2025年12月27日

In Power Query, you can build and register M language scripts (command text) using VBA to flexibly import external data like CSV files.

This article explains how to use Arrays and the Join function to dynamically insert changing parameters, such as file paths and filter conditions, into your query.

Sample Scenario

Data Source: data_utf8.csv (UTF-8 format, comma-separated)
Requirements: Promote the header row and extract only rows where the “Person in Charge” column matches “Hayashi Taro”.
Target Query Name: CSV_Import_Query

Sample Code

Sub CreateDynamicPowerQuery()

    Dim csvFullPath As String      ' Full path of the CSV file
    Dim mScript()  As String       ' Dynamic array to store M code
    Dim queryName  As String       ' Name of the query to register
    
    ' Build the file path for import
    csvFullPath = ThisWorkbook.Path & "\data_utf8.csv"
    
    ' Set the query name
    queryName = "CSV_Import_Query"
    
    ' ------- Build M script using an array -------
    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" _
    )
    ' -----------------------------------------
    
    ' Join the array with line breaks
    Dim mCode As String
    mCode = Join(mScript, vbCrLf)
    
    ' Overwrite if query exists, otherwise add new
    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 has been registered.", vbInformation

End Sub

Key Points of the Code

Item	Description
Array + Join	Each line of code is stored in an array and concatenated with line breaks using `Join`. This ensures high maintainability and makes editing specific lines easy.
UTF-8 Encoding	Specifying `Encoding = 65001` prevents text garbling (Mojibake).
Filter Condition	`Table.SelectRows` extracts only rows where the `[Person Name]` column matches “Hayashi Taro”. By turning this condition into a variable, you can dynamically switch names.
Preventing Duplicates	The code checks for the existence of the `WorkbookQuery` object. If a query with the same name exists, it updates the `Formula` property; otherwise, it creates a new one.

Advanced Ideas

Batch Registration: Manage multiple file names in a collection and loop through them using For Each to register multiple queries.
Date-Specific Folders: Construct csvFullPath using Format(Date, "yyyymmdd") to automatically reference the latest daily file.
Multi-Condition Filters: Extend the filter logic, for example: each ([ColumnA] > 0 and [ColumnB] = "Complete").

Summary

When building M scripts from VBA, using Arrays + Join is convenient for ensuring readability and maintainability.

By separating parameters (like file paths and conditions) as variables, you can minimize the range of code modifications required when requirements change. Additionally, checking for existing queries before adding or updating them ensures smooth Power Query management within your workbook.

Try combining Power Query and VBA to automate and standardize your data import flows.

よかったらシェアしてね！