パワークエリでこういった作業も自動化できますか？
その場合、「クエリのマージ」コマンドの「結合の種類」を設定すれば、2つの表の一致や差異のレコードを特定できますよ！
では、詳細を解説していきますね。
目次[閉じる]
はじめに
この記事はパワークエリの「クエリのマージ」コマンドの使い方を把握していることが前提です。
「クエリのマージ」コマンドの詳細は、以下の記事をご参照ください。
「結合の種類」とは、Power Queryエディター上で2つの表のマージ方法を指定する設定項目
「結合の種類」とは、「クエリのマージ」コマンドで起動される「マージ」ダイアログ上の設定項目の１つです。
以降、ワークシート上の「マージ」コマンドも同様。
通常、「クエリのマージ」コマンドは、Power Queryエディターでデータ転記を行うことが一般的ですが、この「結合の種類」の設定を変更することで、2つの表の一致や差異のレコードを特定することも可能です。
なお、この場合もデータ転記と同様、主キーを基準に一致しているか否かを判定することが基本です。
主キーとは、そのレコードが一意（重複していない）であることを示すためのコードや番号のこと（代表例として、社員番号や商品コードなど）。
この「マージ」ダイアログの「結合の種類」ボックスで設定できる内容は、以下の6パターンです。
- 左外部（デフォルト）
- 右外部
- 完全外部
- 内部
- 左反
- 右反
「左」や「右」といった表現が多いですが、「左」は「マージ」ダイアログの上側に設定したクエリ、「右」は「マージ」ダイアログの下側に設定したクエリをそれぞれ示します。
個人的には、「右」が付くものは指定するクエリを間違えてしまう恐れがあるため、パターン②⑥は覚えなくて良いと思います。
「結合の種類」各パターンの意味と使い分け
「マージ」ダイアログの「結合の種類」ボックスの6パターンそれぞれの意味と使い分けを順番に解説します。
解説に際し、「マージ」ダイアログの上側のクエリを「表A」、下側のクエリを「表B」とします。
- 本記事の前提
本記事は、主キーで管理されたマスタ同士の比較を前提に解説しています。
主キー以外の列を基準としたマージとも可能ですが、キーの重複状況等によってはマージ結果が異なる場合があるため、ご注意ください。
【パターン①】左外部
パターン①の「左外部」は、デフォルトのパターンとなり、表Aのレコードに対し、主キーを基準に表Bから任意の列をマージします。
表Aのすべてのレコードは残りますが、表B側は表Aに存在しない主キーのレコードは残りません。
また、表Aの主キーに一致する表B側のデータがない列は「null」になります。
パターン①はデータ転記をしたい場合に使います。
【パターン②】右外部
パターン②の「右外部」は、表Bのレコードに対し、主キーを基準に表Aから任意の列をマージします。
こちらもデータ転記をしたい場合に使いますが、「マージ」ダイアログの上下が逆のパターンになります。
あえてパターン①と使い分けを行う理由はないので、実務でパターン②は使わないで良いと思います。
「マージ」ダイアログで上下のクエリの指定に気を付ければ充分でしょう。
【パターン③】完全外部
パターン③の「完全外部」は、主キーを基準に表A・Bのすべてのレコードを1つの表へマージします。
一方の表にしかデータがない列は「null」となる。
表Bの列は通常のデータ転記（パターン①）と同様に、選択した列のみが展開されます。
マージする2つの表が同じ列構成の場合、列の展開時にプレフィックスのチェックをONにし、表Bのものを区別できるようにすると良いですね。
表Bのクエリ名が「商品マスタ_2022」の場合、列名の頭に「商品マスタ_2022.」が付加。
2つの表の不一致（「null」があるレコード）を特定できる以外にも、2つの表で共通する列（「単価」等）のデータの相違も確認できます。
パターン③は2つの表の全レコードを残し、一致と差異をまとめて整理したい場合に使いましょう。
ちなみに、うまく「条件列」コマンドを活用し、一致・差異のフラグ立てを行い、そのフラグをフィルターで任意のレコードを絞込むと便利です。
表A・Bの主キー列のいずれか一方が「null」ならTRUE等。
「条件列」コマンド・フィルターの詳細は、以下の記事をご参照ください。
【パワークエリ】表へ条件分岐の結果となる列を自動で追加できる「条件列」の使い方
【パワークエリ】「フィルター」の使い方｜条件に一致したレコード（行）以外を削除する方法【動画あり】
【パターン④】内部
パターン④の「内部」は、表A・Bで主キーを基準に一致するレコードのみを1つの表に残します。
パターン③と違い、表A・Bの一方にしか存在しないレコードはマージ対象外になるため、「null」の部分は発生しません（もともと「null」のデータ除く）。
パターン④は2つの表の一致レコードだけ必要な場合に使いましょう。
2つの表で共通する列（「単価」等）のデータの相違を中心に確認したい場合に便利です。
【パターン⑤】左反
パターン⑤の「左反」は、主キーを基準に表Aのみ存在するレコードを1つの表に残します。
当然ながら、表B側の列はすべて「null」となります。
そのため、表B側の列はわざわざ展開せず、表Bのテーブル列自体を削除しても問題ないですね。
パターン⑤は一方の表の差異レコードだけ必要な場合に使いましょう。
【パターン⑥】右反
パターン⑥の「右反」は、主キーを基準に表Bのみ存在するレコードを1つの表に残します。
パターン⑤の「マージ」ダイアログの上下が逆のパターンとなり、表A側の列はすべて「null」となります。
あえてパターン⑤と使い分けを行う理由はないので、実務でパターン⑥は使わないで良いと思います。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_PowerQueryデータ整形_クエリのマージ_結合の種類.xlsx
ブックを開いたら、「マージ」ダイアログの「結合の種類」の6パターンを実際に試してみてください。
「クエリのマージ」コマンドの使用手順は、以下の記事をご参照ください。
【パワークエリ】「クエリのマージ」の使い方｜別表の列データを転記する方法【動画あり】
さいごに
いかがでしたでしょうか？
「マージ」ダイアログの「結合の種類」ボックスを活用することで、主キーを基準に2つの表の一致・差異レコードを特定できます。
特に、同じ列構成でバージョン管理しているマスタ等のメンテナンスで役立ちますので、実務で機会のある方は活用してみてください。
なお、パワークエリの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
他にも、別表の行データを追加できる「クエリの追加」・「追加」コマンドも併用すると、複数の表を自由自在に集約できますね。
表の集約作業は、業界や業種を問わず実務での頻度が高いので、パワークエリをうまく活用していきましょう！
コメント Comments
コメント一覧
コメントはありません。
トラックバックURL
https://excel-master.net/data-utilization/data-shaping/data-transcription/power-query-query-merge-join-kind/trackback/