Power Query(パワークエリ)でデータソースをエクセルのワークシート上のセル入力値で可変に設定する方法を紹介します。
ここでは、ファイルパス情報のテーブルで準備して、それを参照してクエリを作成する方法を解説しています。複数のファイルパス設定にも利用できます。
データソースをセル値により動的に設定する
データソースをセル値により可変設定する手順は下記の通りです。
- ファイルパス設定用のテーブル(入力用セル)を作成する
- ファイルを読み込んでクエリを作成する
- クエリのファイルパスをセル入力値に変更する
下図のデータを使用して設定手順を解説します。データソースとして「データA」「データB」「データC」とそれぞれのファイルの値を2倍にした「データA2」「データ2」「データC2」の6個のエクセルファイルを準備しています。

まずはファイルパスを入力するためのテーブルを一つ作成します。
- クエリを作成するエクセルファイル上に列名とファイルパス(必要に応じてシート名も)を入力して範囲選択する。
- 挿入タブから「テーブル」を選択する。
- 「先頭行をテーブルの見出しとして使用する」にチェックを入れてOKをクリックする。

テーブル作成後は任意でテーブル名を必要に応じて設定してください。そのままでも問題ないです。

次に実際にファイルを読み込みます。
データタブの「データの取得」 > 「ファイルから」 >「ブックから」をクリックして 読み込むファイルを選択する。準備したファイルの内、読み込むファイルやシートはどれでもOKです。

ここでは、データAのファイルを読み込んでいます。

ファイルパス設定用のテーブルの値をクエリに適用させます。テーブルの値を利用するには以下の方法を利用します。
エクセル上にあるテーブルの値を参照するには下記の式を用います。※行番号は0始まりです。
Excel.CurrentWorkbook(){[Name=”テーブル名“]}[Content][テーブルの列名]{行番号}
例として、テーブルの1行目のファイルパスを読み込む場合は以下のように記載します。
Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][ファイルパス]{0}

- 適用したステップにある「ソース」を選択する。
- 数式バーのファイルパス部をテーブル参照式に置き換えます。ここではデータAのファイルを読み込むので下記の式に置き換えます。
Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][ファイルパス]{0}


ファイルパスと同様の方法で読み込むシート名を設定します。シート名を動的設定する必要がない場合は飛ばしてしまってOKです。
- 適用したステップにある「ナビゲーション」を選択する。
- 数式バーのシート名部をテーブル参照式に置き換えます。ここではデータAのシートを読み込むので下記の式に置き換えます。
Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][シート]{0}


以上でデータソースの動的設定は完了です。
同様の方法で「データB」と「データC」のファイルを読み込むクエリBとクエリCを作成します。動作確認としてファイルパスの設定値を下図のように変更すると、読み込む値が変わるのがわかります。


データソースのテーブル構成が変わるとエラーが発生する可能性がありますので注意してください。「列名」が変わりエラーが出ている場合は、ステップ内にある「昇格されたヘッダー数」を削除すればエラーが回避できる可能性がありますので試してみてください。
Sponsored link
エラーの対処方法
ファイルパスを動的設定すると下記のエラーが発生することがあります。
クエリは他のクエリまたはステップを参照しているため、データ ソースに直接アクセスできません。
その場合は、プライバシーレベルの設定を下記の手順で変更するとエラーを回避できます。
「ファイル」→「オプションと設定」→「クエリのオプション」を選択する。

「現在のブック」→「プライバシーレベル」→「プライバシーレベルを無視すると、パフォーマンスが向上する場合があります」にチェックをしてOKをクリックする。

Sponsored link
まとめ
今回は、ファイルパス情報を別テーブルで準備して、それを参照することでデータソースを動的に設定する方法を紹介しました。手順は以下の通りです。
- ファイルパス設定用のテーブル(入力用セル)を作成する
- ファイルを読み込んでクエリを作成する
- クエリのファイルパスをセル入力値に変更する
パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。
・初心者向けおすすめ学習本
・初中級者向けおすすめ学習本
コメント
コメント一覧 (1件)
データソースがURL(表データで複数)を取り込みたい場合、どうすればよいか調べたところ、記事が検索されました。ファイルパスについて参考になりました。ありがとうございます。