Power Query でデータの更新ができなくて困ったことはありませんか？
例えば次のような症状が起きることがあります。
- エラーメッセージが出て処理が止まる
- 何も動いていないように見える
- データを追加したのに処理結果が変わらない
これらの問題はたった一つの対策であっさり解決できるケースがほとんどです。
この記事では Power Query で更新できない問題 について症状ごとの原因と対策を解説いたします。
Power Query のトラブルで悩まれている方は是非ご一読下さい。
症状1｜別のプロセスで使用されている エラー（ファイルロック）
よくあるエラーメッセージ1
Power Query で更新しようとしたら次のようなメッセーが出て処理が止まってしまうことがあります。
[Data.Source.Error] 別のプロセスで使用されているため、プロセスはファイルにアクセスできません
「別のプロセス」というのは良く分からない方も多いと思いますが、これは他で動かしているウィンドウや処理のことを指します。
原因1-1｜読み取るファイルを自分で開いている
Power Query は読み取るファイルを自分で開いている場合、データを処理することができません。
データを編集した後で Power Query で処理するというような作業をしている場合、ついファイルを閉じるの忘れてしまってこのようなエラーに遭遇することがあります。
対策1-1
対策は簡単で、Power Query で読み取るファイルを全て閉じてから Power Query の更新を行います。
これですんなりと処理が終わるはずです。
原因1-2｜他のユーザーがファイルを開いている
ネットワーク上の共有フォルダで作業をしている場合、ファイルを開くのは自分だけとは限りません。
他のユーザーがファイルを開いている場合も同じエラーが発生してしまいます。
対策1-2
ファイルを開いている人が分かるのであれば声をかけてファイルを閉じてもらえば解決します。
これが難しいようであればファイルを自分のPCにコピーして他のユーザーのアクセスが無い環境で Power Query を実行します。
この場合、Power Query のデータ読み込み先の設定を変えるなど設定変更を忘れずに行う必要があります。
Power Query でファイルを読み取るのはこのように少し不便なところがあります。
より安定的にデータの共有とデータ処理をするに SQL Server などのデータベース上でデータを共有するという１ランク上の対策もあります。
Power Query と SQL Server の連携については Power Query で SQL Server を活用する方法 の記事にノウハウをまとめていますので、興味がある方は是非ご覧ください。
症状2｜ファイルが見つからない エラー（フォルダ移動・パス変更）
よくあるエラーメッセージ2
このケースで表示されるエラーダイアログのメッセージは次のようなものです。
[Data.Source.NotFound] File or Folder が見つかりませんでした
原因2｜読み込むファイルの保管先変更
このエラーでよくある原因は読み込むファイルの保管先を変更してしまったことです。
フォルダを移動したつもりがなくても、上位のフォルダの名前を少しだけ変更した場合でもエラーが発生します。
フォルダ名に誤記があって修正したというようなことはよくあるケースです。
対策2
このケースでは Power Query のファイルの読み込み先の設定を変更する必要があります。
まず、次のようにして Power Query エディター を開きます。
- Excel のメニューから 「データ」＞「クエリと接続」 をクリック
- ウィンドウ右に出たサイドウィンドウからデータ読み込みをしている クエリ名をダブルクリック
ファイルやフォルダからのデータ読み込みは、最初のステップでパスを指定して読み込んでいます。
ですので、図のように 最初のステップを選択 して、表示された M関数式 の中のパスを修正します。
Power Query エディターを閉じて変更を反映すれば対策は完了です。
症状3｜列が見つからない エラー（列名変更）
よくあるエラーメッセージ3
このケースでのエラーメッセージは次のようなものです。
[Expression.Error] テーブルの列 ‘***’ が見つかりませんでした
原因3-1｜列名の変更
列名を変更してしまってもエラーが発生して Power Query の処理が止まってしまいます。
対策3-1｜元に戻す／クエリ修正
このエラーは列名を元に戻すことで元通りに処理が流れるようになります。
しかし、列名を修正したい場合は、クエリを修正する必要があります。
この場合はまず、上述の対策2で説明したように Power Query エディター を開きます。
続いて Power Query エディターの右サイドに表示されている ステップを上から順に確認 してエラーが発生しているステップを探し出します。
エラーが Power Query エディターに表示されるので、そこがエラーが発生しているステップになります。
Power Query の処理内容の M関数式 を確認すると、修正したい列名が見つかると思いますのでこれを修正します。
他にエラーが無ければこれで Power Query の処理が流れるようになります。
原因3-2｜列の削除
列を削除してしまった場合も同じように Power Quey はエラーで処理を停止してしまいます。
対策3-2｜元に戻す／クエリ修正
列名の変更と同様に列を元に戻すことで Power Query は元通り処理できるようになります。
しかし、そもそも削除した列が不要なのであればクエリの方を修正した方が良いです。
この場合、対策3-1で解説した通りクエリ内で問題となっている 列名が出てくる部分 を修正していきます。
良くあるケースは、データ型を設定するステップ でエラーが発生するケースです。
Power Query の操作で、自分では処理したつもりが無くても Power Query が自動的に「データ型の変更」のステップを加えている ことがあります。
ここでは全ての列に対してデータ型を設定していますので、利用しない列も処理されるようになっています。
このため、列を削除するとこのステップでエラーが発生するのです。
この「データ型の変更」のステップから削除対象の列名の部分を消去します。
他にも削除対象の 列名が M関数式で出てくる部分 を削除していくことでエラー発生を回避することができます。
原因3-3｜複数ファイル間の列名の不一致
フォルダ内のファイルを一括して読み込むクエリを作成していた場合、一つでも列名が違うファイルがあるとエラーになってしまいます。
対策3-3｜列名変更に強いクエリ化
これも列名はクエリの処理内容と整合するようにしておくのが正しい対策になります。
しかし、実際にデータ処理で対象としていない列が入っている場合に、いちいち対応するのは非効率です。
この場合は、列名の変更に強いクエリ にすることで対応します。
この対策は 「他の列の削除」 を使う方法になります。これは次のようにしてクエリを作成します。
- Power Query エディターで、データ読み込み直後に データ処理必要な列のみ を Ctrl キー を押しながら選択
- Power Query エディターのメニューで「列の削除」ボタンの 右下の三角マーク をクリック
- 出てきたプルダウンメニューから 「他の列の削除」ボタン をクリック
これによりデータを読み込んだ直後にデータ処理で使う列にデータを絞り込むことができて、エラー発生のリスクを減らすことができます。
症状4｜更新しても「変化がない」（バックグラウンド更新）
「すべて更新」ボタンを押したのに全く変化が無いように見える場合があります。
正常に動作する場合でもこのような状態になることがあります。
原因4｜バックグラウンド更新が有効
Power Query でクエリを作成すると デフォルトではバックグラウンド更新が有効 になっています。
この場合、「すべて更新」ボタンを押した後にすぐにマウスが復帰して次の操作ができるようになります。
しかし、バックグラウンドではクエリの更新作業が行われていて、しばらくするとクエリの処理結果が表示されることになります。
Excel のウィンドウの左下をよく見ると図のようにバックグラウンド更新がされているということが分かります。
対策4｜バックグラウンド更新の設定を外す
バックグランド更新が問題なのではないので、このような動作が困るのでなければこのまま使い続けることで問題ありません。
しかし、更新が終わってからマウスカーソルなどが復帰する動作の方が分かりやすいのであれば設定を変更します。
- Excel のメニューから 「データ」＞「クエリと接続」 ボタンを押してサイドウィンドウを開く
- 対象のクエリを 右クリック してサブメニューを表示
- サブメニューの 「プロパティ」 をクリック
- 表示された クエリ プロパティ のウィンドウで 「バックグラウンドで更新する」 のチェックボックスを外す
- 「OK」 ボタンを押して設定を完了
この設定により「すべて更新」ボタンを押したら更新が終わるまでマウスカーソルに回転マークが表示されて操作ができなくなります。
これで現在 Power Query が更新中なのか、更新が終わったのかが分かりやすくなります。
症状5｜「追加したデータが反映されない」
データを追加したのに Power Query の出力結果を見ると追加したデータが反映されていないように見える場合があります。
原因5-1｜結合キーの不一致
追加したデータの結合キーが結合先のテーブルと一致しない場合、Power Query の出力結果がデータが欠落したようになってしまいます。
対策5-1｜結合キーの修正
追加したデータを確認して結合先の結合キーと一致するようにデータを修正することでデータが正しく処理されるようになります。
原因5-2｜フィルターによるデータ絞り込み
データをフィルターで絞り込みする際にデータが落とされるケースがあります。
対策5-2｜フィルター条件の修正
追加したデータがフィルタにより落とされているような場合はフィルター条件を見直すことでデータが出力されるようになります。
症状6｜「更新が終わらない・Excelが落ちる」
更新に非常に時間がかかったり、Excelが落ちてしまうようなケースもまれに発生します。
原因6-1｜データ量が多過ぎる
特にデータをデータベースから取得するような場合、不用意にデータ取得をしてしまうとデータベースにある大量のデータを全て読み込んでしまうことがあります。
対策6-1｜データの絞り込みを最初に入れる
Power Query はデータベースからデータを取得する場合、列の絞り込みやフィルターによる行の絞り込み処理を入れておくと、最初から絞り込んだデータのみを取得するようになります。
つまり、Power Query とデータベース間の通信は最小限に絞り込んで処理されるということです。
これはデータ処理の最初の段階で行っておかないと上手くいかないケースがあるので注意が必要です。
Power Query とデータベース（SQL Server）の連携については、SQL Server 活用法 の記事でさらに詳しく解説していますので、興味を持たれた方は是非ご一読下さい。
原因6-2｜重い処理が無駄に入っている
Power Query でのデータ処理は簡単に追加することができてしまうので、処理ステップが多くなりがちです。
しかし、やはりどんなに処理が速いコンピューターでも無駄な処理を多くさせると動作は重くなってしまいます。
対策6-2｜クエリの整理
クエリを一度作成した後で見返してみて、次のような処理があったら削除するなどしてクエリの整理をすると良いでしょう。
これらの処理は無駄であるばかりでなく、重たい処理もあるので削除することでかなり処理が軽くなる可能性があります。
- 何度も ソート処理 を入れている
- データ型変更処理を何度も行っている
- 無用なデータ結合がある
原因6-3｜テーブル出力時に「列の幅を調整」している
Power Query の出力をテーブルに出力させている場合で、「列の幅を調整」するオプションが入っている場合、動作が非常に重たくなる場合があります。
これはデータの内容に合わせて列の幅を自動で調整するもので、全てのデータを読み込んだ後で実行されます。
Power Query の仕様で、この列の幅の調整処理が思った以上に重たい処理になっています。
対策6-3｜「列の幅を調整」オプションを外す
これは次の手順で設定を外すことが可能です。
- Power Query から出力される テーブルのセルを1箇所選択
- Excel のメニューから 「データ」＞「プロパティ」 をクリック
- 「外部データのプロパティ」 ウィンドウが開く
- 「列の幅を調整」 のチェックボックスを外す
これはあまり知られていませんが、この設定により Power Query の動作が劇的に改善する場合がありますので、是非お試し下さい。
まとめ｜「症状から切り分ける」のが最短ルート
Power Query を使い込んでいると必ず一度はこのような更新できないトラブルに見舞われると思います。
しかし、原因は非常に簡単なで少し手を加えるだけで元通り Power Query が動き出すことが多いです。
この記事で解説したようにパターン別の対策を知ることで Power Query を安心して使うことができると思います。
Power Query は仕事の強い味方です、是非この知識を得てさらに Power Query の活用の幅を広げていって下さい。
