読者のみなさま！こんにちは！！Apachanです。
前回はパワークエリを活用することで、日々のルーチン業務がボタン1つで完了するようになった事例を紹介しました。
マウス操作だけで容易にデータの取得や加工が設定できるので、利用のハードルの低さを実感したのではないかと思っています。
今回は日々の運用の中でパワークエリが直面する課題とそれに対しての対策について考察していきます。
目次
・運用上の課題
・エラーの対策
・事前準備
・データの取得の変更方法
・相対パス変更後のテスト施行
まとめ
・運用上の課題
取得するデータの保存先の変更
日々の運用の中でフォルダの名前を変えたり、フォルダやファイルの格納先を変更することはよくあります。
一時的な業務引継ぎのために第三者とファイルを共有することもあるでしょう。
実はパワークエリを利用している場合はこのようなケースでは高い確率でエラーが発生します。
エラーが発生する原因は、パワークエリが取得するデータのパスが変わってしまうからです。
パワークエリを利用してデータを取得する際は固定のパスで読み取るため、読み取った時点の文字列が固定値として設定されます。
したがって、後からデータ取得先のパスを変わってくるとエラーが発生します。
残念ながら現時点では自動でパスを修正するような機能はないため、手動で相対パスに変更してあげる必要がありあす。
※ コンピューター内のフォルダやファイルなどがある場所をパスと呼びます。
一般的にはデータの取得先の保存先、格納先が変更になることをパスの変更と表現したりします。
取得するデータの保存先変更に伴うエラー発生を再現
データの取得先のパスを変更することでエラーが発生するかどうか試してみます。
デスクトップ上にフォルダ(PowerQuery)を作成してこの中にパワークエリを設定するファイル(データ加工.xlsx)と、
データ取得先のファイル(出荷情報.xlsx)を配置します。
この状態パワークエリのデータ取得対象のファイルを設定したと仮定します。
当然ですが、この状態でデータタブの「すべての更新」を押下してもエラーは発生しません。
読み込みファイルが更新されても問題なく定義通りにデータ更新は行えます。
意図的にデータ取得先のパスを変更するために、
デスクトップ上のPowerQueryフォルダをツール格納先という別のフォルダへ移動します。
PowerQueryフォルダはツール格納先の配下にパスが変更されます。
データタブより「すべての更新」を押下しましたがエラーが発生しました。
このようにエラーが発生した場合は毎回データの取得対象のファイルを設定し直す必要があります。
頻繁にパスが変わったり、第三者と共有する場合にはこの修正作業が手間になる可能性があります。
また、パスが変わらない想定でもごく稀にパスが変わってエラーが発生した際にエラーの特定に時間を要する可能性があります。
運用上起こりうることなので、原因と対策などは押さえておいた方がいいでしょう。
・エラーの対策
エラーの原因
パワークエリエディタを起動してエラーの原因となる箇所を特定します。
詳細エディターを利用するとデータ取得先のパスを一目で見ることができます。
多くのコード記述量がありあすが、実際にはletブロックの最初の行の「ソース = XXXX」が該当箇所となります。
下図の通り、固定の文字列となっているためパスが変更しても対応することができません。
取得するデータを固定値から変動値へ変更
要するにデータ取得先のパスが固定値から現在のパスを加味した変動値を設定すれば良いということになります。
そのための方法はいくつかありますが、今回はこちらのTwitter上で紹介されている方法を試します。
参考ソース：
PowerQueryのファイルパスの変更方法(絶対→相対パス)
・事前準備
今回利用するツール一式
前回作成したパワークエリを使います。実際に操作したい方は前回のブログを参考にしてください。
参考ソース：
Excel Power Query #4 データの取得
・データの取得の変更方法
① 現在パス取得のためのExcel関数を利用
パワークエリを利用するExcelファイルの中に現在のパスを取得するExcel関数を設定します。
注意点としては、パワークエリエディターの中でExcel関数は利用できませんので、シートを新規追加して関数を設定します。
configという名前のシートを新規作成いて、A1セルに以下関数を設定します。
戻り値として一階層上のフォルダ＋”\”まで反映するように作ります。
関数の詳しい意味が分からなくとも問題ありません。このファイルの現在のパスを取得するためのものだというくらいでOKです。
=LEFT(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1),1)-1)
② A1セルを呼び出し可能な状態に定義
現在のパスを取得したセルに名前を付けてパワークエリから呼び出し可能な状態にします。
FilePathという名前でブック内から呼び出し可能な状態にします。
設定が終わったらconfigシートは非表示にします。
シート上で右クリックをして非表示を選択してください。
運用上不必要なシートは非表示にすることを推奨します。
③ パワークエリの詳細エディターの編集
取得先データのパスを②で設定したパスを利用するようコード記述を変更します。
そのためにまずはパワークエリエディターを起動します。
パワークエリエディタが起動したら、リボンから詳細エディターをクリックして起動してください。
起動すると下図のような詳細エディターのダイアログが表示されます。
赤枠のコード記述を以下のように変更してください。
(変更前)
ソース = Excel.Workbook(File.Contents(“C:\Users\omik03\Desktop\PowerQuery\出荷情報.xlsx”), null, true),
(変更後)
ファイルパス = Excel.CurrentWorkbook(){[Name=”FilePath“]}[Content]{0}[Column1],
ソース = Excel.Workbook(File.Contents(ファイルパス & “出荷情報.xlsx”), null, true),
変更前のコードは行頭に//（コメントアウト）を付けると無効化することができます。
慣れるまではコメントアウトして残しておいてもいいかも知れません。
※ 今回はデータの取得対象がExcelとしていますが、CSVなど別のファイルの場合は記述方法が異なります。
詳しくは以下リンクを参照してください。
外部データ ソースからデータをインポートする (Power Query)
・相対パス変更後のテスト施行
デスクトップ上のダミーフォルダにツールを移動
冒頭の「運用上の課題」ではパスの変更に対しての対策を一切していなかったためエラーが発生しました。
しかし、今回は上述のように詳細エディターより相対パスを参照するようコード記述を変更しました。
エラーが発生するか否かあらためて確認してみます。
先ほどと同様にPowerQueryフォルダをツール格納先フォルダに移動して意図的にデータ取得先のパスを変更させます。
下図のように意図的にデータ取得先のパスを変更します。
同様にデータタブの「すべての更新」を押下します。
今回は相対パスを参照しているため正常に更新できました。
念のためconfigシートにて取得した現在パスを確認してみましょう。
一度非表示にしたシートは同様にシート上で右クリックをして再表示することが可能です。
A1セルを確認すると、ツール格納先に移動した後のパスに切り替わっています。
これは設定した関数が変更を加味した現在のパスを表示しているからです。
利用上の注意点
パワークエリを利用するファイルとデータ取得対象のファイルが同一フォルダの同一階層にある前提の記述方法となります。
同一フォルダ内でも階層が異なるとコード記述方法が変わるためご注意ください。
まとめ
パワークエリは手軽に実務で利用できますが、運用上想定される課題とその対策を知っておくとより安定感が増します。
今回のパスを取得する関数や参照するパスを変更するようなコード記述方法は分からなくとも一旦はコピペでできればOKです。
ゆくゆくはパワークエリの標準機能として相対パスが設定できるようになりますように！
