Power Query 実務テンプレ | クレンジング（前処理）系：マスタ未存在データ検出

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

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

今回のテーマは「Power Query で “マスタに存在しないコードを持つ明細行だけをきれいに炙り出す” 実務テンプレ」です。

やりたいことはこうです。
売上明細の顧客コードが、顧客マスタに存在しているかチェックしたい。
受注明細の商品コードが、商品マスタに登録されているか確認したい。
「マスタにいないコード」を持つ行を一覧にして、担当者に修正してもらいたい。

つまり、「明細側のキーが、マスタ側に“未存在”なデータを検出する」のが目的です。
これは、データ品質チェックの中でもかなり重要な“最後の関門”です。

まず「マスタ未存在データ」を具体的にイメージする

売上明細と顧客マスタの例

次の2つのテーブルを想像してください。

売上明細（Sales）

伝票番号顧客コード金額
1001C0011200
1002C002800
1003C999500
1004C0031500

顧客マスタ（Customer）

顧客コード顧客名
C001田中商事
C002鈴木工業
C003佐藤商会

ここで問題になるのは、売上明細の「C999」です。
顧客マスタには C999 が存在しません。
つまり、「マスタ未存在データ」です。

今回のゴールは、この「C999 を持つ売上明細行」を Power Query で確実に炙り出すことです。

基本の考え方：マスタとの結合結果から「マッチしなかった行」を拾う

「結合してみて、マスタ側が NULL の行」を探す

Power Query で「マスタ未存在データ」を検出するときの基本発想はこうです。

明細テーブルとマスタテーブルをキーで結合する。
マスタに存在するコードは、結合結果にマスタの情報が入る。
マスタに存在しないコードは、結合結果のマスタ側が NULL になる。
この「マスタ側が NULL の行」だけを抽出する。

これをテンプレとして形にしていきます。

ステップ1：売上明細と顧客マスタを結合する

Table.NestedJoin でキー結合する

まずは、売上明細（Sales）と顧客マスタ（Customer）を、顧客コードで結合します。

let
    Sales = ・・・売上明細・・・,
    Customer = ・・・顧客マスタ・・・,

    Joined =
        Table.NestedJoin(
            Sales,
            {"顧客コード"},
            Customer,
            {"顧客コード"},
            "Customer",
            JoinKind.LeftOuter
        )
in
    Joined
Power Query

ここで大事なポイントは、結合の種類を JoinKind.LeftOuter にしていることです。

左外部結合（LeftOuter）は、
左側（Sales）の行は全部残す。
右側（Customer）にマッチする行があれば、その情報がくっつく。
マッチしなければ、右側（Customer）の部分は NULL になる。

という動きをします。
この「マッチしなかったときに NULL になる」という性質を使って、「マスタ未存在」を検出します。

ステップ2：マスタ側が NULL の行だけを抽出する

結合結果から「マスタ未存在行」を取り出す

Table.NestedJoin の結果は、「Customer」という列に“マスタ側の小テーブル”が入った状態になっています。
マスタに存在するコードなら、その小テーブルには1行入っています。
マスタに存在しないコードなら、その小テーブルは空（行数 0）です。

この「行数 0 の小テーブル」を持つ行だけを抽出すれば、それが「マスタ未存在データ」です。

let
    Sales = ・・・売上明細・・・,
    Customer = ・・・顧客マスタ・・・,

    Joined =
        Table.NestedJoin(
            Sales,
            {"顧客コード"},
            Customer,
            {"顧客コード"},
            "Customer",
            JoinKind.LeftOuter
        ),

    NotFoundRows =
        Table.SelectRows(
            Joined,
            each Table.RowCount([Customer]) = 0
        )
in
    NotFoundRows
Power Query

この NotFoundRows には、こういう行だけが残ります。

伝票番号顧客コード金額Customer
1003C999500（行数0）

つまり、「顧客マスタに存在しない顧客コードを持つ売上明細行」です。
これを「マスタ未存在データ一覧」として、別シートに出したり、担当者に修正依頼をかけたりできます。

ステップ3：マスタ情報を展開して確認しやすくする

あえて展開して「NULL になっていること」を目で確認する

さっきの NotFoundRows は、Customer 列が「空の小テーブル」になっています。
これを展開してしまうと、マスタ未存在行ではマスタ側の列が全部 NULL になります。

let
    Sales = ・・・売上明細・・・,
    Customer = ・・・顧客マスタ・・・,

    Joined =
        Table.NestedJoin(
            Sales,
            {"顧客コード"},
            Customer,
            {"顧客コード"},
            "Customer",
            JoinKind.LeftOuter
        ),

    Expanded =
        Table.ExpandTableColumn(
            Joined,
            "Customer",
            {"顧客名"},
            {"顧客名"}
        ),

    NotFoundRows =
        Table.SelectRows(
            Expanded,
            each [顧客名] = null
        )
in
    NotFoundRows
Power Query

この書き方もよく使います。

マスタに存在するコードなら、顧客名が入る。
マスタに存在しないコードなら、顧客名が NULL になる。
顧客名 = null の行だけを抽出すれば、「マスタ未存在データ」になる。

どちらの書き方でも本質は同じですが、
「展開して NULL を見る」方が、初心者にはイメージしやすいかもしれません。

応用1：商品マスタ・部署マスタなど、複数マスタに対してチェックする

同じ型を繰り返すだけでよい

実務では、マスタは1つとは限りません。

顧客マスタ
商品マスタ
部署マスタ
担当者マスタ

など、複数のマスタに対して「未存在データ検出」をしたいことが多いです。
やることは同じで、

明細テーブル × 顧客マスタ
明細テーブル × 商品マスタ
明細テーブル × 部署マスタ

というように、マスタごとに LeftOuter 結合＋未存在抽出を繰り返すだけです。

例えば、「売上明細の顧客コードと商品コードを両方チェックしたい」なら、
顧客未存在一覧クエリ
商品未存在一覧クエリ

をそれぞれ作る、という形が現実的です。
（1つのクエリで全部やろうとすると、かえって読みにくくなりがちです）

応用2：マスタ未存在行を「本体から除外」したい場合

マスタに存在するコードだけを残して集計したい

今回のテーマは「検出」ですが、
「マスタに存在しないコードを本番集計から除外したい」というニーズもよくあります。

その場合は、さっきの逆をやれば OK です。

展開後に「顧客名 <> null」の行だけを残す
または
小テーブルの行数 > 0 の行だけを残す

という形です。

let
    Sales = ・・・売上明細・・・,
    Customer = ・・・顧客マスタ・・・,

    Joined =
        Table.NestedJoin(
            Sales,
            {"顧客コード"},
            Customer,
            {"顧客コード"},
            "Customer",
            JoinKind.LeftOuter
        ),

    Expanded =
        Table.ExpandTableColumn(
            Joined,
            "Customer",
            {"顧客名"},
            {"顧客名"}
        ),

    ValidRows =
        Table.SelectRows(
            Expanded,
            each [顧客名] <> null
        )
in
    ValidRows
Power Query

これで、「顧客マスタに存在する顧客コードを持つ売上明細だけ」が残ります。
マスタ未存在行は、本番集計からはきれいに除外されます。

重要ポイント1：キーのクレンジングを必ず先にやる

トリム・全角半角・桁数チェックをサボると“ニセ未存在”が量産される

マスタ未存在検出で一番やってはいけないのが、
「キーのクレンジングをしないまま結合する」ことです。

” C001″（前にスペース）
“C001 “（後ろにスペース）
“Ｃ００１”（全角）
“C001 “＋改行

これらは、マスタ側が “C001” だとしても、全部「別の値」です。
つまり、本当はマスタに存在しているのに、
クレンジング不足のせいで「マスタ未存在」と誤判定されます。

なので、マスタ未存在検出の前に、必ずこういう前処理を済ませておきます。

前後トリム（Text.Trim）
全角→半角（コード系）
改行削除
桁数チェック（必要なら）

「マスタと明細で、キーの表現ルールをそろえてから結合する」
ここを徹底するだけで、マスタ未存在検出の精度が一気に上がります。

重要ポイント2：「未存在を検出する」のか「自動補正する」のか

検出と補正は、役割が違う

今回のテンプレは「マスタ未存在データを検出する」ことにフォーカスしています。
つまり、

どのコードがマスタにいないのかを知りたい
その行を一覧にして、担当者に修正してもらいたい

という“監査・品質チェック”寄りの用途です。

一方で、「似たコードに自動で寄せる」「候補を提示して補正する」みたいな話になると、
それはもう別のレイヤー（ビジネスルール・人の判断）が必要になります。

Power Query でやるべきことは、

マスタ未存在行を確実に炙り出す
本体から分離しておく

ここまでです。
その先の「どう直すか」は、業務側と相談して決める領域になります。

重要ポイント3：結合方向と結合種別を意識する

なぜ LeftOuter なのか、AntiJoin という選択肢もある

さっきは LeftOuter 結合＋NULL 判定で説明しましたが、
実は「AntiJoin（反結合）」というもっとストレートなやり方もあります。

AntiJoin は、

左側の行のうち、右側にマッチしなかった行だけを残す

という結合です。
つまり、「マスタ未存在行だけ欲しい」という今回の用途には、かなりピッタリです。

let
    Sales = ・・・売上明細・・・,
    Customer = ・・・顧客マスタ・・・,

    NotFoundRows =
        Table.NestedJoin(
            Sales,
            {"顧客コード"},
            Customer,
            {"顧客コード"},
            "Customer",
            JoinKind.LeftAnti
        )
in
    NotFoundRows
Power Query

これだけで、「顧客マスタに存在しない顧客コードを持つ売上明細行だけ」が返ってきます。
中で NULL 判定をする必要すらありません。

LeftOuter＋NULL 判定は「考え方が分かりやすい」
LeftAnti は「書き方がシンプルで意図が明確」

どちらも正解なので、好みとチームのレベル感で選んでください。

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

「マスタ未存在データ検出」の本質は、次の二つです。

明細テーブルとマスタテーブルをキーで結合し、「マッチしなかった行」だけを抽出する。
その前に、キー列（コード）のトリム・全角半角統一・桁数チェックなどのクレンジングを済ませておく。

実装としては、

LeftOuter 結合＋マスタ側 NULL 判定
または
LeftAnti 結合で“未存在行だけ”を一発抽出

のどちらかをテンプレとして持っておけば十分です。

ここまであなたが積み上げてきた前処理テンプレ（トリム、桁数チェック、主キー重複、数値・日付変換など）と、
今回の「マスタ未存在データ検出」を組み合わせると、
「マスタと整合しないデータが本番集計に紛れ込む」リスクをかなりの精度で潰せるようになります。

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