今回は、ExcelのPower Queryの型変換エラーを確認して直す方法を紹介します。
型変換エラーが起きる場面
Power QueryでCSVやExcelファイルを読み込むと、日付、数値、文字列などのデータ型が自動で設定されることがあります。このとき、列の中に想定と違う値が混ざっていると、型変換エラーが発生します。
Excel Power Query
型変換エラーは、集計前のデータ整形でよく出る問題です。金額列に「未定」と入っている、日付列に空白や説明文が混ざっている、コード列が数値として扱われて先頭のゼロが消える、といったケースがあります。
エラーを無視して読み込むと、集計結果に抜けが出たり、更新時に止まったりすることがあります。Power
Queryでは、どの列で何が原因になっているかを確認し、元データの意味に合わせて直すことが大切です。
まず列の意味を確認する
型変換エラーを直す前に、その列を何として扱うべきかを決めます。見た目が数字でも、計算に使わないコードなら文字列として扱うほうがよい場合があります。
- 金額や数量は数値
- 日付は日付型
- 社員番号や商品コードは文字列
- 郵便番号や電話番号は文字列
- 区分や状態は文字列
たとえば「00123」という商品コードを数値にすると「123」になり、元のコードと一致しなくなります。数値に見える列でも、計算対象か識別子かを確認します。
自動変更された型を確認する
Power Queryでは、読み込み時に「変更された型」というステップが追加されることがあります。このステップで各列の型が設定されています。エラーが出た場合は、まずこのステップを確認します。
不要な自動型変換が原因なら、ステップを削除して手動で型を設定する方法もあります。最初に自動設定をそのまま受け入れるのではなく、列ごとの意味に合わせて見直します。
エラー行を確認する流れ
Power Queryエディターでは、エラーのある行を抽出して確認できます。どの値が変換できないのかを見れば、修正方法を判断しやすくなります。
- Power Queryエディターで対象クエリを開く
- エラーが出ている列を確認する
- 列のフィルターからエラー行を表示する
- 変換できない値の内容を見る
- 元データを直すか、Power Query側で処理するか決める
- 修正後に更新して確認する
エラー行が少ない場合は元データの入力ミスかもしれません。毎回同じ形式で出る場合は、Power Query側で処理を追加したほうが安定します。
元データを直すか処理で吸収するか
元ファイルの入力ミスなら、元データを直すのが分かりやすい方法です。ただし、システムから毎回同じ形式で出力される文字や空白が原因なら、Power
Queryで置換や型変換前の整形を入れるほうが向いています。
たとえば、金額列にカンマや単位が含まれる場合は、不要な文字を取り除いてから数値型に変換します。日付列に余分な空白がある場合は、トリミングしてから日付型にします。
よくある型変換エラーの直し方
型変換エラーは、列の意味と値の中身が合っていないときに起きます。よくあるパターンを知っておくと、原因を探しやすくなります。
- 数値列に文字が混ざっている
- 日付の表記がそろっていない
- 空白や記号が入っている
- 先頭ゼロが必要なコードを数値にしている
- 小数点や桁区切りの形式が混在している
数値列に「-」が入っている場合、それをゼロと扱うのか、空白と扱うのかは業務上の意味で決めます。単純に置換すると、欠損値とゼロを混同することがあります。
エラーを置き換えるときの注意
Power Queryには、エラーを置換する機能があります。エラーを空白やゼロに置き換えることはできますが、原因を確認せずに置き換えると、問題のあるデータを見逃すことになります。
エラー置換は、原因を確認してから使うことが大切です。定期処理では、エラー行を確認するクエリを別に残しておくと、更新時の異常に気づきやすくなります。
型変換の順番を整える
Power Queryでは、適用したステップの順番が結果に影響します。不要な文字を削除する前に数値型へ変換するとエラーになります。日付の表記を整える前に日付型へ変換しても同じです。
- 不要な空白を削除する
- 記号や単位を整理する
- 表記ゆれを置換する
- 列の型を設定する
- エラー行を確認する
型変換は、文字列の整形が終わってから行うと安定します。ステップ名を分かりやすく変更しておくと、後から見たときに何をしているのか分かりやすくなります。
更新時に壊れにくくする
毎月同じ処理をするクエリでは、新しいファイルが追加されたときの例外にも備えます。空ファイル、見出し違い、日付表記の違いが入ると、型変換で止まることがあります。
取込用フォルダーに不要なファイルを入れない、列名を固定する、提出様式をそろえるなど、元データ側のルールも整えるとエラーが減ります。
エラー確認用の出力を作る
定期的に更新するクエリでは、エラー行を確認するための出力を別に作ると便利です。通常の集計用クエリとは別に、エラーがある行だけを表示する確認用クエリを用意しておけば、更新後に問題のあるデータを見つけやすくなります。
確認用の表には、元ファイル名、行番号に相当する情報、エラーが出た列、元の値を残します。原因が分かれば、提出元へ修正を依頼するのか、Power
Query側で処理を追加するのか判断できます。更新作業を担当者間で引き継ぐ場合も、エラー確認用の表があると状況を説明しやすくなります。
エラーが出やすい列は、クエリ内で早めに名前を整えておくと確認しやすくなります。元ファイルの列名が分かりにくい場合でも、Power
Query上で「請求日」「金額」「商品コード」のように変更しておけば、エラー確認時に迷いません。列名変更も適用ステップとして残ります。
まとめ
ExcelのPower Queryで型変換エラーが出たら、まず列の意味と自動設定された型を確認します。数値に見える列でも、コードや郵便番号のように文字列で扱うべきものがあります。
エラー行を抽出し、元データの入力ミスなのか、毎回発生する形式の問題なのかを判断します。不要な文字や空白を整えてから型変換することで、更新時のエラーを減らせます。
Excel Power Query
型変換エラーは、データ整形の品質を確認するサインでもあります。原因を見ずに置換するのではなく、列の意味に合わせて処理を組み立てることが大切です。