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

Power Query 第9回目。「クエリのマージ」については4回目です。
今回は、マージクエリのうち「完全外部結合」についてサンプルデータを操作しながら詳しく勉強してみたいと思います。

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

・前回の内容は「右外部結合」によるマージについてです。
・「クエリのマージ」全般の概要についての記事リンクはこちらです。

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

「完全外部結合」とは

・「完全外部結合」は、「左側テーブル」「右側テーブル」ともに全ての行が表示されます。
・すなわち一致する行はマージしたうえで、一致しなかった行もすべて表示します。
・この処理をイメージした図がこちらです。

・左側も右側も両方の円が全部青く塗りつぶされている状態です。
・KEYとなる照合列が合致している（重なった）部分はマージ結合されて表示され、外側の部分は結合されていない部分ですが表示はされるということです。

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

使用するデータは、あえて「左外部結合」「右外部結合」と同じものを使います。

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

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

・この比較はデータ量が少ないからできることで、通常は簡単には比較できません。

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

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

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

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

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

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

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

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

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

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

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

・今回は「完全外部（両方の行すべて）」を選択します。
・読み替えると、完全外部結合（両方のテーブルのすべての行）です。

・ダイアログ最下部に表示されたテーブルの一致数は、「左テーブル」が４行中３行、「右テーブル」も４行中３行 と表示されています。

・最後に「OK」選択でマージを実行します。

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

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

・３行目が「null」表示です。ID「1003」が最下行に移動しています。

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

・照合のなかった「5行目」は「null」となっています。

・「左テーブル」のID「1003」は「右テーブル」の結合が無かったので最下部になっていたようです？

・「左テーブル」の３行目「null」に対しては「右テーブル」のID「1004」が表示されています。

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

・確かに「左テーブル」「右テーブル」ともにすべて表示されています。

・[ID]の表示順がなぜこのようになるのかは不明です。

・試しに「右テーブル」の「ID」列は表示しない設定で展開してみましたが、表示順は変わりませんでした。

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

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

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

・FullOuter = 完全外部　ということですね。

「Key列」の「null」表示を補完して解消する

・「左テーブル」Key列の「ID」列に「Null」表示があるのが気になっていました。

・よく見ると、「右テーブル」Key列の「ID」列にちゃんとデータがあります。

「カスタム列」で左右の列データを補完する

・設定ではどうやっても「null」を埋めることはできません。

・「条件列」または「カスタム列」を使って、左右の「ID」データを補完してみましょう。

・どちらも同じ結果ですので、使いやすい方でやってみましょう。

「条件列」を使う方法がこちら

・Power Query エディターの[列の追加]タブ → [条件列]リボン クリックで下図の「条件列の追加」ダイアログを表示します。

順番に「条件」を設定していきます。
❶ 「列名」で対象の列名をプルダウンから選択します
❷ 「演算子」をプルダウンから選択します（ここでは「指定の列に等しい」を選択）
❸ 「値」に「値を指定します」「列の選択」「パラメーター」から選択して指定します

結果（then）
❹ 「出力」に「値を指定します」「列の選択」「パラメーター」から選択して指定します

それ以外の場合（else）
❺ 「それ以外の場合」に「値入力」か「列選択」「パラメーター」を指定します
❻ [句の追加]ボタンを押すと条件式を追加（ネスト）設定できます
❼ [OK]　設定終了後押下します

・操作しているGIF画像を下に貼っておきます。

「カスタム列」を使う方法がこちら

・VBAを使っている方なら手動で書いた方が早いかもしれません。
・Power Query エディターの[列の追加]タブ → [カスタム列]リボン クリックで下図の「カスタム列」ダイアログを表示します。

・「カスタム列の式」に「式」を書いていきます。

IF式で列データを比較して補完させます

・下が、実際に入力操作している 動画 です。
・まずは「カスタム列」が出来上がるのを見てください。

・入力した式は次のとおりです。

・[ID]のデータが「null」だった場合[右テーブル.ID]を表示。そうでない場合[ID]を表示します。

・シンプルな IF条件式 ですね。VBAとほぼ一緒ですが、「end if」が不要なところが違います。

・「使用できる列」からダブルクリックまたは「挿入」ボタンで適用できるので楽です。

・式が不完全なら、最下部に注意（エラー）が表示されます。

・詳細エディターで確認した M 言語の関数式は次のようになっています。

・Table.AddColumn が列を追加する関数ですね。列名が “カスタム” となっています。

・これで「ID」列の「null」がなくなりました。

・元の左右「ID」列は削除してしまっても大丈夫です。

・「カスタム列」を「ID」に変更してソートしましょう。。。。。。。

まとめ（おわりに）

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

・「完全外部結合」は、とにかく左右の「テーブル」をすべて表示するものでした。

・ついでに「カスタム列」「条件列」を追加してマージできていない「null」表示のKey列データを IF条件式 を使って補うようにしてみました。

・参考になったなら幸いです(^^ゞ　使ったデータは登録しておきます。

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

まとめと感想など

【今後の記事について】

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

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

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

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

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