【Excel】Power Queryで1899年の日付になる時|元の値と型を確認する

Power Queryで1899/12/30や1899/12/31が現れたら、日付へ変換する前の値と型を確認します。数値の0、数値の1、時刻だけの値、nullは、それぞれ別のデータです。

1900年より前を一律に消すと、正当な過去の日付まで失います。元列を残し、何を欠損値として扱うかを決めてから補正します。

1. どのステップで変わったか調べる

1. クエリを編集し、問題の行が分かるIDを確認します。
2. 「適用したステップ」を順に選び、日付になる直前を探します。
3. 元の値と列のデータ型を記録します。
4. 型変更、置換、結合、独自の計算のどれで変化したか確認します。

ソースがすでに1899年なら、Power Queryへ入る前の処理も調べます。表示だけを見て、すべて同じ原因と判断しないでください。

2. 数値を日付にした場合の基点

MのDate.Fromでは数値の0が1899/12/30、1が1899/12/31に対応します。環境によって0が気まぐれにどちらかへ変わる、という説明は正確ではありません。

これはPower Queryの変換仕様です。Excelのシートの日付システムと完全に同じ説明で扱わず、変換に使った関数と数値を確認してください。nullをDate.Fromへ渡した場合はnullが返ります。

3. 時刻だけなら時刻型で扱う

たとえばDateTime.From(#time(13, 0, 0))は1899/12/30 13:00になります。日付を持たない時刻を日付/時刻へ変換したためです。時刻だけが目的なら、時刻型を維持します。

勤務日などが必要なら、正しい日付列と組み合わせる設計にします。開始時刻・終了時刻と勤務時間の長さも別です。24時間を超える経過時間を、1日の時刻として扱わないでください。

4. 欠損の規則が確認できた値だけ補正する

ソースの数値列「日付番号」で、0が未入力を意味すると確認できた場合のカスタム列例です。日付変換の前に追加し、元列と比較します。

if [日付番号] = null then null
else if [日付番号] = 0 then null
else Date.From([日付番号])

この例は数値またはnullの列が対象です。文字列やErrorが混ざる場合は、別に確認します。0が実際の日付を意味するデータでは、この規則を使わないでください。

5. 空文字とエラーを隠さない

CSVなどの空文字とnullは同じとは限りません。空文字を欠損として扱う規則があるなら、型変換の前に対象を確認して置換します。文字のnullではなく、Mの値としてのnullを使います。

try ... otherwise nullで全エラーを消すと、変換不能の理由まで見えなくなります。まずエラー行を取り出し、日付形式や地域の解釈違いを調べてください。

6. 補正前後の件数を照合する

過去の日付が業務上あり得る場合は、年だけを根拠に削除しないでください。

7. 次回の更新でも確認する

新しいデータを追加して更新し、同じ規則で正しく処理されるか確認します。元列は検証が終わるまで残し、欠損値の定義をクエリの説明にも記録します。

基点と型ごとの変換はMicrosoftのDate.FromとDateTime.Fromで確認できます。

📊

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

🏆

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