Power Query 実務テンプレ | クレンジング（前処理）系：不正文字除去

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

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

今回のテーマは「Power Query で“データの中に紛れ込んだ不正文字・余計な文字をきれいに取り除く”実務テンプレ」を身につけることです。
ここでいう「不正文字」は、制御文字、印刷できない文字、想定外の記号、コピー元システム特有のゴミ文字などを含みます。

やりたいことは、次の状態を作ることです。
見た目は同じなのに、謎の文字が混ざっていて JOIN や集計がズレる、という事故を防ぐ。
「この文字は絶対に要らない」というルールを決めて、一括で削除できるようにしておく。

そのために、「不正文字除去用の関数を一つ作り、それを対象列（または全テキスト列）に適用する」という型をテンプレ化します。

どんな「不正文字」が問題になるのかを具体的にする

実務でよく出てくる不正文字の例

例えば、こんなものがよく紛れ込みます。

コピー＆ペースト時に混ざるタブや改行。
外部システムからの出力に含まれる制御文字（コード 0〜31 あたり）。
全角の謎記号や、想定していない絵文字。
「顧客コードは英数字だけのはずなのに、なぜかハイフンやスラッシュが混ざっている」などのパターン。

これらは見た目では気づきにくいのに、JOIN・GROUP BY・DISTINCT などで確実に悪さをします。
だからこそ、「この列にはこの文字種しか許さない」「それ以外は全部削る」という“フィルタリング発想”が効きます。

基本方針：許可する文字だけを残す（ホワイトリスト方式）

「何を消すか」ではなく「何を残すか」を決める

不正文字除去で一番安定する考え方は、「消したい文字を列挙する」のではなく「許可する文字だけを残す」やり方です。
例えば「顧客コードは英大文字と数字だけ」と決めるなら、
A〜Z と 0〜9 だけを残し、それ以外は全部捨てる、というロジックにします。

この発想にすると、想定外の変な文字が紛れ込んでも、自動的に落ちてくれます。
「ブラックリスト（消したい文字の列挙）」は、漏れが出やすいので、実務ではホワイトリスト方式を強くおすすめします。

例題1：顧客コードから「英数字以外」を全部除去する

想定する状況

顧客コード列に、次のような値が混ざっているとします。

“AB-123”
“Ａ１２３”
” 99X “
“X1/02”

本来は「半角英大文字＋数字だけ」にしたい。
つまり、最終的にはこうなっていてほしいイメージです。

“AB123”
“A123”
“99X”
“X102”

ここで使えるテンプレが、「許可文字だけ残す関数」です。

不正文字除去用の関数を作る（英数字ホワイトリスト）

まずは、専用の関数クエリを一つ作ります。

// クエリ名：fn_KeepAlphaNum
let
    KeepAlphaNum = (text as nullable text) as nullable text =>
        let
            // null はそのまま返す
            t =
                if text = null then
                    null
                else
                    text,

            // 許可する文字（半角英大文字＋数字）
            Allowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

            // 文字列を 1 文字ずつリストに分解
            chars =
                if t = null then
                    {}
                else
                    Text.ToList(Text.Upper(t)),

            // 許可文字だけを残す
            filtered =
                List.Select(
                    chars,
                    (c) => Text.Contains(Allowed, c)
                ),

            // 再び文字列に結合
            result = Text.Combine(filtered)
        in
            result
in
    KeepAlphaNum
Power Query

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

引数が null のときは、そのまま null を返す。
Allowed に「許可する文字」を並べる（ここでは英大文字＋数字）。
Text.ToList で文字列を 1 文字ずつのリストに分解する。
List.Select で「Allowed に含まれる文字だけ」を残す。
最後に Text.Combine でつなぎ直す。

これで、「英数字以外は全部落とす」関数ができました。

顧客コード列に適用する

この関数を顧客コード列に適用するクエリは、次のようになります。

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

    Cleaned =
        Table.TransformColumns(
            Source,
            {
                {"顧客コード", fn_KeepAlphaNum, type nullable text}
            }
        )
in
    Cleaned
Power Query

これで、顧客コード列の中身は、
“AB-123” → “AB123”
“Ａ１２３” → “A123″（全角→半角＋英大文字化）
” 99X ” → “99X”
“X1/02” → “X102”

のように、「英数字だけ」にきれいにそろいます。

例題2：氏名から制御文字・改行・タブを除去する

想定する状況

氏名列に、コピー＆ペースト由来の変な文字が混ざっているとします。

“田中太郎”
“山田花子\r\n”
“佐藤\t次郎”

見た目はほぼ同じでも、改行やタブが混ざっていると、
重複チェックや結合で事故ります。

ここでは、「印刷できない制御文字を全部落とす」関数を作ります。

制御文字を除去する関数

// クエリ名：fn_RemoveControlChars
let
    RemoveControlChars = (text as nullable text) as nullable text =>
        let
            t =
                if text = null then
                    null
                else
                    text,

            chars =
                if t = null then
                    {}
                else
                    Text.ToList(t),

            filtered =
                List.Select(
                    chars,
                    (c) =>
                        let
                            code = Character.ToNumber(c)
                        in
                            code >= 32 and code <> 127
                ),

            result = Text.Combine(filtered)
        in
            result
in
    RemoveControlChars
Power Query

ここでのポイントは、「文字コードで判定している」ことです。

Character.ToNumber(c) で、その文字のコードポイントを取得。
32 未満（制御文字）と 127（DEL）は除外。
それ以外は残す。

これで、改行（LF=10、CR=13）、タブ（9）などの制御文字が全部落ちます。

氏名列に適用する

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

    Cleaned =
        Table.TransformColumns(
            Source,
            {
                {"氏名", fn_RemoveControlChars, type nullable text}
            }
        )
in
    Cleaned
Power Query

これで、”山田花子\r\n” も “佐藤\t次郎” も、
制御文字が除去されたきれいな文字列になります。

全ての文字列列に一括で「不正文字除去」をかける

「どの列に変な文字がいるか分からない」ケース

列が多い、どこに不正文字が紛れているか分からない、という場合は、
「文字列型の列を自動で拾って、全部に関数をかける」テンプレが便利です。

例えば、「制御文字除去」を全テキスト列に適用するなら、こう書けます。

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

    TextColumns =
        List.Select(
            Table.ColumnNames(Source),
            (colName) =>
                Value.Type(Source[colName]) = type text
        ),

    TransformList =
        List.Transform(
            TextColumns,
            (colName) =>
                {colName, fn_RemoveControlChars, type nullable text}
        ),

    Cleaned =
        Table.TransformColumns(
            Source,
            TransformList
        )
in
    Cleaned
Power Query

同じパターンで、fn_KeepAlphaNum を使えば「全テキスト列を英数字だけにする」こともできますし、
「この列は英数字だけ」「この列は制御文字だけ除去」といった組み合わせも作れます。

重要ポイントの深掘り

「何を不正とみなすか」を先に決める

不正文字除去で一番大事なのは、「何を不正とみなすか」を列ごとに決めることです。

顧客コードや商品コードなら、「英数字だけ」「英数字＋ハイフンだけ」など。
郵便番号なら、「数字とハイフンだけ」。
氏名なら、「制御文字だけ除去して、漢字・ひらがな・カタカナ・記号は許容」。

この“列ごとのルール”が決まれば、あとはそれを M の関数に落とし込むだけです。

不正文字除去は「早めの段階」でやる

不正文字除去は、基本的に「型を text にそろえた直後」「JOIN・集計の前」に入れるのが鉄則です。

変な文字が残ったまま JOIN すると、結合漏れが出る。
GROUP BY や DISTINCT で、見た目同じなのに別グループ扱いになる。

前処理の早い段階で「トリム」「全角→半角」「不正文字除去」をセットで入れておくと、
後ろのロジックがかなりシンプルになります。

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

「不正文字除去」の本質は、次の二つに集約されます。

一つ目は、「許可する文字だけを残すホワイトリスト方式で考える」。
二つ目は、「そのロジックを関数化して、対象列（または全テキスト列）に一括適用する」。

この型さえ持っておけば、
外部システム由来のゴミ文字、コピー＆ペースト由来の制御文字、想定外の記号などに振り回されず、
「クレンジング済みのきれいな文字列」を前提に、JOIN・集計・分析を組み立てられるようになります。

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