概要
データ分析の現場で、Power Queryはもはや必要不可欠なツールとなっています。しかし、多くのユーザーが直面する課題の一つに、データソースのパス、ファイル名、シート名が固定されてしまうという問題があります。ファイルが移動したり、ファイル名が変更されたり、あるいは異なる環境で同じクエリを使用する際に、手動でパスを修正する手間は、せっかくの自動化効率を著しく損ねてしまいます。
本記事では、この課題を根本から解決するため、Power QueryのM言語とExcelのセル値を連携させ、データソースのパス、ブック名、そしてシート名を完全に動的に指定する方法を、ベテランExcel VBA講師としての視点から徹底解説します。M言語の強力な機能とExcelの柔軟性を組み合わせることで、一度設定すれば、セルの値を変更するだけで接続先を切り替えられる、堅牢かつ柔軟なデータ取得プロセスを構築できるようになります。これにより、煩雑な手作業から解放され、真のデータ自動化を実現するための道筋を明らかにします。
詳細解説
Power Queryのデータソース設定は、通常、固定されたパスやファイル名を指定します。これは単一のファイルからデータを取得する分には問題ありませんが、次のようなシナリオでは大きな壁となります。
1. **ファイルパスの変更:** 共有サーバーのパスが変わった、ファイルを別のフォルダに移動した。
2. **ファイル名の変更:** 月次レポートのファイル名が「Report_202301.xlsx」から「Report_202302.xlsx」に変わった。
3. **複数ユーザーでの利用:** 各ユーザーのローカル環境で異なるパスにファイルが保存されている。
4. **シート名の変更:** データが格納されているシート名が「Sheet1」から「Data_Summary」に変更されることがある。
これらの課題を解決するのが、Power QueryのM言語とExcelのセル値の連携です。
**1. Excel.CurrentWorkbook()の活用**
M言語で現在のExcelブックの情報を取得する最も強力な関数が`Excel.CurrentWorkbook()`です。この関数は、現在のブック内に定義されている「テーブル」と「名前付き範囲」をテーブルとして返します。この特性を利用して、特定のセルに格納されたパスやファイル名を取得します。
例えば、Excelシート上でセル`B1`にファイルパス、`B2`にファイル名、`B3`にシート名を入力し、それぞれに「FilePath」「FileName」「SheetName」といった名前を定義（名前の管理）します。
`Excel.CurrentWorkbook()`を実行すると、以下のようなテーブルが返されます。
| Name | Content |
| :——— | :—————————————– |
| FilePath | Table |
| FileName | Table |
| SheetName | Table |
ここで、`Content`列は、それぞれの名前付き範囲が参照するテーブル（または単一セルの場合は1行1列のテーブル）です。この`Content`列から実際の値を取り出す必要があります。
**2. 値の抽出方法**
`FilePath`という名前付き範囲から値を取り出すには、次のようなM言語のステップを踏みます。
let
ソース = Excel.CurrentWorkbook(){[Name=”FilePath”]}[Content],
値 = ソース{0}[Column1] // もしくは Table.Column(ソース, “Column1″){0}
in
値
* `Excel.CurrentWorkbook(){[Name=”FilePath”]}`: `Excel.CurrentWorkbook()`が返すテーブルから、`Name`列が”FilePath”である行をフィルタリングします。結果はレコードになります。
* `[Content]`: フィルタリングされたレコードから`Content`フィールド（テーブル）を取得します。
* `ソース{0}[Column1]`: `Content`が単一セルを参照している場合、そのテーブルは通常1行1列で、列名は”Column1″となります。`{0}`で最初の行（レコード）を取得し、`[Column1]`でそのレコードの”Column1″フィールドの値を取得します。
**3. パス、ブック名、シート名の動的な構築**
取得した個々の値（パス、ブック名、シート名）をM言語内で組み合わせて、完全なデータソースパスを構築します。
* **完全なファイルパスの構築:**
ファイルパス（例: “C:\Data\”）とファイル名（例: “Sales.xlsx”）を結合して、”C:\Data\Sales.xlsx”という完全なパスを作成します。これには`Text.Combine`関数や文字列結合演算子`&`を使用します。
例: `FilePathValue & FileNameValue`
* **データソースへの接続:**
構築した完全なファイルパスを使って、データソースに接続します。
1. `File.Contents(完全なファイルパス)`: 指定されたパスのファイルをバイナリ形式で読み込みます。
2. `Excel.Workbook(File.Contents(…))`: バイナリデータをExcelブックとして解釈し、ブック内のシートやテーブルの情報をテーブルとして返します。
* **特定のシートの選択:**
`Excel.Workbook`が返すテーブルには、ブック内のすべてのシートやテーブルに関する情報が含まれます。ここから、動的に指定したシート名に合致するシートを選択します。
例: `Excel.Workbook(…){[Item=SheetNameValue, Kind=”Sheet”]}[Data]`
* `{[Item=SheetNameValue, Kind=”Sheet”]}`: `Item`列が`SheetNameValue`と一致し、かつ`Kind`列が”Sheet”である行をフィルタリングします。
* `[Data]`: フィルタリングされたレコードから`Data`フィールド（実際のシートデータ）を取得します。
これらのステップを組み合わせることで、完全に動的なデータソース指定を実現できます。M言語の`let … in`構造を効果的に使用し、各ステップを変数として定義することで、コードの可読性と保守性を高めます。
サンプルコード
まず、Excelシートを以下のように準備します。
1. **Excelシートの準備:**
* セル`B1`にファイルが保存されているフォルダのパスを入力します（例: `C:\Temp\`）。このセルに「**DataSourcePath**」という名前を定義します。
* セル`B2`にExcelファイルの名前を入力します（例: `MonthlyReport.xlsx`）。このセルに「**DataFileName**」という名前を定義します。
* セル`B3`に読み込みたいシート名を入力します（例: `SalesData`）。このセルに「**DataSheetName**」という名前を定義します。
名前の定義は、「数式」タブ→「名前の管理」から行えます。
2. **Power Query M言語コード:**
Power Queryエディタを開き、「新しいソース」→「空のクエリ」を選択し、以下のM言語コードを記述します。
let
// 1. 現在のExcelブックから名前付き範囲の値を取得
// ファイルパスを取得
SourcePath = Excel.CurrentWorkbook(){[Name="DataSourcePath"]}[Content]{0}[Column1],
// ファイル名を取得
SourceFileName = Excel.CurrentWorkbook(){[Name="DataFileName"]}[Content]{0}[Column1],
// シート名を取得
SourceSheetName = Excel.CurrentWorkbook(){[Name="DataSheetName"]}[Content]{0}[Column1],
// 2. 取得した値を使って完全なファイルパスを構築
// パスが末尾に'\'を持たない場合は追加
FullFilePath =
if Text.EndsWith(SourcePath, "\") then
SourcePath & SourceFileName
else
SourcePath & "\" & SourceFileName,
// 3. 構築したパスからファイルを読み込み、Excelブックとして解釈
FileContent = File.Contents(FullFilePath),
ExcelWorkbook = Excel.Workbook(FileContent),
// 4. 動的に指定されたシート名に合致するシートを選択し、そのデータを取得
// Excel.Workbookが返すテーブルには、Item（シート名）、Kind（シートかテーブルか）、Data（シートの内容）などの列が含まれる
TargetSheetData =
ExcelWorkbook{[Item=SourceSheetName, Kind="Sheet"]}[Data],
// 5. 必要に応じて、データの整形や変換を行う（例: 最初の行をヘッダーとして昇格）
PromotedHeaders = Table.PromoteHeaders(TargetSheetData, [PromoteAllScalars=true]),
// 最終的な結果
Result = PromotedHeaders
in
Result
このコードを貼り付け、Power Queryエディタで「完了 & 読み込み」を実行すれば、Excelシートの`B1:B3`セルに指定された情報に基づいてデータが読み込まれます。`B1:B3`セルの値を変更し、「データの更新」を行うだけで、別のファイルやシートからデータを取得できるようになります。
実務アドバイス
Power Queryの動的データソース指定は非常に強力ですが、実務で安全かつ効率的に運用するためには、いくつかの考慮事項とベストプラクティスがあります。
1. **セキュリティと信頼性:**
* **信頼できる場所からの読み込み:** ユーザーが自由にパスを指定できる場合、悪意のあるファイルや不正なスクリプトが実行されるリスクがあります。Power Queryは通常、信頼できる場所からの読み込みを推奨しますが、ユーザーが指定するパスが常に安全であるとは限りません。このため、可能であれば、指定可能なパスを特定の共有フォルダやネットワークドライブに限定するなどの運用ルールを設けることが重要です。
* **入力値の検証:** Excelセルに入力されるパスやファイル名が正しい形式であるかをM言語内で簡易的に検証することも検討できます。例えば、パスが存在するかどうかを事前にチェックし、存在しない場合はエラーメッセージを返すといった処理です。ただし、M言語での高度なエラーハンドリングは複雑になる傾向があるため、Excel VBA側でセルの入力規則を厳しく設定する方が現実的です。
2. **パフォーマンスの最適化:**
* **不必要なデータソースの再評価の回避:** Power Queryは、データソースのパラメータが変更されるたびに再評価を行います。複数のクエリで同じ動的パスを使用する場合、それぞれのクエリで`Excel.CurrentWorkbook()`を呼び出すのではなく、一度パスを取得する専用のクエリを作成し、他のクエリからそのクエリを参照する形にすると、処理の重複を避けることができます。
* **クエリの折りたたみ:** 動的にパスを指定する場合でも、データ取得後の変換ステップ（フィルタリング、列の選択など）が可能な限りデータソース側で処理されるよう、クエリの折りたたみ（Query Folding）を意識することが重要です。特にデータベース接続の場合に顕著な効果を発揮しますが、Excelファイルでは限定的です。
3. **エラーハンドリング:**
* **ファイルまたはシートが見つからない場合:** 指定されたパスにファイルが存在しない、あるいは指定されたシート名が見つからない場合、Power Queryはエラーを発生させます。M言語の`try … otherwise`構文を使用することで、これらのエラーを捕捉し、ユーザーフレンドリーなメッセージを表示したり、デフォルトの値を返したりすることが可能です。
let
// ... 前述のパス取得部分 ...
TryFileAccess =
try File.Contents(FullFilePath) otherwise null,
Result =
if TryFileAccess = null then
#table({"エラー"}, {{"指定されたファイルが見つかりません。"}})
else
// ... 後続のExcel.Workbook処理 ...
in
Result
同様に、`TargetSheetData`の取得部分でもエラーハンドリングを適用できます。
4. **VBAとの連携強化:**
* **自動更新のトリガー:** Excelのセル値を変更しただけでは、Power Queryは自動的に更新されません。「データ」タブの「すべて更新」ボタンをクリックする必要があります。これを自動化するには、VBAと連携させるのが効果的です。VBAでセルの値を変更した後、`ActiveWorkbook.Connections(“Query – クエリ名”).Refresh`のようなコードを実行することで、Power Queryの更新をトリガーできます。
* **ユーザーインターフェース:** パスやファイル名を入力するためのユーザーフォーム（UserForm）をVBAで作成し、その入力値をExcelシートの特定のセルに書き込むことで、より洗練されたユーザーインターフェースを提供できます。これにより、ユーザーはExcelシート上のセルを直接編集することなく、安全かつ直感的にデータソースを指定できるようになります。
5. **応用例:**
* **複数ファイルの動的結合:** Excelシートに、結合したい複数のExcelファイルのパスとファイル名をリスト形式で入力し、それをPower Queryでテーブルとして読み込みます。その後、M言語の`List.Accumulate`やカスタム関数を使って、リスト内の各ファイルからデータを動的に取得し、結合することができます。
* **環境ごとの設定:** 開発環境、テスト環境、本番環境でデータソースのパスが異なる場合、環境名をセルに指定し、M言語内で`if`文を使って環境に応じたパスを切り替えることができます。
これらの実務アドバイスは、単に動的なデータソース指定を実現するだけでなく、そのソリューションが堅牢で、使いやすく、そして保守しやすいものとなるよう支援します。
まとめ
本記事では、Power QueryのM言語とExcelセル値の連携を通じて、データソースのパス、ブック名、シート名を完全に動的に指定する画期的な方法を解説しました。`Excel.CurrentWorkbook()`関数を核として、Excelシートに定義された名前付き範囲から必要な情報をM言語で抽出し、それらを結合して動的なデータソースパスを構築する一連のプロセスを詳細に説明し、具体的なサンプルコードを提供しました。
これにより、これまで手動で修正せざるを得なかったデータソースの変更作業から解放され、ファイルが移動したり、ファイル名やシート名が変わったりしても、Excelシート上のセル値を更新するだけで、Power Queryが自動的に新しいデータソースに接続できるようになります。これは、データ収集プロセスの自動化と、レポートやダッシュボードの保守性向上に大きく貢献する、極めて強力なテクニックです。
さらに、実務におけるセキュリティ、パフォーマンス、エラーハンドリングといった側面から具体的なアドバイスを提供し、VBAとの連携によるさらなる自動化の可能性についても言及しました。これらの知見を活用することで、皆さんのPower Query活用は新たな次元へと進化し、より柔軟で、堅牢なデータ分析基盤を構築できるようになるでしょう。
Power QueryのM言語は奥深く、その可能性は無限大です。今回
