【PowerQuery/Tips】相対パスでファイルやフォルダを読込む方法

PowerQueryでファイルやフォルダを読込む際に、絶対パスではなく相対パスで読込む方法について投稿します。

通常のGUI操作でデータを読込んだ時には、データファイルの指定は絶対パスになっています。読込元ファイルとデータファイルが同じ階層にある場合などで、ファイルを一緒に場所移動したいケースはあろうかと思います。

そのまま移動してしまうと、データファイルのパスが変わってしまうためにクエリによるデータ更新は動かなくなってしまいます。

相対パス化しておけば、場所を移動してもデータ更新できるようになります。

パワークエリで相対パスでファイルやフォルダを読込む方法
1. 通常の方法で読込むと…
2. 相対パスでデータを読込む手順
まとめ

パワークエリで相対パスでファイルやフォルダを読込む方法

サンプルのファイル構成は次の通りです。クエリが含まれたファイルとデータファイルがサブフォルダを挟んで同じ階層に存在するケースとします。

.¥Folder
    |
    |--- クエリが含まれた読込元ファイル.xlsx
    |
    |---.¥data
        |
        |--- データファイル.csv

通常の方法で読込むと…

パワークエリはファイルやフォルダでデータを読み込む際、絶対パスとして読み込みます。

相対パス化せず、通常の状態では「Folder」の場所を変更するとクエリが含まれたファイルからのデータ更新は出来なくなります。

パワークエリでは読込先データの場所を変更するとデータが読み込まれなくなる — データ更新時のエラー

上記のエラーは、クエリエディター上で移動後のファイルパスを絶対パスとして指定しなおせばエラーは解消されます。

アレコレメモ

ただ、ファイルの最終的な場所を決めておらず、ローカルでクエリ処理を先に作ることはあろうかと思います。準備ができて、正規の場所に移した場合にもクエリが含まれたファイルを特に触らずに動作させたいですよね。

相対パスでデータを読込む手順

ステップ1
パワークエリが含まれたファイルのフォルダパスを取得する
ステップ2
フォルダパスの記述箇所をテーブル化（名前定義）する
ステップ3
対象のデータを読み込む
ステップ4
パワークエリのM言語コード部分を修正する

1. フォルダパスを取得する

読込元ファイル（ここでは「クエリが含まれた読込元ファイル.xlsx」）の任意シート内のどこでもいいので関数で、読込元ファイルが存在するフォルダのパスを取得します。

運用管理面でわかりやすくするために、専用のシートを作るとよいでしょう。ここでは「初期設定」というシートを準備します。

パワークエリで相対パスでファイルやフォルダを読込む方法（フォルダパスを取得する）

A1に、パワークエリで変数として取得する列名「filePath」と入れておきます。A2にはフォルダパスを取得する関数を入れます。

=LEFT(CELL("filename",$A$2),FIND("[",CELL("filename",$A$2),1)-1)

セル番号を同じにすれば、基本コピペでよいです。いちおう分解して解説しておきます。

「CELL(“filename”,$A$2)」という部分でファイルのフルパスとシート名が取得されます。ファイル名部分は「鉤括弧 [ ] 」で囲まれていますね。

（表示例） C:¥folder¥[クエリが含まれた読込元ファイル.xlsx]Sheet1

上記表示からフォルダパス部分のみ（「C:¥folder¥」）を取り出したいという話になります。

かぎ括弧「“[“」の前までが欲しいので、FIND関数でかぎ括弧「“[“」を見つけて、LEFT関数でかぎ括弧「“[“」の直前までを取り出します。

この関数は、ローカルやファイルサーバーなどのWindows環境下、OnedriveやSharepointなどのオンライン保存領域でも同じようにパスを取得できます。

2. パスの記述箇所をテーブル化（名前定義）する

フォルダパスを取得したらA1とA2のセルをテーブル化します。

アンナ

「テーブル化」というのは、Excelに明示的に「この範囲はきちんとした構造データです！」と伝えるおまじないよ。

テーブル化することにより名前定義されますので、あとからパワークエリでテーブル名と列名称で指定できるようになります。

テーブル化せずに、セル範囲に名前定義するやり方もあります。

テーブル化のほうが手っ取り早く、名前定義（テーブル名称）を視覚的に見つけやすいのでこの記事ではテーブルを使ったやり方をご紹介します。

パワークエリで相対パスでファイルやフォルダを読込む方法（テーブルによる名前定義） — ホームタブの「テーブルとして書式設定」

▲ テーブル化は、GUIならホームタブの「テーブルとして書式設定」、ショートカットなら「CTRL+T」（Macは「CMD+T」）で指定することができます。

▲ テーブル化した後は、デザインタブ「テーブル名」にテーブル名称が入ります。

通常は自動で「テーブル<番号>」の形式で名称が入ります。

今回は、パワークエリ・エディターで上記の名称をコード記述しますのでなるべくわかりやすいテーブル名に付け替えたほうがよいです。ここではテーブル名称を「setup」としておきます。

アレコレメモ

これに限らず、パワークエリやピボットテーブルを扱う際にはテーブルに名称をつけるクセをつけておいたほうがよいです。

ここまでで初期設定を整理します。

シート名は「初期設定」
テーブル名は「setup」
列名称は「filePath」

これらの名前は全くもって任意に決めてもらって構いません。テーブル名と列名はこの後に使用します。

3. 対象のデータを読み込む

ここまでできたらエクセルで取り扱う予定であったデータを読み込みます。このステップ以前に読み込んだデータであれば、このステップは読み飛ばしても結構です。

ここでは読み込み元の一つ下の階層にCSVがある前提で進めます。

.¥Folder
    |
    |--- クエリが含まれた読込元ファイル.xlsx
    |
    |---.¥data
        |
        |--- データファイル.csv

ひとつ下の階層のCSVファイルを読み込むということでやり方は二つあります。

単一のCSVファイルを指定する
フォルダを開いてCSVファイルをまとめて指定する

どちらのやり方でも、今回の方法で相対パスによる指定は可能となります。

今回のようなフォルダ構成ではCSVファイルが増加していくことを想定しています。フォルダを開いてCSVをまとめて指定する方法がよいでしょう。

4. M言語部分を修正する

絶対パスで記述されたコードを修正するために、パワークエリ・エディターを起動します。

パワークエリで相対パスでファイルやフォルダを読込む方法（詳細エディターでM言語マクロコードを変更する）

▲ ホームタブより「詳細エディター」を開きます。パワークエリのマクロはこの中にステップ毎に定義されています。

▲上記は、単一のCSVファイルを指定して開いた場合のM言語マクロコードです。「Csv.Document()」という関数で絶対パス指定でファイルが開かれています。

▲上記は、フォルダを開いてCSVファイルをまとめて開いた場合のM言語マクロコードです。「Folder.Files()」という関数で絶対パス指定でフォルダが開かれています。

CSVファイルやエクセル、またはフォルダでもデータを読み込むための関数は多少異なりますが、修正する要領は同じです。

アレコレメモ

1行目の「let」、2行目の「ソース」の間に追加の行を入れます。その後、3行目に繰り上げになる「ソース」部分を一部修正します。

1行目の「let」、2行目の「ソース」の間に行を追加する

以下の行を追加します。

ファイルパス = Excel.CurrentWorkbook(){[Name="setup"]}[Content]{0}[filePath],

この構文の意味は、左から順に解読していくと以下のようになります。

現在のワークブックを開いて
「setup」と名前定義された空間を呼び出して
コンテンツとなるテーブルを開いて
（ヘッダー部分を除いた）1行目を指定して
「filePath」列のみを取り出す
- 結果としてファイルパス部分（今回の場合は「C:\folder\」）となる
- 上記を「ファイルパス」という変数に入れる