Power Query によるデータ処理の自動化を活用していると、ある日突然エラーが発生して処理ができない、または処理結果がおかしくなることがあります。
最初はどこから対処すれば良いか戸惑ってしまうかもしれませんが、原因のパターンはある程度決まっている ので一通り知っておくと対処方針はすぐに分かるようになります。
この記事では、Power Query でよく発生するエラーとその対策を、実例を上げて紹介します。
エラーが発生して困っている方、Power Query をより使いこなしたい方は是非参考にしてみて下さい。
では、早速いってみましょう！
エラーの2つのパターン
Power Query のエラーは大きく分けて２つのパターンに分かれます。
ステップレベルエラー（Step-level errors）
１つ目は、処理が止まってしまって、処理結果が何も得られないパターンです。
これは Power Query の処理ステップのどこかで致命的なエラーが発生して、それ以上処理ができなくなってしまったものです。
このパターンのことを ステップレベルエラー と呼びます。
セルレベルエラー（Cell-level errors）
２つ目は、処理はできているものの、一部のセルでエラーが発生していて値が無い状態になっているパターンです。
これはセルの値を処理する際に何らかのエラーが発生してデータ処理ができなかったものです。
このパターンのことを セルレベルエラー と呼びます。
ここからエラーのパターン別にエラーの確認方法や対策について解説します。
処理が止まる ＜ステップレベルエラー＞「ダウンロードは完了していません。」
ステップレベルエラーが発生すると、「クエリと接続」ウィンドウに図のようにエラーが表示されます。
このエラーが発生した場合は、データの更新は失敗しており、表示されているデータは古いままなので注意して下さい。
ここから更に深くトラブルの原因を探っていきます。
【エラー1】「（フォルダ or ファイル）*** が見つかりませんでした。」
エラーが発生しているクエリをダブルクリックして Power Query エディターを開きます。すると図のように黄色いボックスにエラーの内容が表示されます。
エラーの内容がフォルダが見つからない となっています。
これはフォルダを整理したりして 読み込むファイルの場所が変わってしまった場合 などに発生するエラーです。
対策は ファイル読み込みの場合 と、フォルダ読み込みの場合 に分かれます。
【対策1-1】ファイル読み込みの場合
ファイル読み込みのクエリ は１つのクエリで構成されていますので、エラーが発生しているクエリを Power Query エディターで開いて修正していきます。
図のように「エラーに移動する」ボタンを押すとエラーが発生しているステップに移動できます。
ファイルの読み込みでエラーが発生している場合は図のように最初の「ソース」というステップに移動しますので、図のようにしてファイル パスを修正して ファイルの正しい保存場所を指定 します。
この原因の場合はこれで無事クエリが動き出すはずです。
【対策1-2】フォルダ読み込みの場合
フォルダ読み込みの場合 は ２つのクエリの修正が必要 です。図の２つのクエリをそれぞれ開いて修正します。
クエリを開いたらそれぞれ最初の「ソース」というステップを図のようにして修正します。
２つのクエリを修正すると、無事クエリが動き出すはずです。
【対策1-3】それでもエラーが消えない場合
クエリを修正したら、Excelのメニューから「データ」＞「すべて更新」とすることでクエリが再実行されて更新されます。
しかし、時々クエリがエラーになったままの場合があります。
その場合は、エラーのクエリを Power Query エディターで開いて図のように プレビューを更新 してみて下さい。修正が反映されてクエリが正常に動き出すと思います。
【エラー2】「テーブルの列 ‘***’ が見つかりませんでした。」（列指定のエラー）
Power Query エディターでエラーを確認すると 列が見つからないというエラー になっているケースがあります。
Power Query でのデータ処理の自動化を作成した後で、元データの方で列名を変更 されたり、列が削除された場合 に発生するエラーです。
【対策2-1】列名指定を修正
エラーが発生しているステップを選択すると、そのステップのM関数式が表示されます。
M関数式は難しくて理解できなくても、エラーになっている列名を正しくする程度の修正であれば簡単にできます。
図のように修正してみて下さい。
【対策2-2】「変更された型…」なら削除して再設定でも
よくあるパターンとして Power Query が自動で追加した データ型設定のステップでエラーが発生 することがあります。
上記の対策のように M関数式を修正するのが面倒な場合は、まずエラーが発生している データ型設定のステップを一旦削除 して、再度データ型を指定し直してステップを追加するという方法でも問題ありません。
通常、Power Query でデータ型を設定するステップは、「変更された型」というような名前に設定されていますので、探してみて下さい。
【対策2-2】ピボット解除前のデータ型設定のステップは削除
ピボット解除をすると、解除された列は無くなって、列名は「属性」という列の文字列データに変換されます。
ですので、ピボット解除する前に列のデータ型を設定する必要がありません。
データ型の設定がピボット解除の前に処理されているようであれば、そのステップは削除しておくと良いです。
ピボット解除の後にデータ型を設定して下さい。
セルの処理ができない ＜セルレベルエラー＞「○○個のエラーです」
セルレベルのエラーが発生するとクエリは一通り処理されますが、「クエリと接続」のウィンドウを見ると図のようにエラーの件数が表示されます。
エラーが発生した場合は、そのクエリを Power Query エディターで開いて原因を調べていきます。
列名の下に縞々の線が表示
セルレベルエラーが発生したクエリを Power Query エディターで開くと、図のような表示になっています。
セルレベルエラーには原因がいくつかありますので、原因ごとの対策を解説します。
【エラー3】「*** に変換できませんでした」（データ型の変換エラー）
クエリを Power Query エディターで開いて、エラーが発生しているセルを選択すると、図のようにエラー内容が表示されます。
Power Query で最もよくあるエラーの一つとして、データ型の変換エラーがあります。サンプルのケースを図に示します。
【対策3-1】元データを修正する
Power Query のデータ処理自動化の機能を活かすには、元データがルール通りに整っていること が重要です。
問題があるデータがあるなら、クエリを修正する前に データを極力修正 するようにしましょう。
エラーが発生している箇所は Power Query エディターで確認できるので、元データの場所を特定して修正することができると思います。
【対策3-2】データの入力ルールを設定する
元データが Excel ファイルで人の手でデータを入力している場合は、データの入力範囲にルールを設定する という対策があります。
図のようにデータを入力するセルに「データの入力規制」の設定をすることで、ルール以外のデータを入力すると警告が発生して入力が中断されるようになります。
この設定をすることで元データを整えることが可能です。
【対策3-3】データ型設定の前に値を置換する
元データを極力整えると言っても、実際にはそうはいかないケースもあると思います。
例えば、売上金額を入力する列に「未定」などと入力して管理するケースもあるかもしれません。
そういった場合はデータ型を設定する前に Power Query の データ置換機能 でデータを置換しておくという対策があります。
上記の例であれば、「未定」という値を数値のゼロに変換しておくというのも対策として効果があります。
【対策3-4】エラーを置換する
数値を入力すべき列に「未定」「未確定」「調整中」など様々な人が様々な表現でデータを入力していた場合は、いちいちそれをゼロに変換するという処理をしても追いつきません。
そういう場合は、まず数値型の設定するステップを実行した後で、エラーになっているセルを一括で変換する という対策があります。
図のようにしてエラーの部分を一括で置換します。
【対策3-5】エラー行を削除する
エラーが発生している行は必要無いという場合は、エラー行ごと削除 してしまうことが有効です。
図のように操作することで エラー行を削除 することができます。
【エラー4】「セル値 ‘***’ が無効です。」（Excelのエラー値）
元データの Excel ファイルのデータの範囲に Excel 式のエラー が含まれている場合があります。
Excel 式のエラーとしては以下のようなものがあります。
- N/A（検索関数でマッチが無いケースなど）
- DIV/0!（数値をゼロで割り算しているケース）
- #REF!（セル参照先が無いケース）
これらは Power Query で読み込まれるとそのままエラーになってしまいます。
【対策4-1】対策3-1～5 を実施
Excel のエラー値の対策は上記の【対策3-1】～【対策3-5】を参考に対策を講じて下さい。
【エラー5】計算結果が null になってしまう
Power Query で設定した計算式は間違っていないのに、計算結果が null になってしまう というケースがあります。
null とは「データが無い」ことを意味する一種のデータです。
問題は、null値 を計算すると結果は必ず null になってしまうということです。
つまり、1 + null = null , 1 x null = null とどう計算しても結果は null になります。
「データが無い」のだからゼロと同じように扱ってくれることを期待したいところですが、これはデータベースの世界での標準的な仕様なので仕方ありません。
【対策5-1】null 値 を置換する
上記の通り、null 値 は通常ゼロとして扱うことで問題無いはずです。
ですので、null 値 が含まれる列に対して図のようにして null をゼロに変換する 処理を加えます。
これで無事 null 値はゼロに変換でき、以降の計算も正しく結果が出力されるようになります。
まとめ
Power Query を利用しているとデータの変化によりエラーが発生する場合があります。
強力な Power Query の機能を活かしきるにはエラーへの対処方法も身に付けておく必要があります。
この記事ではよくあるエラーのパターンとその対策についてご紹介しました。通常の使用で直面するエラーのケースはこれらが主なものです。
皆さんも是非ここで紹介した知識を身に付けて、Power Query を使いこなして業務を効率化していっていただければと思います。
