エラーの概要
クエリを作成し、すべて順調に見えていた矢先、突然黄色いエラーバーが表示されることがあります。列の型を日付に変更した際、Power Query のセルが Error で埋め尽くされてしまうことがあります。エラーの横にある空白部分をクリックすると、原因が表示されます。
DataFormat.Error: We couldn't parse the input provided as a Date value.
要するに、Power Query はあるテキストを見て、それをカレンダー上の日付に変換する方法が分からず困惑している状態です。
なぜこのエラーが発生するのか？
このエラーは通常、エンジンが想定されたパターンに合致しない値に遭遇したときに発生します。主な原因は以下の通りです。
- 地域設定の不一致： コンピュータ側は
日/月/年（日本やベトナムなど）を想定しているのに、データが月/日/年（米国）形式である場合。例えば、12/31/2023 は、31月が存在しない地域の設定ではエラーになります。 - 隠れた不要なデータ： 列に末尾のスペース、印刷できない文字、または「N/A」や「未定」といったテキストが含まれている。
- 単純な数値形式： 日付が
20231225のような生の数値になっている。Power Query はこれをクリスマスではなく、単なる整数として認識します。 - カレンダー上ありえない日付：
31/02/2023（2月31日）や00/00/0000といった入力データ。
解決策 1：ロケールを使用する（「プロ」の方法）
ヘッダーの「日付」アイコンをただクリックするだけでは不十分な場合があります。データが異なる地域の形式である場合は、**「ロケールを使用して」**機能を使います。これにより、Power Query にどの地域のルールに従うべきかを明示的に指示できます。
- 列ヘッダーを右クリックします。
- 型の変更 > ロケールを使用して... を選択します。
- ダイアログで、データ型を日付に設定します。
- ロケールで、そのデータの元の地域を選択します（例：米国形式の日付なら「英語 (米国)」を選択）。
- OKを押します。
これにより、Mコードにカルチャコードが追加され、海外の同僚とファイルを共有してもクエリが安定して動作するようになります。
= Table.TransformColumnTypes(Source, {{"日付列", type date}}, "en-US")
解決策 2：事前にデータをクレンジングする
一見きれいに見えるデータでも、目に見えない「ゴミ」が含まれていることがあります。データ型を変更する前に、以下の3つのステップを実行してください。
- トリム： 列を右クリック > 変換 > トリム を選択し、先頭や末尾のスペースを削除します。
- クリーン： 右クリック > 変換 > クリーン を選択し、印刷できない制御文字を除去します。
- 置換： 値の置換 を使用して、「N/A」や「null」という文字列を実際の
null値に変換します。
解決策 3：数値形式（YYYYMMDD）を処理する
システムからのエクスポートデータでは、日付が 20240512 のような8桁の数値になっていることがよくあります。これを修正するには、テキストを分割して結合し直す必要があります。まず、列をテキスト型に設定してから、以下の式でカスタム列を追加します。
let
DateStr = Text.From([日付列]),
Year = Text.Start(DateStr, 4),
Month = Text.Middle(DateStr, 4, 2),
Day = Text.End(DateStr, 2)
in
Date.FromText(Year & "-" & Month & "-" & Day)
解決策 4：不備のあるデータに「try...otherwise」を使用する
膨大なデータセットの中に、修正する価値のない不正な行が数行混じっている場合は、try ブロックを使用します。これにより、一部の不良セルのためにデータ更新全体が失敗するのを防げます。カスタム列で以下のように入力します。
try Date.FromText([日付列]) otherwise null
これは変換を試行し、失敗した場合はエラーを出す代わりにセルを空（null）にします。
修正結果を確認する方法
変更を適用したら、結果を再確認しましょう。表示タブに移動し、列の品質にチェックを入れます。「有効」が100%、「エラー」が0%になっていることを確認してください。もう一つの簡単なテストは、列を並べ替えてみることです。時系列順に並べ替えられれば「日付」として認識されています。もし 10/01/2023 が 02/01/2023 より前に来る場合は、まだ「テキスト」として扱われています。
再発防止のヒント
- ISO 8601 形式を推奨する： SQL ビューなどのソース側を制御できる場合は、
YYYY-MM-DD形式での出力を依頼してください。これは失敗することのない世界標準規格です。 - ロケールを固定する： チームが国際的な場合は、常に「ロケールを使用して」メソッドを使用してください。これにより、米国のユーザーが英国のユーザーにファイルを送ったときに発生する「自分のマシンでは動くのに」という問題を回避できます。
- ソースでの入力規則： データが手入力の Excel ファイルから来る場合は、「データの入力規則」を使用して、ユーザーが「2月31日」のような値を入力できないように制限してください。