Power Query 第16回目です。
前回はテーブルを使った「ソース」の設定方法について勉強しました。テーブルを使う方法の応用についてさらに勉強していきます。
そういえばソースファイルって、集計するExcelファイルと同じ場所だったり、そのサブフォルダーに置いたりすることが多いですよね。なんとか応用できませんか？
今回もよろしくお願いしますm(__)m
前回はテーブルを使ってフォルダーのパスを設定してみました。使い方の基本的な方法についてはわかったと思いますので、もう少し応用できないかを検討していきたいと思っています。
確かにExcelファイルのすぐ近くといえば「サブフォルダ」を作って格納することが多いですね。それでは、その点をうまく活用できるように考えてみましょう。
【この記事でわかることは】
・自ブックのパスをテーブルにセットして活用する方法がわかります
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容はテーブルを使った「ソース」の設定方法でした。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/
自ブックの保存先変更に対応する方法
では、Excelの関数を使って保存先を取得しましょう。
Excel の CELL 関数を使います
・CELL関数 の使い方を確認するため Office のサポートを検索してみました。
・「現在の Excel ファイルの名前、パス名、またはワークシート名をセルに挿入する」なんていう、そのまんまのページがヒットしました。
CELL 関数とは
CELL 関数は、セルの書式、位置、または内容に関する情報を返します。
書式：CELL(検査の種類, [対象範囲])
【引数】検査の種類（必須）、対象範囲（省略可能）
・検査の種類は12個ありますがここで使うのは “filename” です。
セルに「= CELL( “filename” )」のように入力します。
“filename” は対象範囲を含むファイルのフルパス名 (文字列)を返します。
CELL 関数を使ってみます
・セルC2に「 =CELL(“filename”) 」を入力した結果画像です。
![CELL関数で自ブックのフルパス＋[ブック名]＋シート名を取得](https://www.kurumico.com/wp-content/uploads/2021/07/pq-mybookpath-03-800x243.jpg)
・フルパス＋[ブック名]＋シート名 が表示されています。
・でも、加工しないとこのままでは使えないですね。
・ [ブック名]＋シート名 は不要なので外す方法を考えます。
取得できた文字列から不要な部分をカットします
・LEFT 関数（文字列の左側から指定文字数分取得）を使って文字列を抜き出します。
・指定文字数の取得は、FIND 関数（文字列から指定文字の位置を取得）を使います。
・検索する文字は、[ブック名] の左側 ” [ ” の文字です。
=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)
・見つかった ” [ ” の位置の１文字手前までを抜き出すので「-1」しています。
・下の画像が、この式を入力した結果です。
・うまくいきました(^^♪
・では次は、これをテーブルに設定していきます。
テーブルの設定を手直しします
ソースデータの場所を自ブックのサブフォルダに設定します
・ソースファイルのあるフォルダを自ブックのサブフォルダで設定してみましょう。
・テーブル「ソースパス」をドラッグして下側に移動しました。
・ついでにテーブル名を「Source」に変更してフォルダー名「CSV」だけ入力しました。
・「mypath」という名前のテーブルを作成しました。
・見出しは「MyPath」とし、A2セルには自ブックのパスを取得する次の式を入力しています。「=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1) 」
Power Query M言語 コードを設定します
let 以下の追加・変更した部分だけ抜粋しました
MyPath = Excel.CurrentWorkbook(){[Name=”mypath”]}[Content]{0}[MyPath],
SourceFolder = Excel.CurrentWorkbook(){[Name=”source”]}[Content]{0}[Source],
ソース = Folder.Files(MyPath & SourceFolder),
・MyPath で “mypath” テーブルの最初の行 [Content]{0} から自ブックのパスを取得しています。
・ SourceFolder は “source” テーブルの最初の行 [Content]{0} からフォルダー名を取得しています。
・ (MyPath & SourceFolder) は自ブックと「＆」で連結してサブフォルダのパスにしています。
データ更新して動作確認してみます
・ソースフォルダを自ブックのサブフォルダー移動して更新した結果がこちらの画像です。
・問題なく更新できています。
・今後は、自ブックのパスは自動取得してくれるので、一緒にサブフォルダーも移動するだけで設定を何も変更せずに動作できるようになりました。
・この方法を活用すればもっといろいろなことができそうですね(^^)
まとめ（おわりに）
・以上で自ブックのパスをExcel 関数で取得し「テーブル」にセットして活用する方法についての勉強は終了です。
・でも、もう少し活用事例を増やしていきたいと思いますので、次回に継続することとしましょう。
・以上、ここまでの内容が参考になったなら幸いです(^^ゞ
・今回記事で使ったサンプルデータはダウンロードページに登録しておきます。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
自Excelブックのパスを取得する方法と「データソース」をサブフォルダーに設定する方法を勉強しました。自ブックとサブフォルダーをセットで移動すれば設定変更の必要がなくなりました。いかがでしたか？
次回もこの手法の応用を深めましょう。楽しみに待っていてね(^^)/
これですね！
これならPC環境が変わっても設定し直さなくていいのでバッチリです(^^♪
どんどん便利になっていきますね！ つぎも楽しみです(^^)
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事のサンプルファイルを登録しています！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
【今回わかったことは】
・自ブックのパスをテーブルにセットして活用する方法がわかりました