Power Query 実務テンプレ | クレンジング（前処理）系：数値エラー行の検出

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

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

今回のテーマは「Power Query で “数値になってほしい列”に変な値が入っている行をきれいに検出する実務テンプレ」です。
ここでいう「数値エラー行」は、例えばこんな状態を指します。

本当は数値列なのに「ABC」「不明」「-」などの文字が混ざっている。
空文字やスペースだけが入っていて、数値に変換するとエラーになる。
「999999999999999999999」みたいに、桁が大きすぎて数値にできない。

目的は、「どの行が“数値としておかしいか”を機械的に判定して、一覧にできるようにする」ことです。
除外するか、修正候補として別テーブルに出すかは、そのあとで決めればOKです。

まず「数値エラー」がどこで起きるかを理解する

Power Query が数値として読めないパターン

Power Query で数値エラーが起きる典型パターンは、だいたい次のようなものです。

「売上金額」列に「不明」「調整中」などの文字が混ざっている。
「数量」列に「10個」「5台」のように単位付きで入っている。
「単価」列に空文字（””）やスペースだけが入っている。
「カンマ付き数値」をそのまま number 型に変換しようとしている。

これらを type number に変換しようとすると、変換できない行でエラーになります。
そのエラーを「事前に検出しておく」のが、今回のテンプレの役割です。

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

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

商品コード数量単価売上金額
A001“10”“120”“1,200”
A002“5個”“200”“1,000”
A003“”“300”“不明”
A004“ABC”“150”“900”
A005null“調整中”“0”

本来は、数量・単価・売上金額は全部「数値」として扱いたい列です。
でも、実際には文字列として入っていて、しかも変な値が混ざっています。

ここから、「数値に変換したときにエラーになる行」を検出するテンプレを作っていきます。

基本の型：一度“数値に変換してみて”、失敗した行をマークする

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

日付のときと同じで、数値でも try ... otherwise ... が使えます。
やりたいことはこうです。

この値を Number.From で数値に変換してみる。
成功したらその数値を返す。
失敗したら null を返す。

この「成功したかどうか」を使って、「数値エラー行かどうか」を判定します。

ステップ1：数量列に対して「数値変換結果」の列を追加する

数量を“試しに数値化した列”を作る

まずは数量列だけを対象に、「数値として読めたかどうか」を判定する列を追加します。

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

    AddedQtyNumber =
        Table.AddColumn(
            Source,
            "数量_数値変換",
            each
                let
                    v = [数量],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.Trim(Text.From(v)),
                    parsed =
                        try Number.From(t) otherwise null
                in
                    parsed,
            type nullable number
        )
in
    AddedQtyNumber
Power Query

ここでのポイントを言葉で整理します。

元の「数量」は text だと仮定。
NULL や空文字は、そのまま null として扱う。
それ以外は Text.From で文字列にし、Text.Trim で前後の空白を削る。
try Number.From(t) otherwise null で、「数値として読めれば number、ダメなら null」を返す。

この結果、「数量_数値変換」列にはこう入ります。

“10” → 10
“5個” → null（数値に変換できない）
“” → null
“ABC” → null
null → null

この「数量_数値変換 が null かどうか」が、「数量として不正かどうか」の判定材料になります。

ステップ2：「数値変換に失敗した行だけ」を抽出する

数値エラー行だけを別テーブルとして取り出す

次に、「数量_数値変換 が null で、かつ元の値が空ではない行」を“エラー行”として抽出します。
空文字や null を「エラー」とみなすかどうかはルール次第ですが、ここでは「空欄もエラー扱い」にしてみます。

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

    AddedQtyNumber =
        Table.AddColumn(
            Source,
            "数量_数値変換",
            each
                let
                    v = [数量],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.Trim(Text.From(v)),
                    parsed =
                        try Number.From(t) otherwise null
                in
                    parsed,
            type nullable number
        ),

    ErrorRows =
        Table.SelectRows(
            AddedQtyNumber,
            each
                let
                    original = [数量],
                    converted = [数量_数値変換]
                in
                    (original <> null and Text.Trim(Text.From(original)) <> "") and converted = null
        )
in
    ErrorRows
Power Query

このクエリの結果には、次の行が含まれます。

数量 = “5個”（変換できない）
数量 = “”（空欄をエラー扱いにしている場合）
数量 = “ABC”

こうして「数量列の数値エラー行だけ」を一覧にできます。
このテーブルを「エラーログ」として別シートに出したり、担当者に確認してもらう、という運用ができます。

ステップ3：逆に「正常な行だけ」を残したい場合

本体テーブルからエラー行を除外する

今度は逆に、「正常な行だけを残したい」場合です。
さっきと同じ判定ロジックを使って、「数値変換に成功した行だけ」を残します。

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

    AddedQtyNumber =
        Table.AddColumn(
            Source,
            "数量_数値変換",
            each
                let
                    v = [数量],
                    t =
                        if v = null or v = "" then
                            null
                        else
                            Text.Trim(Text.From(v)),
                    parsed =
                        try Number.From(t) otherwise null
                in
                    parsed,
            type nullable number
        ),

    ValidRows =
        Table.SelectRows(
            AddedQtyNumber,
            each [数量_数値変換] <> null
        )
in
    ValidRows
Power Query

これで、「数量を数値として読めた行だけ」が残ります。
“5個” や “ABC” の行は落ちます。

実務では、

本体テーブル：ValidRows（正常行だけ）
エラーテーブル：ErrorRows（異常行だけ）

という2本立てにしておくと、後工程がかなりスッキリします。

応用：複数の数値列をまとめてチェックしたい

数量も単価も売上金額も「どれか一つでもおかしければエラー行」

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

数量・単価・売上金額のどれか一つでも数値として不正なら、その行は“数値エラー行”とみなす。

この場合は、各列に対して「数値変換結果」の列を作り、
どれか一つでも null になっている行をエラー行として抽出します。

イメージはこうです。

数量数値変換 列を追加。
単価数値変換 列を追加。
売上金額_数値変換 列を追加。
数量_数値変換 = null or 単価_数値変換 = null or 売上金額_数値変換 = null の行だけ抽出。

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

もう一歩進んだ実務テンプレ：関数化して再利用する

「数値として読めるかどうか」を判定する関数を作る

毎回同じロジックを書くのが面倒なら、
「数値として読めるかどうか」を判定する関数を一つ作っておくと便利です。

// クエリ名：fn_TryToNumber
let
    TryToNumber = (value as nullable any) as nullable number =>
        let
            v =
                if value = null then
                    null
                else
                    Text.Trim(Text.From(value)),

            result =
                if v = null or v = "" then
                    null
                else
                    try Number.From(v) otherwise null
        in
            result
in
    TryToNumber
Power Query

これを使えば、クエリ側はかなりスッキリ書けます。

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

    Added =
        Table.AddColumn(
            Source,
            "数量_数値変換",
            each fn_TryToNumber([数量]),
            type nullable number
        )
in
    Added
Power Query

「数値として読めるかどうか」という意図が、コードから一目で分かるようになります。

重要ポイントの深掘り

「エラーにする」のではなく「エラーになる行を先に見つける」

Power Query には「列の型を number に変えると、変換できない行がエラーになる」という仕組みがあります。
ただ、いきなり型変換してエラーを出してしまうと、

どの行が悪いのか分かりにくい。
クエリ全体がエラー扱いになって、後続の処理が見えなくなる。

というデメリットがあります。

今回のテンプレは、「型変換の前に、try ～ otherwise で“静かに”判定しておく」という発想です。
これを挟んでおくと、

エラー行だけを別テーブルに出せる。
正常行だけを本体として先に進められる。

という、かなり実務的な運用ができます。

「空欄」をエラーとみなすかどうかは最初に決める

空文字（””）や NULL を「数値エラー」とみなすかどうかは、業務ルール次第です。

「入力漏れも全部エラーとして洗い出したい」なら、空欄もエラー扱い。
「空欄は“欠損値”として許容し、エラーとは別に扱いたい」なら、空欄は除外。

この方針を最初に決めておくと、判定ロジックがブレません。
さっきの例では、条件式の中で「空欄もエラーに含めるかどうか」を切り替えられます。

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

「数値エラー行の検出」の本質は、次の二つです。

一度“数値に変換してみて”、成功したかどうかで「正常／異常」を判定する。
その判定結果を使って、エラー行だけを抽出したり、逆に正常行だけを残したりする。

この型さえ持っておけば、
数量・単価・金額・ポイント・残高など、あらゆる「数値であるべき列」に対して、
「どの行が怪しいか」を機械的に洗い出せるようになります。

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