PowerQueryでデータの取り込みを行う場合に、どうしても発生するのが接続のメンテナンスです
単純にリンクの更新が主になります
またcsvファイルを社内Webシステム等からダウンロードする場合は、ダウンロードの名称が毎回違っていたり、所定の位置に上書き保存をしたりする必要があります
ここに関してはショートカットをうまく使うか、PowerAutomateDesktopを活用する方法で良いでしょう
今回の記事では、リンク切れや別のcsvファイルへ参照を変更したい場合の操作について解説を行います
2種類の方法がありますので、やりやすい方法で行ってください
ソースの変更を利用
まずはPowerQueryを開きます
取り込みデータのテーブル内にアクティブセルがある状態になれば、リボンにクエリタブが表示されます
その中の最左にある「編集」ボタンをクリックします
PowerQueryの編集画面が表示されたら、メニュー右側にある「データソース設定」を選択します
ブック内にある外部参照のリストが表示されます
ここで注意してほしいのが、編集中のソース以外にもここには表示されますので変更対象のソースであることを確認して、選択してから操作を進めてください
別の形式のファイルを指定してしまうと、ソースの取り込みステップにてエラーが発生します
エラー発生の場合は一度画面を変更を破棄して終了してください
変更対象を選択してから、画面下側にある「ソースの変更」を選択します
対象ファイルの変更を行うには、ファイルパス項目の参照ボタンからファイル選択ダイアログにて変更を行います
もし、文字コードに変更があった場合はここで修正可能です
変更が完了すればOKボタンを選択します
これで対象ファイルを変更することができます
詳細エディターを利用
ファイルパスだけであれば、絶対パス文字列を直接修正する方法も可能です
※他項目も変更可能ですが、理解していないと面倒です
PowerQuery編集画面のメニュー左側にある「詳細エディター」を選択します
この詳細エディターというのは、取り込みから各ステップの全ての内部コードを直接編集することが出来る機能です
黄色にハイライトした箇所が、データの絶対パスになるのでここに対象ファイルの絶対パスを拡張子まで含めて入力を行います
他の箇所を見てみると分かるように、各設定値もここで修正は可能です
例えば「Columns=15」というのはデータから15列を取り出すという意味になるので、ここを13とかにすれば取り込む列数を変更出来ます
取り込んでから削除しても良いですが、最初に取り込む量を減らすことが出来るのでデータの更新速度向上につながります
ただあくまでも左から何列という指定なので、歯抜けにしたいとか5列目だけとかは指定は出来ません
上記2点のいずれかの方法で、リンク切れになった際は対応を行ってください
更新頻度の設定について
外部データとの更新頻度も任意に設定可能です
ただあまり頻度が多いと他の作業に影響がありますので適当に調整します
初期設定では、更新を能動的に行う必要があります
更新を自動的に行う場合は、クエリタブの左側にある「プロパティ」を選択します
画像は初期状態の表示となっています
「バックグラウンドで更新する」
これは更新中もExcelの操作が可能となりますので、通常はオンにしておいて問題ありません
ただ、後述の自動更新を設定した場合にその更新が行われていることを明示的に使用者に分からせるにはあえてバックグラウンドで実行しない設定も可能です
「定期的に更新する」
データの更新を開いている間、一定時間ごとに更新を行います
例えばcsvファイルなどの更新頻度の低いものではなく、Web情報であったり、最新情報が欲しいExcelデータであった場合に活用できます
ただこの取り込んだデータをピボットテーブルなどで集計する場合、そちらまで自動更新されるわけでは無いので注意が必要です
「ファイルを開くときにデータを更新する」
ブックオープン時に更新を行います
データの取得が目的のファイルであれば有効にしておくと良いです
前回開いた時点のデータを確認した場面も多々あり得るので、基本はオフで問題は無いでしょう
サブオプションの「ブックを保存する前に外部データ範囲からデータを削除する」をオンにすることで取り込んだデータを保存しないのでファイルサイズの軽量化を行えます
あまりにも大きいデータの取得を行っている場合は有効です
「すべて更新でこの接続を更新する」
すべて更新というのは、データタブの真ん中くらいにある画像のボタンです
これをクリックすると、ブックに含まれるピボットテーブルや接続などの再更新を行います
このオプション設定は、これをクリックしたときに更新を有効とするかどうかの指定です
名前の通り、すべて更新したいときに使用するボタンなので、なるべくこの設定はオフにしない方が混乱はしないと思うのでそのままオンの設定で問題ありません
VBAの場合だと、エディター画面を開いて、プロシージャを探して、パスの箇所を見つけて修正、となるのでメンテナンス工数は圧倒的に違います
接続関係のVBAは無くなりますね、こりゃ