左反結合でマスタに一致しない受注明細を見つける

LWP | 左反結合でマスタに一致しない受注明細を見つける

左反結合でマスタに一致しない受注明細を見つける

図解1|マスタに一致しない明細を、別表で確認する

受注明細と商品マスタを商品コードで照合し、一致しないP999の明細を別表へ残す図

図1|マスタに一致しない明細を、別表で確認する。

解説1|マスタに一致しない明細を、別表で確認する

ストーリー

フナさん「マスタに見つからない商品コードを、受注明細から確認したいです。元の明細は残しておきます。」

シャケモン「確認用のクエリを分けて、左反結合で不一致の明細だけを残しましょう。」

淡水社は、全国規模で中華食材を扱う架空の卸売会社である。受注明細には受注番号・明細番号・商品コード・数量・取引単価があり、商品マスタは商品コードに商品名や規格を対応させている。今回は商品コードに一致相手があるかを調べる。

確認用に残す行

受注明細の一行は、ある受注の一つの商品明細を表す。商品コードがマスタに見つからなければ、その明細は確認候補になる。左反結合は、右の表に一致する行がない左の行を残す方法である。

確認したい対象

この例での置き方

不一致を探す表

左:受注明細の比較用コピー

登録済みコードを照合する表

右:商品マスタ

一致の条件

両方の商品コードが同じ

残す行

右に一致相手がない受注明細

読むポイント

図のP999は、検出を確かめるためコピーに入れた誤記である。実業務で抽出された行も、誤記なのか登録漏れなのかを元の記録と照合する。抽出できたことだけで、商品マスタへの追加や受注明細の削除を決めない。

図解2|一行の意味と、照合キーを決める

受注明細の識別に使う受注番号と明細番号、商品照合に使う商品コードを区別する図

図2|一行の意味と、照合キーを決める。

解説2|一行の意味と、照合キーを決める

ストーリー

フナさん「O001には、干ししいたけと豆板醤の二つの明細があります。」

シャケモン「受注番号は明細を一意にしませんね。商品マスタとの照合には商品コードを使います。」

受注明細の識別は受注番号と明細番号の組合せで行い、商品マスタとの関係は商品コードで持つ。何を同じと見るキーなのかを、表ごとに決める。

正常な入力例

受注番号

明細番号

商品コード

数量

取引単価

受注金額

O001

1

P001

6袋

1,200円/袋

7,200円

O001

2

P002

4本

800円/本

3,200円

O002

1

P001

3袋

1,200円/袋

3,600円

商品マスタはP001=干ししいたけ500g/袋、P002=豆板醤1kg/本で、1コードにつき1行とする。コードは両側とも文字列。金額は数量×取引単価の教材値で、税・送料・値引きは含めず、売上確定額とも区別する。単価は受注時の条件であり、照合時のマスタ価格から再計算しない。

読むポイント

明細は3行、受注金額は14,000円である。数量列に「袋」「本」の文字を直接混ぜて計算するのではなく、実際のクエリでは数値の数量と単位の意味を分けて管理する。今回のM例は数量を6・4・3、取引単価を1200・800・1200として扱う。

図解3|左反結合は、一致しない左の行を残す

左反結合で一致する2明細を除き、P999の1明細を確認表へ残す図

図3|左反結合は、一致しない左の行を残す。

解説3|左反結合は、一致しない左の行を残す

ストーリー

シャケモン「P999に一致相手がないので、比較用コピーのこの明細だけが残ります。」

フナさん「元の数量と取引単価も一緒に確認できますね。」

比較用コピーは、正常な受注明細のO002・明細1だけをP001からP999へ変えたものとする。商品マスタはP001とP002のままである。文字列の商品コードを両側で選び、左反結合を指定する。

サンプルコード 確認用クエリの結合部分

既存クエリ「受注明細_比較用」と「商品マスタ」を前提にしたM式である。前者には前節の列と受注金額があり、後者は商品コードを一意に持つ。

01

let

02

    orders = 受注明細_比較用,

03

    products = 商品マスタ,

04

    unmatched = Table.NestedJoin(

05

        orders, {"商品コード"},

06

        products, {"商品コード"},

07

        "照合先", JoinKind.LeftAnti

08

    ),

09

    result = Table.RemoveColumns(unmatched, {"照合先"})

10

in

11

    result

結果と読むポイント

O002・明細1、商品コードP999、数量3、取引単価1200、受注金額3600の1行が残る。照合先列を展開する必要はなく、掲載式では最後にその列を外して元の明細列だけを見せている。

左反結合の結果に出なかった行も、元の受注明細には残っている。照合専用の出力と、業務の原記録を区別しよう。

図解4|商品名が空でも、キーは一致する場合がある

マスタの商品名がnullでも商品コードは一致する場合を示す図

図4|商品名が空でも、キーは一致する場合がある。

解説4|商品名が空でも、キーは一致する場合がある

ストーリー

フナさん「商品コードは登録済みでも、商品名が未入力ということはあります。」

シャケモン「では、正常なコードのまま、マスタの商品名だけを空にした別のコピーで比べましょう。」

商品コードの不一致と、商品名の未入力は、異なる箇所の不備である。前者は表同士のキーの対応、後者はマスタの属性の内容を調べている。

二つの比較条件

比較

受注明細

商品マスタ

左反結合の結果

コードの誤記

O002・1だけP999

P001・P002が存在

P999の1明細

名前の未入力

全て正常なP001・P002

P002の商品名だけnull

0行

後者ではP002の行自体がマスタに存在するため、商品名がnullでもキーは一致する。左外部結合して商品名を展開した後、そのnullだけを抽出する方法では、コード不一致と名前未入力を同じ結果に混ぜてしまうことがある。

読むポイント

マージ直後の照合先列に入るTableは、一致する行を持つ表である。一致相手がなければ中は0行で、「表の件数がnull」となるわけではない。展開後の属性のnullと、照合先の有無を分けて読む。

左反結合が0行でも、商品名の入力完了やマスタ全体の正しさまで確認できたことにはならない。

図解5|左右を入れ替えると、確認する対象が変わる

左に置く表を入れ替えると不一致の明細と受注に登場しない商品の違いが生じる図

図5|左右を入れ替えると、確認する対象が変わる。

解説5|左右を入れ替えると、確認する対象が変わる

ストーリー

シャケモン「今度は商品マスタを左にして、同じ比較用の受注明細と結合してみます。」

フナさん「左の一行が商品になると、明細の不一致とは別の確認になりますね。」

左反結合で残るのは、いつも左の表の行である。商品マスタを左にすると、受注明細に一度も登場しない商品コードを持つマスタの行が結果になる。

左右を入れ替えた比較

受注明細の比較用コピーにはP001・P002・P999が登場し、商品マスタにはP001・P002がある。

左の表

右の表

残るもの

結果

受注明細

商品マスタ

マスタに一致相手がない明細

P999の1行

商品マスタ

受注明細

明細に登場しない商品

0行

逆向きが0行なのは、P001もP002も受注明細に登場しているためである。受注明細のP999に登録先があることを示す結果ではない。

読むポイント

表名と結合の種類だけを記録しても、確認した意味は伝わらない。「左の一行は何を表すか」「右の何と比べるか」「何が残ったか」を併記しよう。商品マスタ側を確認する場合も、対象期間の受注に登場しなかっただけで、不要な商品と断定しない。

図解6|正常・誤記・修正後を、コピーで比べる

正常・誤記・修正後のコピーで不一致件数0・1・0を確認する図

図6|正常・誤記・修正後を、コピーで比べる。

解説6|正常・誤記・修正後を、コピーで比べる

ストーリー

フナさん「抽出された1行の3,600円を、元の注文の記録と照らし合わせます。」

シャケモン「正常時に0行、誤記を入れたときに1行、戻した後に0行。この三つで確認しましょう。」

不一致が0行という結果だけでは、意図した検査が働いているか分かりにくい。練習用コピーで検出対象を一つ作り、どの明細が残るかを確かめると、照合キーや左右の取り違えも見つけやすい。

検証結果

条件

不一致の件数

対象の受注金額

正常なP001・P002

0行

対象なし

O002・1をP999へ変えたコピー

1行

3,600円

同じコピーをP001へ戻す

0行

対象なし

元の正常な受注明細は3行・14,000円のまま保持する。確認用の1行を原記録から削除すると、後続集計の金額も変わるため、修正の判断は別に行う。

まとめと今回の仕掛け

左反結合は、一致相手がない左の行を残す。商品名のnullで代用せず、照合したいキーを直接比較した。抽出後は誤記・登録漏れなどの原因を調べ、修正した結果を再照合する。

出典

著者原稿『図解と実習で学ぶ Power Query GUI』V052の6.3.1と6.4.2の左反結合部分をもとに構成。仕様はMicrosoft LearnのLeft anti joinを参照した。