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

powerquery-relative-pathモダンExcel

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

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

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

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

スポンサーリンク

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

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

.¥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に明示的に「この範囲はきちんとした構造データです!」と伝えるおまじないよ。

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

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

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

パワークエリで相対パスでファイルやフォルダを読込む方法(テーブルによる名前定義)
ホームタブの「テーブルとして書式設定
パワークエリで相対パスでファイルやフォルダを読込む方法(テーブルによる名前定義)
ショートカットなら「CTRL+T」(Macは「CMD+T」)

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

パワークエリで相対パスでファイルやフォルダを読込む方法(テーブルによる名前定義)

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

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

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

アレコレメモ
アレコレメモ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

アレコレメモ
アレコレメモ

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

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

以下の行を追加します。

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

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

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

行を追加したら、2行目から3行目に繰り上げになった「ソース」で始まる行を修正します。あとは、わかりますよね。絶対パスで指定されたフルパスの一部を「ファイルパス」変数に置き換えます。

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

▲ 「ファイルパス」変数と最終的な読込先のデータファイルをアンパサンド(&)で連結します。

読込先のデータファイルはハードコーディングが残っていることから、このファイル名称を変更してしまうと読み込みエラーとなるのでそこは頭の片隅に入れておきましょう。

これで、読込元・読込先の構成を変更しない限りはフォルダを変更してもエラーは出なくなります。

スポンサーリンク

まとめ

パワークエリで相対パスでファイルやフォルダを読込む方法について投稿しました。

Excelファイル内にフォルダ場所を記述する、というのは少し特殊な操作にも思えます。もう少しGUI上からスマートな操作で同じことが実現できるようにアップデートしてもらいたいものです。

Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本
翔泳社
パワークエリやパワーピボットといったExcelの新機能を利用して、データ集計・分析を「自動化」する方法を解説が7つのステップで解説してくれますデータとロジックが分離されるので、今までの次元の異なる、圧倒的な業務改善が可能になります。
Excelパワークエリ データ収集・整形を自由自在にする本
翔泳社
日本で初めてとなるMicrosoft Power Query(パワークエリ)専門の書籍です。例をなぞってお終いではなく、パワークエリを使ったデータ変換の原理・原則を理解し、自由自在に応用できる基礎を身に着けることができます。
Excelピボットテーブル データ集計・分析の「引き出し」が増える本
翔泳社
ピボットテーブルの専門書籍ならコレできまり!ピボットテーブルの基本から応用まで網羅、さまざまな集計・分析の方法を知ることができ、引き出しが増える、集計前のデータ整理や、ピボットテーブルの構造も理解できる。
ピボットテーブルも関数もぜんぶ使う!Excelでできるデータの集計・分析を極めるための本
ソシム
Excelで実際に高度なデータ分析を行なっている現役社員が解説するエクセルデータ分析の専門書籍。集計作業に費やす時間を大幅に短縮するためのテクニックを、そして「質」の高い分析結果を導き出すためのノウハウが徹底的に追求されている。
翔泳社ならPDFで書籍が購入できる!

紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群!!

\ お得なポイントバック! /

全国送料無料!IT書、ビジネス書、資格書が豊富なSEshop

「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。

\ 10%OFFクーポン配布中!! /
タイトルとURLをコピーしました