Power Query 実務テンプレ | クレンジング（前処理）系：日付が不正な行を除外

Excel VBA Power Query M Formula Language
スポンサーリンク

ゴールのイメージをそろえる

今回のテーマは「Power Query で 日付が変（読めない・ありえない）な行だけをきれいに除外する実務テンプレ」です。
やりたいことはシンプルで、「日付が壊れている行は、集計や分析の前にテーブルから落としておく」ことです。

ポイントは次の二つです。
「どの行が“日付として不正”なのかを判定する」
「その行だけをフィルタで除外する」

この“判定＋フィルタ”をテンプレとして持っておくと、日付が原因のバグをかなり潰せます。

まず「日付が不正」とは何かをはっきりさせる

典型的な“不正日付”のパターン

現場でよく出てくる「日付が不正」は、だいたいこんな感じです。

「2024/13/01」や「2024/02/30」のように、カレンダー上ありえない日付。
「2024/00/10」「2024/01/00」のように、月や日が 0。
「2024/1/1a」「2024-XX-01」のように、そもそも日付として読めない文字列。
「9999/99/99」のようなダミー値を“日付列”に入れているケース。

これらをそのまま残しておくと、次のような問題が起きます。

日付型に変換しようとしたときにエラーになる。
期間フィルタや集計で、意図しない結果になる。
別テーブルの日付と JOIN しようとしても、型変換でこける。

だからこそ、「日付として読めない行は、前処理で落としておく」という発想が効きます。

例題の前提データをイメージする

次のようなテーブルを想像してください。

受注番号受注日
1001“2024/10/01”
1002“2024/02/30”
1003“2024/13/01”
1004“2024/10/15”
1005“XXXX/10/20”
1006“”
1007null

ここで「正しい日付」として残したいのは、
“2024/10/01” と “2024/10/15” だけ、というイメージです。

“2024/02/30” と “2024/13/01” はカレンダー上ありえない。
“XXXX/10/20” はそもそも日付として読めない。
空文字や NULL は「日付が入っていない行」として扱いたいので、除外対象にするかどうかはルール次第です。

ここから、「日付として有効な行だけ残す」テンプレを組み立てます。

基本の型：一度“日付型に変換してみて”、失敗した行を落とす

try ～ otherwise を使って「変換できるかどうか」を判定する

Power Query には try ... otherwise ... という構文があります。
これは「この処理を試して、エラーになったら別の値を返す」という仕組みです。

日付の判定に使うと、こうなります。

「この文字列を Date.From で日付に変換してみる」
「成功したらその日付を返す、失敗したら null を返す」

この“成功したかどうか”を使って、「有効な日付だけ残す」フィルタをかける、という流れです。

ステップ1：日付文字列から「安全に日付型を作る」列を追加する

受注日を「試しに日付化した列」を作る

まずは、受注日列から「変換に成功したときだけ日付が入る列」を作ります。

let
    Source = ・・・前のステップ・・・,

    AddedValidDate =
        Table.AddColumn(
            Source,
            "受注日_日付型",
            each
                let
                    v = [受注日],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.From(v),
                    parsed =
                        try Date.From(t) otherwise null
                in
                    parsed,
            type nullable date
        )
in
    AddedValidDate
Power Query

ここでやっていることを言葉で整理します。

元の「受注日」は text（文字列）だと仮定。
NULL や空文字は、そのまま null として扱う。
それ以外は Text.From で文字列として取り出す。
try Date.From(t) otherwise null で、「日付として読めれば date、ダメなら null」を返す。

結果として、「受注日_日付型」列にはこう入ります。

“2024/10/01” → #date(2024,10,1)
“2024/02/30” → null（存在しない日付なので変換失敗）
“2024/13/01” → null
“XXXX/10/20” → null
“” → null
null → null

この「受注日_日付型 が null かどうか」が、「日付として有効かどうか」の判定材料になります。

ステップ2：「日付として有効な行だけ」をフィルタで残す

受注日_日付型 が null でない行だけ残す

次に、「受注日_日付型 が null ではない行だけ」を残します。

let
    Source = ・・・前のステップ・・・,

    AddedValidDate =
        Table.AddColumn(
            Source,
            "受注日_日付型",
            each
                let
                    v = [受注日],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.From(v),
                    parsed =
                        try Date.From(t) otherwise null
                in
                    parsed,
            type nullable date
        ),

    Filtered =
        Table.SelectRows(
            AddedValidDate,
            each [受注日_日付型] <> null
        )
in
    Filtered
Power Query

これで、「日付として読めた行だけ」が残ります。

先ほどの例なら、残るのは 1001 と 1004 の行だけです。
1002, 1003, 1005, 1006, 1007 は、受注日_日付型 が null なので除外されます。

ステップ3：最終的に「日付型の列だけ」を残す

補助列を本番列に昇格させる

実務では、「受注日_日付型」を本番の「受注日」として使いたいので、
列名を入れ替えて、元の文字列列を消すのがきれいです。

let
    Source = ・・・前のステップ・・・,

    AddedValidDate =
        Table.AddColumn(
            Source,
            "受注日_日付型",
            each
                let
                    v = [受注日],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.From(v),
                    parsed =
                        try Date.From(t) otherwise null
                in
                    parsed,
            type nullable date
        ),

    Filtered =
        Table.SelectRows(
            AddedValidDate,
            each [受注日_日付型] <> null
        ),

    RemovedOriginal =
        Table.RemoveColumns(
            Filtered,
            {"受注日"}
        ),

    Renamed =
        Table.RenameColumns(
            RemovedOriginal,
            {{"受注日_日付型", "受注日"}}
        )
in
    Renamed
Power Query

これで、最終的なテーブルには「日付型の受注日」だけが残り、
かつ「不正な日付の行」はすべて除外された状態になります。

応用：複数の日付列をまとめてチェックしたい場合

受注日も納品予定日も「どちらかが不正なら行ごと落としたい」

例えば、次のようなルールにしたいとします。

「受注日も納品予定日も、両方とも“有効な日付”である行だけ残す」

この場合は、両方に対して「日付型に変換してみる」列を作り、
どちらも null でない行だけを残す、というフィルタをかけます。

イメージはこうです。

受注日日付型 列を追加（さっきと同じロジック）。
納品予定日日付型 列も同じように追加。
[受注日_日付型] <> null and [納品予定日_日付型] <> null の行だけ残す。

この型を一度作っておけば、日付列が増えても同じパターンで拡張できます。

重要ポイントの深掘り

「除外」か「NULL にして残す」かを最初に決める

今回のテーマは「不正な行を除外」ですが、
現場によっては「行は残したいが、日付だけ NULL にしたい」というケースもあります。

その場合は、フィルタで落とさずに、
「日付型に変換できなかったものは NULL にして、そのまま残す」という設計にします。

どちらが正解かは、「そのデータをどう使うか」で決まります。

集計や分析で「不正データは一切混ぜたくない」なら、行ごと除外。
監査やログ用途で「不正データも記録として残したい」なら、行は残して日付だけ NULL。

この方針を最初に決めておくと、クレンジングの設計がブレません。

try ～ otherwise を使うときの感覚

try Date.From(t) otherwise null は、
「とりあえず日付として読んでみて、ダメなら静かに null にしておく」という意味です。

これを使うと、「変換エラーでクエリ全体が止まる」のを防げます。
日付に限らず、「壊れているかもしれないデータ」を扱うときの定番パターンとして、
体に染み込ませておくとかなり楽になります。

実務テンプレとしてのまとめ

「日付が不正な行を除外」の本質は、次の二つです。

一度“日付型に変換してみて”、成功したものだけを「有効な行」とみなす。
その判定結果（null かどうか）を使って、Table.SelectRows で行をフィルタする。

この型さえ持っておけば、
受注日・出荷日・請求日・有効期限など、どんな日付列でも、
「壊れた日付が混ざったまま集計してしまう」という事故をかなり防げます。

スポンサーリンク
タイトルとURLをコピーしました