【Excel】Power Queryで「#ERROR」が大量発生!Excelの型変換エラー特定と修正方法

Power Queryでデータを取り込んだ際に、意図せず「#ERROR」が表示されてしまうことがあります。特に数値や日付のデータ型が正しく変換されない場合に頻繁に発生するエラーです。このエラーが大量に発生すると、データの集計や分析が不可能になり、業務に支障をきたします。この記事では、Power Queryにおける型変換エラー「#ERROR」が発生する原因を特定し、その修正方法を具体的に解説します。

Power Queryの型変換エラーは、取り込むデータの形式とExcelが期待する形式が一致しない場合に起こります。例えば、数値として取り込みたいのに、データの中に文字列が混入している場合などが該当します。このエラーを解消することで、Power Queryでのデータ処理をスムーズに進められるようになります。

Power Queryで型変換エラー「#ERROR」が発生する原因

Power Queryで「#ERROR」が表示される主な原因は、データの型変換に失敗することです。Excelがデータを特定の型(数値、日付、通貨など)として認識しようとした際に、そのデータが期待される形式と異なるとエラーが発生します。例えば、数値として扱いたい列に、数値以外の文字(例: “A”, “-“, “不明”)が含まれている場合などが該当します。

また、日付データにおいても、Excelが認識できない形式(例: “2023年12月1日” のような全角文字を含む日付)が含まれていると、日付型への変換に失敗しエラーとなります。これらのエラーは、元のデータソースに不整合がある場合に発生することが多いです。Power Queryのステップで明示的に型変換を行っている箇所で、このエラーが顕著に現れます。

Power Queryの型変換エラー「#ERROR」を特定・修正する手順

Power Queryで発生した型変換エラー「#ERROR」を特定し、修正するには、エラーが発生している箇所を特定し、原因となっているデータを修正するか、型変換のステップを調整する必要があります。ここでは、具体的な手順を解説します。

1. エラーが発生している列の特定
   Power Queryエディターで、データが読み込まれているテーブルを確認します。エラーが表示されている列を特定してください。多くの場合、列名の左側に表示されるアイコンが、エラーを示唆するアイコン(例: 丸にビックリマーク)に変わっていることがあります。
2. エラーの原因となっている行の特定
   エラーが発生している列を選択した状態で、リボンの「ホーム」タブにある「クエリのエラー」グループから「エラーの保持」または「エラーの削除」をクリックします。ここでは「エラーの保持」を選択し、エラーが発生している行だけを抽出します。これにより、どの行でエラーが発生しているのかを具体的に確認できます。
3. エラー原因データの確認と修正
   抽出されたエラー行を確認し、どのデータが原因で型変換エラーを起こしているのかを特定します。例えば、数値列でエラーが出ている場合、その行に数値以外の文字が含まれていないかを確認します。原因データが特定できたら、元のデータソース(Excelファイル、CSVファイル、データベースなど)に戻って、そのデータを修正します。例えば、不要な文字を削除したり、正しい形式に修正したりします。
4. Power Queryでの再読み込みと型変換の調整
   元のデータソースを修正したら、Power Queryエディターに戻り、「ホーム」タブの「更新」をクリックしてデータを再読み込みします。多くの場合、これでエラーが解消されます。もしエラーが解消されない場合は、型変換のステップを見直します。
5. 型変換ステップの修正
   「適用したステップ」ペインで、型変換を行っているステップ(例: 「変更された型」)を確認します。このステップで、間違ったデータ型が指定されていないか、あるいは変換できないデータが混入している可能性がないかを確認します。必要であれば、そのステップを削除し、再度正しいデータ型を指定して適用し直します。
6. エラー処理の追加(オプション)
   データソースの修正が難しい場合や、一時的なエラーを無視したい場合は、型変換のステップでエラー処理を追加することも可能です。例えば、数値変換を行う際に「エラー時」の処理として「nullを返す」などを指定できます。これにより、データ自体はそのまま保持され、エラーとして表示されなくなります。

Power Queryで型変換エラーを回避するための対策

Power Queryで型変換エラー「#ERROR」を未然に防ぐためには、データソースの品質管理と、Power Queryでの適切なデータ処理が重要です。以下に、エラーを回避するための具体的な対策を挙げます。

データソースのクレンジング

Power Queryに取り込む前に、元のデータソース(Excelシート、CSVファイル、データベースなど)のクレンジングを徹底することが最も効果的です。数値列には数値のみ、日付列にはExcelが認識できる日付形式のみが含まれるように、事前にチェックと修正を行います。特に、外部から提供されるデータや、複数の担当者が作成するデータの場合は、この事前クレンジングが不可欠です。

Power Queryでの型変換タイミングの調整

Power Queryは、データの読み込み時に自動で型変換を試みることがあります。この自動変換が原因でエラーが発生する場合があるため、型変換のステップを意図的に後回しにする、または手動で適用する方が安全な場合があります。具体的には、初期段階では「テキスト型」として読み込み、データ全体の確認や不要な文字の削除を行った後に、必要なデータ型に変換する手順を踏むことが推奨されます。

エラー処理機能の活用

Power Queryには、型変換時のエラーを処理する機能があります。例えば、数値をテキストに変換する際に、変換できない値があった場合に「null」や特定の文字列(例: “変換不可”)を返すように設定できます。この機能を利用することで、データ全体のエラーを回避し、問題のあるデータだけを後で確認・修正することが容易になります。

カスタム列での条件分岐

より複雑なデータ型変換や、特定の条件に基づいて型を決定したい場合は、カスタム列の追加機能を使用します。カスタム列では、M言語(Power Queryの関数言語)を使用して、条件分岐(if文など)を記述し、エラーが発生しそうな場合に代替の値を返す処理を記述できます。これにより、柔軟なデータ処理が可能になります。

Power Queryの型変換エラーに関するよくある質問

Q1: Power Queryで「null」値が「#ERROR」と表示されるのはなぜですか?

「null」値自体はエラーではありません。しかし、本来数値や日付が期待される列に「null」値があると、その「null」を数値や日付に変換しようとして失敗し、「#ERROR」と表示されることがあります。または、その「null」値が、後続のステップでエラーを引き起こしている可能性もあります。この場合、「null」値を適切に処理するか、エラーが発生するステップの前に「null」を別の値(例: 0や空文字)に置き換える処理を追加する必要があります。

Q2: 数値列にカンマ(,)や通貨記号(¥)が含まれているとエラーになりますか?

はい、多くの場合エラーになります。Power Queryが数値を自動認識する際、カンマや通貨記号は数値の一部とは見なされません。これらの記号が含まれていると、数値型への変換に失敗し、「#ERROR」が表示されます。修正するには、Power Queryエディターでこれらの記号を削除するステップを追加するか、元のデータソースで削除してから再読み込みする必要があります。

Q3: 日付列で「#ERROR」が発生した場合、どのような原因が考えられますか?

日付列で「#ERROR」が発生する原因としては、以下のようなものが考えられます。

• Excelが認識できない日付形式(例: “2023年12月1日” のような全角文字、”12/31/2023″ のようなアメリカ式日付を日本式と誤認識される場合など)が含まれている。
• 日付と見なせない文字列(例: “未定”、”休業”)が混入している。
• 日付の区切り文字(“/” や “-“)が統一されていない。

これらの場合、元のデータソースで日付形式を統一するか、Power Queryで日付変換前に不要な文字を削除・置換するステップを追加する必要があります。

Q4: 特定の列だけ型変換エラーを無視したい場合はどうすればよいですか?

特定の列で発生する型変換エラーを無視したい場合は、その列でエラーが発生した際に、代替の値(例: null、空文字、0)を返すように設定するのが一般的です。これは、カスタム列を追加し、M言語の`try otherwise`構文を使用することで実現できます。例えば、数値を変換する際に、`try Number.From([列名]) otherwise null` のように記述すると、変換に失敗した場合はnullが返されます。

Q5: Power Queryの型変換エラーは、Excelのバージョンによって違いがありますか?

Power Queryの基本的な機能や型変換の仕組みは、Excelのバージョン(Excel 2016以降、Microsoft 365)で大きな違いはありません。ただし、最新のMicrosoft 365版Excelでは、より高度な関数や機能が追加されている可能性があります。しかし、型変換エラーの根本的な原因や修正方法は、どのバージョンでも概ね共通しています。

比較項目	Power Queryでの型変換	Excelシートでの手動変換
処理対象	大量データ、複数ファイル、自動化された処理	単一ファイル、少量のデータ、手動での確認
エラー発生時	「#ERROR」表示、エラー行の特定・保持・削除機能あり	「#N/A」「#VALUE!」などのエラー表示、特定が煩雑
型変換の柔軟性	豊富な関数、カスタム列での複雑な条件分岐が可能	限られた関数、複雑な条件分岐はVBAなどが必要
自動化	クエリの更新で自動化可能	手動操作が必要
学習コスト	M言語の理解が必要な場合あり	Excel関数の知識があれば容易

Power Queryで「#ERROR」が表示される型変換エラーは、データソースの不整合や、Power Queryでの型変換設定の不備が原因で発生します。エラーが発生した列と行を特定し、元のデータソースを修正するか、Power Queryエディターで型変換のステップを調整することで、この問題を解決できます。また、データソースの事前クレンジングや、Power Queryのエラー処理機能の活用により、将来的なエラー発生を未然に防ぐことが可能です。

この記事で解説したエラー特定と修正の手順を実践することで、Power Queryでのデータ処理における「#ERROR」問題を解消し、データの信頼性を向上させることができます。今後は、データを取り込む前にデータソースの形式を統一する習慣をつけ、Power Queryでは必要に応じてエラー処理を組み込むことを検討してください。

📊

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

🏆

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