PowerQuery データソースをセル入力値により動的に設定する

Power Query(パワークエリ)でデータソースをセル入力値で可変に設定する方法を紹介します。ここでは、ファイルパス情報を別テーブルで準備して、それを参照してクエリを作成する方法を解説しています。複数のファイルパス設定にも利用できます。

目次

データソースをセル値により動的に設定する

データソースをセル値により可変設定する手順は下記の通りです。

  1. ファイルパス設定用のテーブル(入力用セル)を作成する
  2. ファイルを読み込んでクエリを作成する
  3. クエリのファイルパスをセル入力値に変更する
STEP
データ準備

下図のデータを使用して設定手順を解説します。データソースとして「データA」「データB」「データC」とそれぞれのファイルの値を2倍にした「データA2」「データ2」「データC2」の6個のエクセルファイルを準備しています。

STEP
ファイルパス設定用のテーブルを作成する

まずはファイルパスを入力するためのテーブルを一つ作成します。

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

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

STEP
クエリを作成する

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

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

STEP
ファイルパスを動的設定する

ファイルパス設定用のテーブルの値をクエリに適用させます。テーブルの値を利用するには以下の方法を利用します。

テーブルの値の参照方法

エクセル上にあるテーブルの値を参照するには下記の式を用います。※行番号は0始まりです。

Excel.CurrentWorkbook(){[Name=”テーブル名“]}[Content][テーブルの列名]{行番号}

例として、テーブルの1行目のファイルパスを読み込む場合は以下のように記載します。

Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][ファイルパス]{0}

  1. 適用したステップにある「ソース」を選択する。
  2. 数式バーのファイルパス部をテーブル参照式に置き換えます。ここではデータAのファイルを読み込むので下記の式に置き換えます。

Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][ファイルパス]{0}

STEP
シート名を動的設定する

ファイルパスと同様の方法で読み込むシート名を設定します。シート名を動的設定する必要がない場合は飛ばしてしまってOKです。

  1. 適用したステップにある「ナビゲーション」を選択する。
  2. 数式バーのシート名部をテーブル参照式に置き換えます。ここではデータAのシートを読み込むので下記の式に置き換えます。

Excel.CurrentWorkbook(){[Name=”ファイルパス設定”]}[Content][シート]{0}

STEP
設定完了&動作確認

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

注意点

データソースのテーブル構成が変わるとエラーが発生する可能性がありますので注意してください。「列名」が変わりエラーが出ている場合は、ステップ内にある「昇格されたヘッダー数」を削除すればエラーが回避できる可能性がありますので試してみてください。

Sponsored link

エラーの対処方法

ファイルパスを動的設定すると下記のエラーが発生することがあります。

クエリは他のクエリまたはステップを参照しているため、データ ソースに直接アクセスできません。

その場合は、プライバシーレベルの設定を下記の手順で変更するとエラーを回避できます

ただ、こちらの変更を行うとプライバシーレベルを無視するようになるのでデータの機密性は低下します。使用状況などを考慮して各自で判断して利用してください。

クエリのオプションを開く

「ファイル」→「オプションと設定」→「クエリのオプション」を選択する。

プライバシーレベルを変更する

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

Sponsored link

まとめ

今回は、ファイルパス情報を別テーブルで準備して、それを参照することでデータソースを動的に設定する方法を紹介しました。手順は以下の通りです。

  1. ファイルパス設定用のテーブル(入力用セル)を作成する
  2. ファイルを読み込んでクエリを作成する
  3. クエリのファイルパスをセル入力値に変更する
Power Queryのおすすめ書籍

パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。

初心者向けおすすめ学習本

初中級者向けおすすめ学習本

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

仕事、お金、旅行のことなどメモしてます。
仕事ではTableauを勉強中!
初心者にもわかりやすいように学んだことを発信していきます。

コメント

コメント一覧 (1件)

  • データソースがURL(表データで複数)を取り込みたい場合、どうすればよいか調べたところ、記事が検索されました。ファイルパスについて参考になりました。ありがとうございます。

コメントする

コメントは日本語で入力してください。(スパム対策)

CAPTCHA

目次