前回の続きで、パワークエリを使用して行だけでなく列についても、値の変更もあわせて変更箇所を特定できるようにしたいと思います

ポイント

２つの表を結合するのは前回と一緒です

今回はピボット解除を行い、複合キーを作ったうえで結合を行います

使うデータ

Before、Afterの２つの表を用意するのですが、キー（りんご、みかん・・）を行単位で作成し、列は月単位で作成します

最後は２つの表でどこが変わったかを明確にできるようにクエリを作成します

ピボット解除＆複合キー作成

BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます

その後、「列のマージ」を使用して、複合キーを作成します

この複合キーを使用して、BeforeとAfterの２つの表を結合（マージ）します

マージ処理

ポイントでも前述したように、２つの表を複合キーを使用してマージ処理します

マージ種類は通常でいけば、完全結合がよいです

「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます

但し、こちらは用途にあわせて６つの結合種類を使いわければよいかと思います

各種調整

判定列の追加

マージ後は、条件列を使用して〇×を判定する列を追加することができます

複合キーの分解

マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます

複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します

そうすると、BeforeとAfterの比較が行いやすくなります

＜まとめ＞

今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました

ポイントはピボット解除と複合キーの作成により、２つの表を比較しやすい形で結合することです

ぜひ有効活用してみてください

にほんブログ村

２つの表の間でどの行が追加されているのか？削除されているのか？を適時確認・集計できるととても便利です

受注のキャンセルデータの管理やミスの発見など、2つの表の適時確認・集計の活用の場はかなり広いです

Power Queryの結合（マージ）には6種類が用意されていますので、それぞれを使い分けるとかなりの集計上手になれます！

今回使用するデータ

今回は１から始まる連番で管理された２つの表を使用します

結合（マージ）

結合の種類は下の画像の赤色の箇所で選択・指定できます

左外部

左の表の全てと、右の表にて左の行と一致する行を結合します

右外部

右の表の全てと、左の表にて右の行と一致する行を結合します

完全外部

一致する一致しないに関わらず、２つの表に存在する行を網羅します

右の表で追加された行や削除された行が一目でわかるようになります

内部

２つの表の間で一致する行のみ結合します

左反

左の表から右の行と一致しないものを抽出します

右の表にて削除された行が一目でわかるようになります

右反

右の表から左の表の行と一致しないものを抽出します

右の表で追加された行が一目でわかります

＜まとめ＞

Power Queryの結合にて一つ問題になるのは、表現がわかりにくいことです

「最初の行」とは左にある表、「2番目の行」とは右にある表と置き換えればわかりやすいと思います

にほんブログ村

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

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

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

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

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

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

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

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

今回のポイント

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

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

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

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

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

２つのクエリのマージ

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

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

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

条件列の追加

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

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

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

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

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

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

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

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

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

まず条件設定です

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

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

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

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

＜まとめ＞

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

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

今回紹介したのはキー列と金額の一番簡単なパターンでした

では、下の画像のような複数列がある場合に変更がある箇所を特定する場合はどうしたらいいか？

この場合は、１列を指定するパターンをまずは作ってクエリを複製してください

まずは電話番号を判定するクエリを作成します

その後に、クエリを複製します

そして、住所を判定するクエリに変更します　

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

にほんブログ村