Excel Power Query ソース 設定にテーブルを使用

Power Query 第15回目です。
前回ソースデータやソースパスの基本的な変更方法について勉強しました。今回はその設定をテーブルを使って【汎用的】に行う方法について勉強します。

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・前回の内容は「ソース」の変更方法【基本編】です。

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

「ソース」をテーブルで設定する方法

・Power Query で設定した Excelファイルを Power Query を知らない自分以外のユーザーに渡して使ってもらう場合、データソースの設定を変更しなければならないでしょう。
・Power Query を知らないユーザーに前回記事の設定・変更方法を説明して実行してもらうのは非常に難しく面倒です。
・そこで、過去の記事で Excel VBA を汎用で動作させたように、「設定」シートに書いておいてた設定を変数に読み込ませる方法を検討していきます。

シートに設定テーブルを用意します

・シートの設定を順番に用意していきます。
・使用するサンプルは次の記事で使ったサンプルを使用していきます。←DL先のリンクです。

設定用のシートを用意します

・Excel に「設定」シートを追加します。
　※ここでは「設定」としていますが任意のシートでOKです。
・ 「設定」 シートのセル（任意の場所）に見出し「ソースパス」と入力しておきます。

「ソース」のパスをセルにセット（入力）します

・ 先ほど入力した「ソースパス」下のセルに「ソース」のパス（Path）を入力します。
・ サンプルは「フォルダーからのデータ取得」なので「フォルダパス」をフルパスで記述します。

入力したデータを「テーブル」にします

・「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
・テーブル名はそのままでもよいのですが、わかりやすくするため「Path」としました。
・このテーブル名は、この後の M言語の記述で使用することになります。

「設定」シートの設定は以上です。

テーブルのデータを取得するには

テーブルをクエリにして確認してみます

・Excel VBAの「マクロの記録」のような感じで、試しにクエリを作成してみます。

・作成されたクエリの「ソース」部分を確認すると次のようになっています。

Excel.CurrentWorkbook（）：現在のExcelブックのデータを使用するための PowerQuery 関数です。
{[Name =”Path”]}：名前付き範囲の名前＝ここではテーブル名です。
[Content]：これだけでは行と列の指定がありませんので、次のように指定する必要があります。
　　　　　 [Content] {0} [ソースパス] ： {行番号}[列名]の順で指定します。
　　　　　 テーブルの場合、行は “0” からスタートします。列は “1” からスタートです。
最後に “,” コンマで終了となります。

行数が2行なら [Content] {1} [ソースパス] となります。必要な数だけ作成できます。

Power Query 詳細エディターで関数を設定します

・変更前の Power Query M言語コードがこちらです。

・追加するのは次のコードです。「ソース」は使われているので「FolderPath」に変更しています。

・let の 直下に FolderPath = Excel.CurrentWorkbook(){[Content] {0} [ソースパス], 追加します。

・ソース = Folder.Files(“D:\CSV”) の下線部分を 変数 FolderPath に変更しました。

・追加・変更後に [完了] をクリックします。下の画像が変更後の状況です。

・ステップに [FolderPath] が追加されています。

・フォルダー内のファイルデータが正常に読み込まれて展開されているのが確認できました。

・テーブルの設定データが読み込まれていることは次の画像で確認できます。

・これでデータソースの場所を変更しても、テーブルの値を書き換えるだけでOKになりました。

まとめ（おわりに）

・以上で「ソース」パス設定を「テーブル」を使って変更可能にする方法についての勉強は終了です。

・今回は「フォルダーパス」を例にしましたが、ファイル指定の場合も基本的に同じ手法です。

・そのほかの パラメータ の設定にも同じように活用できそうですね。

・まとめると 変数 に テーブルのデータ を 代入 して 変数を使用していくということです。

・以上、参考になったなら幸いです(^^ゞ　

・今回記事で使ったサンプルデータはダウンロードページに登録しておきます。

・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルをリンク先に登録しています！

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください