タグ別アーカイブ: 結合種類

【豆知識】どこの行が変わったかを調べる方法

大量のデータを扱っていると、2つの表を比べて「一体、どこの行が変わったのか?」を調べる時がたまにありますよね?

1万行を超すデータだと、関数を使ってもとても面倒な作業です

実はPower Queryの「クエリのマージ」において、結合種類を「完全結合(両方の行すべて)」に指定した上で、「条件列」を組み合わせると、簡単にできます

使用データと行いたいこと

下の図のように、「元データ」と「修正後」の2つの表があったとします

元データと修正後の間で「金額」が変わった行を「OK」「×」で判定して、新たな列に出力します

この「金額」が変わったという判定に当たっては、「ID」「客先」の組み合わせも考慮します

ですので、判定すべき個所は次の画像のようになります

今回のポイント

通常、クエリのマージを行った場合、結合の種類は次の画像の方法を使用するのが一般的だと思います

この場合は、2つのクエリの結合関係は次の画像のようになります

ところが、マージ方法を「完全外部」とすると次の画像のようになります

このマージ方法の場合、「片方の円しか無いものはどう出力されるか?」が今回の大きなポイントです!

では、それぞれの表からクエリ名が「元データ」「修正後」という2つのクエリが作成してあることを前提として、本格的な解説を始めます

2つのクエリのマージ

今回、2つのクエリをマージする際に気を付けることは、次の画像(列名の右横に番号が採番されていることに注目)のように照合列を複数指定することです

でないと、行の列の中で「客先」が変わっていても「判定」されません

照合列を複数指定する時は、Ctrlキーを押しながら選択を行ってください

条件列の追加

前述のクエリのマージを、展開する際には2つのクエリの違いが分かるように「元の列名のプレフィックス」にチェックを入れておきます

そして、マージしたクエリを展開すると次の画像のようになります

ちょっと上の画像だと見づらいので、1つのクエリの列だけ表示したのが次の画像です

「元データ」のクエリにしかない行は、プレフィックスが「修正後」の列では全て「null/空欄」となります

逆に「修正後」のクエリにしかない行は、プレフィックスが無い列が「null」になります

今回のポイントでも前述しましたが、金額が変わった行とは「金額自体」が変わった行と、「ID」や「客先」などの「行の情報」が変わった行の2つのケースがあります

「金額の列」はプレフィックスが「無し」と「有り」の2つがありますが、「行の情報」が変わった場合はどちらかが「null」になります

つまり、条件列を挿入して「金額」を比べれば、金額変更の2のケースが網羅できます

ですので、次のように条件列を指定します

まず条件設定です

上の条件が満たされた場合は「OK」を出力します

条件が満たされない場合には「×」を出力します

条件列を指定してエクセルシートに出力すると、次の画像のようになります

IDに注目して、「×」を見ていくと変わった行が一目瞭然です

<まとめ>

 今回は、マージの結合にて「完全結合」の仕組みを活用して2つの表から「変化した行」を判定する方法を解説しました

 Power Queryでは「元の列名のプレフィックス」を指定すれば、マージしたク エリを展開した際に「どのクエリ」の分なのかが列名で分かるのも便利です

 今回はクイズやパズルのような内容でしたが、Power Queryの便利さに触れるにはとても良い内容だったと思います

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村