【Power Query】結合で行が増える原因と重複の検知方法

名前をキーにして、取込データに別の表のコードを結合したところ、行が増えて合計が合わなくなることがあります。原因は、結合キーが相手の表で重複していることです。

取込処理では、この重複を結合の前に見つける判定を入れました。この記事では、行が増える最小例と、Table.Groupで件数を持たせて検知する方法、集計に使うときにエラーで止める書き方をまとめます。

キーの型が違って結合できない場合は、【Power Query】キーの型がバラバラで結合できない時の対処法で扱っています。今回は逆で、結合はできるのに行が増えるケースです。

行が増える最小例
行が増える仕組み
先にGroupで件数を持たせて検知する
集計に使うなら、エラーで止める
表記ゆれは重複に数えない
応用：同名が正当に複数いるとき
まとめ

行が増える最小例

取込データ（Pay）と、名前からコードを引くマスタ（Master）を用意します。マスタには「Sato」が2人（コード101と205）います。

Pay：Name	Amount
Sato	100
Suzuki	200
Tanaka	300
Ito	400

Master：Code	Name
101	Sato
102	Suzuki
103	Tanaka
205	Sato

名前をキーに、PayへMasterのコードを左外部結合します。

let
    Pay = Table.FromRows({{"Sato", 100}, {"Suzuki", 200}, {"Tanaka", 300}, {"Ito", 400}}, {"Name", "Amount"}),
    Master = Table.FromRows({{101, "Sato"}, {102, "Suzuki"}, {103, "Tanaka"}, {205, "Sato"}}, {"Code", "Name"}),
    Nested = Table.NestedJoin(Pay, {"Name"}, Master, {"Name"}, "M", JoinKind.LeftOuter),
    Joined = Table.ExpandTableColumn(Nested, "M", {"Code"})
in
    Joined

結果は5行です。Satoが2行（コード101と205）に増え、Amountの合計は1000から1100になります。ItoはマスタにないのでCodeはnullです。

エラーは出ません。合計だけがずれます。

行が増える仕組み

Table.NestedJoinは、一致した行をネストしたテーブルとして新しい列に入れます。この時点では4行のままで、Satoのネストしたテーブルが2行になっています。Table.ExpandTableColumnで展開したときに、1行が2行に増えます。

Table.Joinは展開の手順がなく、結合した時点で5行になります。キー列の名前が両方の表で同じ「Name」でも結合でき、結果の列はName、Amount、Codeの順でした。エラーになるのは、キー以外の列名が左右で重なるときで、メッセージは「結合処理で、重複する列名を含むテーブルを生成することはできません」でした。その場合は、片方の列名を変えてから結合します。

行が増えるかどうかは、どちらの表にキーの重複があるかで決まります。次の表は、今回の例で測った行数です。取込側にSatoが2行ある別データ（3行）も使いました。

取込データ	マスタ	結合	結果の行数
4行	Satoが2人	左外部	5
4行	Satoが2人	内部	4
3行（Satoが2行）	重複なし	左外部	3
3行（Satoが2行）	Satoが2人	左外部	5

内部結合でも、マスタ側に重複があれば行は増えます。片側だけの重複では増えず、両側に重複があると掛け算になります。

先にGroupで件数を持たせて検知する

結合の前に、マスタをキーごとにまとめて、件数と候補のコードを持たせます。結合先が1キー1行になるので、結合しても行は増えません。

let
    Pay = Table.FromRows({{"Sato", 100}, {"Suzuki", 200}, {"Tanaka", 300}, {"Ito", 400}}, {"Name", "Amount"}),
    Master = Table.FromRows({{101, "Sato"}, {102, "Suzuki"}, {103, "Tanaka"}, {205, "Sato"}}, {"Code", "Name"}),
    Grouped = Table.Group(Master, {"Name"}, {
        {"Cnt", each Table.RowCount(_), Int64.Type},
        {"Codes", each Text.Combine(List.Transform(List.Sort([Code]), Text.From), "/"), type text}
    }),
    Nested = Table.NestedJoin(Pay, {"Name"}, Grouped, {"Name"}, "Info", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Nested, "Info", {"Cnt", "Codes"}),
    Status = Table.AddColumn(Expanded, "Status", each
        if [Cnt] = null then "NotFound"
        else if [Cnt] > 1 then "Duplicate"
        else "OK", type text)
in
    Status

結果は4行のままで、合計は1000です。SatoはCntが2、Codesが「101/205」で、Statusは「Duplicate」です。ItoはCntがnullなので「NotFound」になります。Cntが2以上の行が、重複キーに当たった行です。Codesに候補を並べておくと、どのコードで迷っているかが分かります。

結合だけの結果は5行で合計1100、Groupで件数を持たせた結果は4行で合計1000になっている2つの表を並べたExcelの画面 — 再現用のコードの実行結果です。左は結合だけ、右はGroupで件数を持たせた場合です。

Table.Groupの詳しい引数は、Microsoft LearnのTable.Groupにあります。

集計に使うなら、エラーで止める

結合した結果を集計に使うなら、重複したキーがある時点でエラーで止めます。出てきた結果を人が目で点検して見つける運用は、設計として採りません。重複は、集計に入る前に止めます。

Status列は、止めるかどうかの判定と、どのキーが重複したかを知るために使います。上のコードのinの前に次の2行を足します（Statusの行の末尾にカンマを付けます）。

    Dups = Table.SelectRows(Status, each [Status] = "Duplicate"),
    Result = if Table.RowCount(Dups) > 0
        then error Error.Record("DuplicateKey", "duplicate keys in master", Dups[Name])
        else Status

inの後ろはResultに変えます。重複があるとエラーになり、重複したキーの一覧（今回は「Sato」）がエラーの詳細に入ります。

止めるときは、取込データに出てくるキーだけを見ます。マスタ全体に重複があっても、取込データにそのキーがなければ、行は増えません。今回の例では、Satoを除いた3行は、マスタにSatoの重複があっても3行のままで、エラーにもなりませんでした。マスタ全体の重複で止めると、使わないキーの重複でも止まってしまいます。

止まったら、マスタ側の重複を直して再実行します。結合の後で行を直すのではなく、結合の前の元データをきれいにしておく設計が前提です。

行数を比べるだけの方法もあります。結合の前後で行数が違えばエラーにします。検知はできますが、どのキーが原因かは分かりません。原因まで知りたいときは、Groupで件数を持たせる方法が向いています。

表記ゆれは重複に数えない

Group・結合ともに、既定では完全一致で比べます。「Sato」「sato」「Sato 」（末尾にスペース）は、別のキーです。Groupは3つに分かれ、「Sato」を結合してもコード101の1行にしかなりません。表記ゆれによる重複は、この方法では検知できません。キーをそろえる前処理は、別に入れる必要があります。