セルの値でクエリを変化させる(ファイル名やパス)
Power Queryでファイルを取得しているとき、後からファイル名やパスが変更されることがあります。そのたびにPower Queryエディタを起動して、ファイル名やパスを修正すればいいのですけれど、それって手間です。せっかくPower Queryで便利な仕組みを作成して第三者に渡したとしても、そうした変更のたびにユーザーは作業が止まってしまいます。こんなとき「新しいファイル名やパスは、このセルに入力してくださいね～」みたくできれば便利です。それを実現するのがパラメータクエリです。本稿では、主にファイル名やパスの変更に関して解説します。なお、そもそもパラメータクエリとは何かを、下記コンテンツで解説していますので、まずはそちらをご覧ください。
ファイル名やパスを変更する
まぁ、これはみなさん知っていると思いますけど、念のために。今回の動作では「C:\Work\Sample.csv」というファイルを使います。このとき「C:\Work」部分が"パス"で、「Sample.csv」部分を"ファイル名"と呼びます。参考までに、「C:\Work」みたいなドライブ名(ここではC)から始まり、ファイルが保存されているフォルダ(ここではWork)までをすべて記述したものを"フルパス"とも言います。また、パスとファイル名を合わせた「C:\Work\Sample.csv」を"フルネーム"と呼びます。さらに、パスを「C:\Work\」のように記述する方式が"絶対パス"です。最近、VBAの話題で、このへんを誤解している人が多いのですけど、ThisWorkbook.Path を使って、ブックが保存されているフォルダを自動的に取得するやり方は、"相対パス"ではありません。"絶対パス"です。"相対パス"というのは、たとえば「C:\Work\Data\2022\..\..\Sample.csv」みたいに「.」や「..」を使う記述です。ちなみに、この書き方は「C:\Work\Sample.csv」を表しています
さてさて、話をPower Queryに戻しましょう。C:\Workフォルダに2つのファイルを用意しました。「Sample1.csv」と「Sample2.csv」です。
まずは、Power Queryで「Sample1.csv」を取得して、Excelに読み込みます。
ここで指定した「Sample1.csv」を変更できるようにしてみましょう。
セルE2を変更してクエリを更新したら、セルE2に指定したファイル名を読み込みます。まずは、この入力欄をテーブルに変換します。
テーブルの名前を「FileName」に変更しました。この名前がクエリの名称になります。
さて、パラメータクエリを作るには、2つの方法があります。詳しくは「セルの値でクエリを変化させる(パラメータクエリの基本)」をご覧ください。今回のケースで、まずは簡単な
- パラメータを受け取る専用のクエリを使う方法
で試してみたのですが、これだと上手くいきませんでした。
やり方は間違えていないはずなのですが、どうしてもエラーになります。まぁ、データの大本であるファイルに関することですから、何らかの理由があるのでしょう。そのうち、Power Queryの偉い人に聞いてみます。なので今回は
- パラメータをM言語で取得する方法
こちらでいきます。Power Queryエディタでクエリ「Sample1」を開き、[詳細エディタ]を開きます。このへんの操作は「セルの値でクエリを変化させる(パラメータクエリの基本)」をご覧ください。詳細エディタで、次のようにコードを編集します。
取得したファイル名 = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[ファイル名],
行末のカンマ(,)を忘れないでくださいね。これで完成です。[閉じて読み込む]でExcelに戻ります。
セルE2のファイル名を変更してクエリを更新すると、下図のように別のファイルが読み込まれます。
今回は、セルE2にパスを含めたフルネームを入力しました。この手の処理でしたら、これが無難だと思います。もし、ファイルが保存されているフォルダは「C:\Work」固定で、ファイル名だけを変更したいときは、セルE2にファイル名だけを入力させて、
ソース = Csv.Document(File.Contents(取得したファイル名)
部分を、
ソース = Csv.Document(File.Contents("C:\Work\"&取得したファイル名)
などと工夫してください。
「フォルダーから」のフォルダを変更する
Power Queryには、指定したフォルダ内の複数ファイルを結合する「フォルダーから」という仕組みがあります。詳しくは「フォルダ内のデータを結合する」をご覧ください。ここで指定するフォルダを、セル内の値で変更するパラメータクエリをやってみましょう。
C:\Workフォルダに「1月」と「2月」というサブフォルダを作りました。それぞれのフォルダには、複数のCSVファイルが存在しています。
このフォルダ内のCSVを結合します。詳しい手順や操作は「フォルダ内のデータを結合する」をご覧ください。
[Source.Name]列は削除しました。これをExcelに読み込みます。
何度もやっているように、パラメータの入力欄をテーブルにします。テーブルの名前は「FolderPath」としました。
フォルダ内のファイルを結合するため、先に作成していたクエリ「1月」をPower Queryエディタで開きます。ちなみに、こういうときは「1月」というクエリ名を、別のものに変えた方がいいかもしれませんね。まぁ、どっちでもいいですけど。さて、クエリ「1月」を選択して[詳細エディタ]を開きます。このへんの手順も、上記と同じです。
取得したパス = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[フォルダ],
[完了]ボタンをクリックすると完了です。[閉じて読み込む]を実行してExcelに戻り、セルF2のフォルダ名を変更すると更新されます。されますけど、ちょっと、これだけでは正確性が乏しいです。フォルダ内の複数ファイルを結合するとき、Power Queryは、フォルダ内の「任意のファイル」を開き、そのファイルの"列構成"などを他のファイルにも適用します。今回でしたら「大阪.csv」です。
言うまでもなく、この「大阪.csv」は「C:\Work\1月\大阪.csv」です。たとえば、セルF2を「C:\Work\2月」に変更したとき、それでも「1月」フォルダの「大阪.csv」を参考にしてもいいのでしょうか？もし、すべてのフォルダのすべてのファイルが、必ず同一の列構成であるという確固たる前提があるのでしたら、これでも問題ありませんが、にしても「2月」フォルダのファイルを結合するのに「1月」フォルダのファイルを参考にするってのはどーなんでしょう？私としては、とても気持ちが悪いです。なので、ここも自動的に変更しましょう。ちなみに「フォルダーから」で複数ファイルを結合するとき、もしファイルによって列構成が異なっていると、正しいデータが取得されません。そのへんの話は
を、ご覧ください。
サンプルのファイルを取得するところは、「ヘルパークエリ」の「サンプルファイル」で行っています。このクエリ「サンプルファイル」を選択して[詳細エディタ]を開きます。やることは同じです。
取得したパス = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[フォルダ],
Excel上で動作を確認してみます。
セルF2のフォルダ名を変更して、クエリを更新します。
上手くいきました。ああ、ちなみに、サンプルファイルを作るのが面倒くさかったので、CSVファイル内の日付しか変えていません。手抜きです。すみません。