ゴールのイメージをそろえる
今回のテーマは「Power Query で “桁数がおかしい値”を機械的にチェックする実務テンプレ」です。
やりたいことは、ざっくり言うとこうです。
「顧客コードは必ず 10 桁のはずなのに、8 桁や 11 桁が紛れ込んでいないか？」
「郵便番号は 7 桁固定のはずなのに、6 桁や 8 桁がないか？」
「社員番号は 6 桁固定なのに、5 桁や 7 桁が混ざっていないか？」
つまり、「桁数ルールに違反しているデータを見つける」のが目的です。
“エラーになる前におかしな値を炙り出す”ための、かなり強力な前処理です。
まず「桁数チェック」が必要になる典型パターンをイメージする
例題データを具体的に見てみる
次のようなテーブルを想像してください。
| 顧客コード | 顧客名 |
|---|---|
| C000000001 | 田中商事 |
| C000000002 | 鈴木工業 |
| C00000003 | 佐藤商会 |
| C0000000004 | 山田商店 |
| C000000005 | テスト顧客 |
ルールはこうだとします。
顧客コードは「C」＋9桁の数字 → 合計 10 文字であるべき
ところが、上のデータにはこういう問題があります。
C00000003 → 9 桁しかないC0000000004 → 11 桁ある
この「桁数がおかしい行」を、Power Query で確実に検出したい、というのが今回のゴールです。
基本の考え方：文字列にして Text.Length で桁数を数える
「桁数チェック」は Text.Length が主役
Power Query で桁数をチェックするときの基本は、これだけです。
1つの値を「テキスト（文字列）」として扱う
その文字列の長さを Text.Length で数える
期待する桁数と一致しているかどうかを判定する
なので、まずは「顧客コードの桁数」を列として追加してみます。
let
Source = ・・・前のステップ・・・,
AddedLength =
Table.AddColumn(
Source,
"顧客コード_桁数",
each
let
v = [顧客コード],
t =
if v = null then
""
else
Text.From(v),
len = Text.Length(t)
in
len,
Int64.Type
)
in
AddedLength
ここでやっていることを言葉で整理します。
顧客コードを v として取り出す。
NULL の場合は空文字にしておく（Text.Length でエラーにしないため）。
Text.From で文字列として扱う。
Text.Length で文字数（桁数）を数える。
この結果、「顧客コード_桁数」列にはこう入ります。
C000000001 → 10
C000000002 → 10
C00000003 → 9
C0000000004 → 11
C000000005 → 10
ここまで来れば、「桁数がおかしい行」を判定するのは簡単です。
ステップ1：桁数がルールと違う行だけを抽出する
「期待桁数と違う行」をエラー候補として取り出す
顧客コードは 10 桁固定、というルールだとすると、
「顧客コード_桁数 <> 10」の行が“桁数エラー行”です。
let
Source = ・・・前のステップ・・・,
AddedLength =
Table.AddColumn(
Source,
"顧客コード_桁数",
each
let
v = [顧客コード],
t =
if v = null then
""
else
Text.From(v),
len = Text.Length(t)
in
len,
Int64.Type
),
ErrorRows =
Table.SelectRows(
AddedLength,
each [顧客コード_桁数] <> 10
)
in
ErrorRows
この ErrorRows には、こういう行だけが残ります。
| 顧客コード | 顧客名 | 顧客コード_桁数 |
|---|---|---|
| C00000003 | 佐藤商会 | 9 |
| C0000000004 | 山田商店 | 11 |
これで、「桁数ルールに違反している行」を一覧にできます。
このテーブルを「エラーログ」として別シートに出したり、担当者に修正依頼をかける、という運用ができます。
ステップ2：逆に「桁数が正しい行だけ」を本体として残す
本番集計に使うテーブルから“桁数エラー行”を除外する
今度は逆に、「桁数が正しい行だけを残したい」場合です。
さっきと同じ桁数列を使って、「顧客コード_桁数 = 10」の行だけを残します。
let
Source = ・・・前のステップ・・・,
AddedLength =
Table.AddColumn(
Source,
"顧客コード_桁数",
each
let
v = [顧客コード],
t =
if v = null then
""
else
Text.From(v),
len = Text.Length(t)
in
len,
Int64.Type
),
ValidRows =
Table.SelectRows(
AddedLength,
each [顧客コード_桁数] = 10
)
in
ValidRows
これで、「顧客コードが 10 桁の行だけ」が残ります。
桁数エラー行は、本番の集計・分析からはきれいに除外されます。
実務では、
本体テーブル：ValidRows（桁数OKの行だけ）
エラーテーブル：ErrorRows（桁数NGの行だけ）
という2本立てにしておくと、運用がかなりスッキリします。
応用1：数字だけの桁数チェック（郵便番号・電話番号など）
「数字以外を除いてから桁数を数える」という発想
郵便番号や電話番号は、こんなパターンが混ざりがちです。
“123-4567”
” 1234567 “
“１２３４５６７”（全角）
これらを「7 桁の数字として正しいか？」と判定したい場合、
そのまま Text.Length を取ると、ハイフンや空白、全角の影響を受けてしまいます。
そこで、次のような流れにします。
全角→半角に変換する（必要なら）
数字以外の文字（ハイフン・空白など）を削除する
残った文字列の桁数を Text.Length で数える
例えば、郵便番号列に対してこう書けます。
let
Source = ・・・前のステップ・・・,
AddedZipLength =
Table.AddColumn(
Source,
"郵便番号_数字桁数",
each
let
v = [郵便番号],
t =
if v = null then
""
else
Text.From(v),
// 数字だけを抽出（0〜9以外を削るイメージ）
digits = Text.Select(t, {"0".."9"}),
len = Text.Length(digits)
in
len,
Int64.Type
)
in
AddedZipLength
これで、次のように判定できます。
“123-4567” → digits = “1234567” → 桁数 7
” 1234567 ” → digits = “1234567” → 桁数 7
“１２３４５６７”（全角） → Text.From の時点では全角のままなので、
全角→半角変換テンプレを先にかけておくと、digits = “1234567” になります。
あとは、「郵便番号_数字桁数 = 7 かどうか」で、桁数チェックができます。
応用2：複数列をまとめて桁数チェックしたい
顧客コード・郵便番号・電話番号を一気にチェックする
実務では、「桁数ルールを持つ列」が複数あることが多いです。
顧客コード：10 桁
郵便番号：7 桁（数字）
電話番号：10〜11 桁（数字）
こういう場合は、列ごとに「桁数列」を追加してもいいですが、
「桁数チェック結果（OK/NG）」だけをフラグとして持つのもよくやるパターンです。
例えば、「顧客コードの桁数が 10 でない行は NG」とするフラグ列を追加するなら、こう書けます。
let
Source = ・・・前のステップ・・・,
AddedFlag =
Table.AddColumn(
Source,
"顧客コード_桁数NG",
each
let
v = [顧客コード],
t =
if v = null then
""
else
Text.From(v),
len = Text.Length(t)
in
len <> 10,
type logical
)
in
AddedFlag
true なら桁数NG、false なら桁数OK、というフラグです。
このフラグを使って、
NG 行だけ抽出する（監査用）
NG 行を除外して本体を作る
という運用ができます。
重要ポイントの深掘り
「何を数えているか」を意識する（生文字か、数字だけか）
桁数チェックで一番ハマりやすいのは、
「生の文字列の長さを数えているのか」
「数字だけを抽出した後の長さを数えているのか」
が曖昧になることです。
顧客コードのように「フォーマットが完全固定」の場合は、生文字の長さで OK。
郵便番号や電話番号のように「ハイフンや空白が混ざる」場合は、
数字だけを抽出してから桁数を数える方が現実的です。
ここを最初に決めておくと、ロジックがブレません。
NULL や空文字を「エラー」とみなすかどうか
桁数チェックでは、NULL や空文字の扱いも重要です。
「値が入っていないこと自体をエラーとみなす」のか
「値なしは“別問題”として扱い、桁数チェックの対象外にする」のか
例えば、さっきの顧客コードの例では、
NULL や空文字を "" に変換してから Text.Length を取っているので、
桁数 0 として扱われます。
「桁数 0 も NG に含めたい」なら、len <> 10 で自然に拾えます。
「NULL は別扱いにしたい」なら、条件式の中で分岐させることもできます。
each
if [顧客コード] = null or [顧客コード] = "" then
null // チェック対象外
else
Text.Length(Text.From([顧客コード])) <> 10
このあたりは、業務ルールに合わせて設計していく部分です。
実務テンプレとしてのまとめ
「桁数チェック」の本質は、とてもシンプルです。
値をテキストとして扱い、Text.Length で長さ（桁数）を数える。
その桁数が「期待する桁数」と一致しているかどうかで、OK/NG を判定する。
そして、実務で効かせるためのポイントは、
必要に応じて「数字だけを抽出してから桁数を数える」
NULL・空文字・全角半角・ハイフンなどの扱いを、最初にルールとして決めておく
というところにあります。
ここまであなたが積み上げてきた前処理テンプレ（トリム、全角→半角、NULL 統一、不正文字除去など）と、
今回の「桁数チェック」を組み合わせると、
「コード系・番号系の品質チェック」がかなり強固になります。