【Excel】Power Queryの接続先を一括変更する!Excelのファイルパス移動時のリンク修正手順

ExcelでPower Queryを使用して外部データを取り込んでいる場合、元のファイルが移動するとリンクが切れてしまいます。ファイルパスの変更が多数あると、一つずつ手作業で修正するのは非常に手間がかかります。この記事では、Power Queryの接続先を効率的に一括変更する手順を解説します。これにより、ファイル移動後のデータ連携をスムーズに再開できます。

Power Queryでは、外部ファイルへの接続情報がクエリ設定に保存されています。ファイルパスが変更された場合、この接続情報を更新する必要があります。手動での更新は、接続先が多い場合に膨大な時間を要します。しかし、Excelの標準機能や簡単な操作で、この作業を劇的に効率化できます。本記事で紹介する手順を理解すれば、ファイルパスの変更によるPower Queryのリンク切れ問題を迅速に解決できるようになります。

Power Queryにおけるファイルパスの仕組み

Power Queryは、外部データソースへの接続情報をクエリ内に保持しています。この接続情報には、ファイル名だけでなく、そのファイルが存在するフォルダのパスも含まれます。外部ファイルへの接続設定は、通常、Excelファイル、CSVファイル、データベースなど多岐にわたります。

データソースがExcelファイルの場合、Power Queryでは「Excelブック」として認識され、ファイルパスが設定されます。このパス情報が、Excelファイル内の特定の場所に記録されているため、ファイルが移動したり、ファイル名が変更されたりすると、Power Queryは元の場所でファイルを見つけられなくなり、リンク切れが発生します。

Power Queryの接続先を修正する手順

Power Queryの接続先ファイルパスを変更するには、主に2つの方法があります。一つは、Power Queryエディター内で個別に接続情報を修正する方法です。もう一つは、Excelファイル自体に保存されているPower Queryの接続情報を置換機能を使って一括で修正する方法です。

接続先の数が少ない場合は、Power Queryエディターで個別に修正する方が直感的かもしれません。しかし、接続先が多数にわたる場合は、後者の置換機能を利用する方が圧倒的に効率的です。どちらの方法も、Excelのバージョンによっては若干操作が異なる場合がありますが、基本的な考え方は共通しています。

方法1: Power Queryエディターで個別に接続先を変更する

この方法は、接続元のファイルが少数である場合や、どの接続先を変更すべきか明確な場合に適しています。クエリエディターを開き、該当するクエリの接続設定を直接編集します。

1. Excelファイルを開く
   Power Queryの接続情報が含まれるExcelファイルを開きます。
2. Power Queryエディターを開く
   「データ」タブをクリックし、「データの取得と変換」グループにある「テーブルまたは範囲から」を選択します。または、既存のPower Queryクエリがある場合は、「クエリと接続」ウィンドウから該当するクエリを右クリックし、「編集」を選択します。これにより、Power Queryエディターが開きます。
3. 接続設定を編集する
   Power Queryエディターの左側にある「クエリ」ペインで、接続先を変更したいクエリを選択します。次に、リボンの「ホーム」タブにある「ソースの編集」ボタンをクリックします。
4. ファイルパスを更新する
   表示される「ソースの編集」ダイアログボックスで、現在のファイルパスを確認します。必要に応じて、新しいファイルパスに手動で修正するか、「参照」ボタンをクリックして新しいファイルの場所を選択します。
5. 変更を適用する
   新しいファイルパスを入力または選択したら、「OK」をクリックしてダイアログボックスを閉じます。その後、Power Queryエディターのリボンにある「閉じて読み込む」ボタンをクリックして、変更をExcelシートに反映させます。

方法2: Excelファイル内の接続情報を置換機能で一括変更する

この方法は、多数のPower Query接続先のファイルパスを一度に変更したい場合に非常に有効です。Excelファイル自体に埋め込まれた接続情報を、テキストの置換機能を使って修正します。この操作は、Excelファイルの内容を直接編集するため、実行前に必ずファイルのバックアップを取ることを強く推奨します。

1. Excelファイルのバックアップを作成する
   編集対象のExcelファイルをコピーし、別の場所に保存しておきます。万が一、置換操作で問題が発生した場合に、元の状態に戻せるようにするためです。
2. Excelファイルを「Excel 97-2003ブック」形式で保存する
   Power Queryの接続情報は、Excelファイル(.xlsx)の内部構造に保存されています。この内部構造をテキストとして編集可能にするために、一度「Excel 97-2003ブック(.xls)」形式で保存し直します。ファイルタブから「名前を付けて保存」を選択し、「ファイルの種類」で「Excel 97-2003ブック」を選んで保存します。
3. テキストエディタでファイルを開く
   保存した.xlsファイルを、メモ帳などのテキストエディタで開きます。ファイルサイズが大きい場合、テキストエディタの動作が遅くなることがあります。
4. ファイルパスを検索・置換する
   テキストエディタの検索・置換機能(通常Ctrl+H)を開きます。「検索する文字列」に、古いファイルパス(例: C:\OldData\)を入力します。「置換後の文字列」に、新しいファイルパス(例: D:\NewData\)を入力します。
5. すべて置換を実行する
   「すべて置換」ボタンをクリックして、ファイル内の古いファイルパスをすべて新しいファイルパスに置き換えます。置換が完了したら、ファイルを保存します。
6. Excelファイルを元の形式に戻す
   テキストエディタで保存した.xlsファイルを、再度Excelで開きます。そして、ファイルタブから「名前を付けて保存」を選択し、「ファイルの種類」で元の「Excelブック(.xlsx)」形式を選んで保存します。
7. Power Queryの接続を確認する
   Excelファイルを開いたら、「データ」タブの「クエリと接続」ウィンドウを開きます。各クエリを右クリックして「編集」を選択し、「ソースの編集」でファイルパスが正しく更新されているか確認します。その後、「閉じて読み込む」をクリックして、データが正しく取得できるか確認します。

Power Queryの接続先一括変更における注意点

Power Queryの接続先を一括で変更する作業は非常に便利ですが、いくつかの注意点があります。これらの点に留意することで、作業をスムーズに進め、予期せぬトラブルを防ぐことができます。

ファイルパスの完全一致を確認する

置換機能を使用する際は、検索する文字列(古いファイルパス)と置換後の文字列(新しいファイルパス)を正確に入力することが重要です。パスの末尾の「\」やフォルダ名のスペルミスがあると、意図した通りに置換されず、リンク切れが解消されない可能性があります。

例えば、「C:\Data」と「C:\Data\」では意味が異なります。また、大文字・小文字の違いも、システムによっては区別される場合があるため注意が必要です。置換前に、古いパスを正確に把握しておきましょう。

Excel 97-2003ブック形式への変換リスク

方法2で.xls形式に変換する際、一部の最新のExcel機能や書式設定が失われる可能性があります。特に、複雑な数式、条件付き書式、グラフなどが、変換によって意図しない表示になることがあります。そのため、この方法はPower Queryの接続情報のみを編集する目的で使用し、編集後は速やかに.xlsx形式に戻すことが推奨されます。

また、.xls形式はセキュリティ上のリスクも.xlsx形式より高いため、編集が終わったらすぐに元の形式に戻すか、不要になった.xlsファイルは削除してください。

接続元がExcelブック以外の場合

今回解説した方法は、主にExcelファイル自体を接続元としているPower Queryクエリに有効です。CSVファイル、Webサイト、データベースなど、他の種類のデータソースに接続している場合、接続情報の保存形式や編集方法が異なることがあります。

例えば、CSVファイルの場合、Power Queryエディターでファイルパスを直接編集する手順は有効ですが、Excelファイル内の置換機能では修正できない可能性があります。データベース接続の場合は、接続文字列の変更が必要になるなど、より専門的な知識が求められることがあります。接続元の種類に応じて、適切な修正方法を選択してください。

Power Queryのバージョンによる違い

Power QueryはExcelのバージョンアップとともに進化しており、機能や操作画面が若干異なる場合があります。特に、Excel 2016以前のバージョンでは、Power Queryがアドインとして提供されていたり、画面構成が現在と異なったりします。

しかし、基本的な接続情報の編集方法や、.xls形式での置換アプローチは、多くのバージョンで共通して利用できます。もし画面表示が異なる場合は、お使いのExcelバージョンのヘルプを参照するか、オンラインで「Power Query 接続先 変更 [お使いのExcelバージョン]」といったキーワードで検索すると、より具体的な情報が見つかるでしょう。

機能	Power Queryエディターでの個別編集	.xlsファイルでの一括置換
対象	個別のクエリ接続	Excelファイル内の全Power Query接続
作業時間	接続数が多いと時間がかかる	接続数が多い場合に短縮可能
難易度	比較的容易	.xls形式への変換・編集に注意が必要
リスク	少ない	書式・機能の喪失、操作ミスによる破損リスクあり
推奨ケース	接続数が少ない、修正箇所が明確	接続数が非常に多い、ファイルパスのパターンが一定

まとめ

この記事では、ExcelのPower Queryで接続先のファイルパスが変更された場合の、一括修正手順を解説しました。Power Queryエディターでの個別編集と、Excelファイルを.xls形式で保存してテキストエディタの置換機能を使う方法の2つを紹介しました。

多数の接続先がある場合は、.xls形式での一括置換が作業時間を大幅に短縮できます。ただし、この方法を実行する際は、必ずファイルのバックアップを取り、パスの正確性を確認することが重要です。これらの手順を理解し、適切に活用することで、ファイル移動後のデータ連携問題を迅速に解決し、業務効率を向上させることができます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】