Excel Power Query マージ【左外部結合】について

Power Query 第7回目。「クエリのマージ」については２回目です。
今回は、マージクエリのうち「左外部結合」について詳しく勉強してみたいと思います。

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・前回の内容は「マージクエリの概要」について全体的に把握してみました。

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替をお願いします(^^)/

「左外部結合」とは

・左外部結合は、「左側テーブル」の行すべてに「右側テーブル」のうち一致する行をマージすることです。
・この処理をイメージした図がこちらです。

・左側の円が「左テーブル」で全部が青く塗りつぶされている状態です。
・一方、右側の円が「右テーブル」で、KEYとなる照合列が合致している（重なった）部分だけが青塗になっている状態です。

・照合列が合致していない「空白」の部分は切り捨てられます。

「左外部結合」でマージしてみます

わかりやすくするために、使用するデータをシンプルにしました。

・「左テーブル」は[ID][名前][販売数]のテーブルで、個人の販売数データです。
・「右テーブル」は[ID][所属店舗][販売数]という店舗別販売数データのテーブルです。

・比べてみると、「左テーブル」の[ID]1003が「右テーブル」にはありません。
・また、「右テーブル」の[ID]1004が「左テーブル」に無いことも確認できます。

・それでは、この状態で「左外部結合」でマージしてみましょう。

「マージ」処理を実行します

・[データ]→[データの取得]→[クエリの結合]→[マージ]でマージダイアログボックス呼び出します。

対象テーブル選択・配置・列のペア選択まで

・用意できた「マージ」ダイアログボックスの画像を先にお見せします。

・それでは、順を追って説明します。

対象テーブルを配置します

・上が「左側」でしたね。「左テーブル」を選択・配置します。
・下側に「右テーブル」を選択・配置します。

「列のペア」を選択します

・[ID]列をKEYとしてマージ処理を行いたいと思います。
・「左テーブル」「右テーブル」それぞれの [ID]列 を「列のペア」として選択します。

「結合の種類」を選択します

・今回は「左外部（最初の行のすべて、および2番目の行のうち一致するもの）」を選択します。
・読み替えると、左外部結合（左側テーブルの行のすべて、右側テーブルのうち一致する行）です。

・ダイアログ最下部にこの設定で「一致している行数（ここでは４行中３行）」が表示されています。

・最後に「OK」選択でマージが実行されます。

マージされたテーブル列の展開

・マージ実行後、下図のように 結合後のクエリが Power Query エディター に表示されます。
・ベーステーブルには左テーブルのすべての列に加えて、新しい列 [右テーブル]（テーブル名）が追加されています。[右テーブル]列には右テーブルに対応する値が行単位で格納されています。

・すると、下図のように「右テーブル」のフィールド列が展開されます。

・ベーステーブルの「左テーブル」の行はすべて表示されていますが、照合のなかった「４行目」については「null」となっています。

・「閉じて読み込む…」でシートの場所にもとのテーブル下部を選択して、テーブルで表示してみたのが下の画像です。

・マージ前に比べてみたとおり、「左テーブル」の[ID]1003が「右テーブル」には無かったので、「右テーブル」の値はマージ後の結果として「空白」が表示されています。

・「右テーブル」にあった[ID]1004が「左テーブル」には無かったので、マージ後のテーブルには取り込まれていません。

詳細エディターでM言語のコードを確認してみます

「左外部結合」の M関数（テーブル関数）

Table.NestedJoin(左テーブル, {“ID”}, 右テーブル, {“ID“}, “右テーブル”, JoinKind.LeftOuter),

【構文】
Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number, optional keyEqualityComparers as nullable list) as table

各テーブルの選択されたキー列の値が等しい場合、各テーブルの当該行を結合します。その結果を newColumnName という名前の列に入力されます。

joinKind で実行する結合の種類の指定ですが、joinKind が指定されなかった場合は、
既定では JoinKind.LeftOuter「左外部結合」が実行されます。

「左外部結合」マージで注意すべき点など

「左外部結合」は Excel の VLOOKUP と似ているけど

・先ほどのマージ後のテーブルを見ると、「左テーブル」の[ID]を基準Key列として「右テーブル」の[ID]で検索してHITした行を結合しています。

・これは、Excel の VLOOKUP と同じような動きです。

・では、本当に同じなのかを確認してみましょう。

Key列のデータが一意でない場合の動作

・「右テーブル」の４番目の行データを１番目と同じ「1001」に変更してからデータを更新してみました。

・その結果が次の画像です。

・[ID]「1001」と合致した「右テーブル」の行が二つありました。

・その場合、合致した行の数だけ「左テーブル」の行が追加されるのです。

・VLOOKUP とは違う動きですね！

・VLOOKUP の場合は、上から検索して最初にヒットしたデータ行の値を引き当てます。

・一意でない列データで照合させる場合は、この点を考慮しておく必要があるので注意しましょう！

「左外部結合」で集計した場合

・では「展開」の際に「集計」オプションを選択した場合どうなるのか確認してみます。

・[ID]の書式は数値でしたので集計からは外して実行しています。

・[所属店]はテキストデータのためカウント、[販売数]は数値なので合計で集計されました。

・[所属店]の カウント は、Excel の COUNTIF と同じ感じです。

・[販売数]の 合計 は、Excel の SUMIF と同じ感じです。

・複数列を選択して照合すれば、「COUNTIFS」「SUMIFS」と同じようになると思われます。

まとめ（おわりに）

・以上で、「左外部結合」についての勉強は終了します。

・「左外部結合」は、よく VLOOKUP と比較されますが、その違いがわかりましたでしょうか。

・比較するKey列のデータが「一意」でない場合は注意が必要です。逆に、「一意」のデータならば VLOOKUP よりも高速ですし、再計算によるストレスもなくなるので非常に有効です。

・サンプルファイルはデータ数が少ないですが、実際にファイルの結合を試してみてください。

・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事で使用したサンプルファイルをリンク先に登録しています！

過去の記事で使用したサンプルファイルもダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください