【PowerQuery】二つの表のあるなしを比較する

パワークエリ1年生の苦悩シリーズ。

エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。（したと思っている）

二つの表を比較して、新しく出現したもの、片方にあって片方にないものなど、調べることってありますよね。関数だとCOUNTIFでやったりしますよね。パワークエリでやると、いったん組んでしまうと表を入れ替えるだけで毎月同様の比較ができたりするので便利だな、と思いました。

１．先月になくて今月にあるものを抽出する
２．片方にあって片方にない、を両方の表で出すとき
- （１）2回やる（左外部結合×２）
- （２）いっぺんに出力する（完全外部結合）
３．元の表を簡単に入れ替えたい：ファイルパスをパラメーターにする
４．最後に
５.参考文献、記事

１．先月になくて今月にあるものを抽出する

2023年フォルダの2月、3月エクセルを比較してみます。

3月に新たに出現した商品を出力します。

①集計用のエクセルファイルで、比較したい表を取り込みします。

今回は別々のエクセルファイルなので、「Excelブックから」で取り込みします。

リボン「データ」＞データの取得＞ファイルから＞Excelブックから

パワークエリエディタからの場合は

リボン「ホーム」＞新しいソース＞ファイルから＞Excelブック

②「3月」「2月」のエクセルファイルの「販売実績」シートを取り込みしました。
「販売実績」「販売実績（２）」となっていて、わかりにくいのでクエリ名を「source1」「source2」に変更しました。（source1のほうが3月ファイルです）

③新出のものをあぶりだしたい「3月」のsource1のクエリを選択します。
勝手に型の変換まで行われています。特に問題はないのでこのまま進みます。

リボン「ホーム」＞クエリのマージから「クエリのマージ」を選択します。
※「新規としてクエリのマージ」でもOK

④下段の結合する表は「source2」を選択します。
「商品ID」で二つの表を結合します。
source1、source2とも「商品ID」をクリックして選択状態にします。
結合の種類は「左外部（最初の行全て、および2番目の行のうち一致するもの）」を指定します。
「OK」ボタンをクリックします。

余談ですが。結合の種類で「左外部（最初の行すべて・・・）」をみて、「最初の行全て・・・」って何？意味わからんと思っていました。これって言葉を省略しすぎている？
「左外部（最初の表の行すべて、および2番目の表の行のうち一致するもの）」
と、「表」を入れてみると、納得できた。
「左外部」というのも視覚的には納得いかず「上外部」のほうが視覚的には納得できる
作成されたM言語ではたしかに元の表が左に来ているので「左外部結合」といってよいのでしょう。

左外部結合（LEFT OUTER JOIN）

⑤source2（2月）の表が横っちょにサブテーブルの形でくっつきました。

⑥source2の展開ボタンをクリックして「商品ID」を表示させます
（商品IDでなくても商品名でもなんでもいいです）

⑦nullの行をフィルタで抽出します。

※たまたまやった表に新出のものがない場合はnullが選択できません。
でも「nullをフィルタリングする」をやりたいんだ、という場合は適当な値を選択し、関数バーに表示されたフィルタの数式を手入力で修正します。
抽出される行無し＝新出の対象無し、ということになります。

２．片方にあって片方にない、を両方の表で出すとき

左外部結合を2回やる方法、完全外部結合で１回でやる方法と二通りの方法でやってみました。

（１）2回やる（左外部結合×２）

１と同様の処理を2回やります。ただし、マージは「新規としてマージ」を使用します。

①source1表にあり、source2にない対象を抽出

source1クエリを選択し、ホーム＞「新規としてクエリをマージ」をクリック

マージするクエリはsource2
結合は「商品ID」
結合の種類は「左外部」

１でやったようにマージされたテーブルを展開し、null行をフィルタで表示します。
販売個数とnullを絞り込みした列は削除し、シンプルに「商品ID」「商品名」の列だけを残し、「読み込み」します。

source1、source2のクエリは（必要がなければ）接続専用として、エクセル上には読み込みしないようにします。

紛らわしいので、クエリ名を「source1にしかない商品」としました。

②source2表にあり、source1にない対象を抽出

表を逆にして、①と同じ手順を繰り返します。

source2クエリを選択し、ホーム＞「新規としてクエリをマージ」をクリック

マージするクエリはsource1
結合は「商品ID」
結合の種類は「左外部」

マージされたテーブルを展開し、null行をフィルタで表示します。
販売個数とnullを絞り込みした列は削除し、シンプルに「商品ID」「商品名」の列だけを残し、「読み込み」します。

クエリ名は「source2にしかない商品」としました。

③別々のシートに「source1にしかない商品」「source2にしかない商品」が表示されました。

一つの表として出力したい場合は上の二つの新規マージで作成したクエリをドッキングすればできます。

④ドッキングして出力する方法

どっちがどっちかわからなくなるので、コメント列をそれぞれのクエリで追加しておきます。

列の追加＞カスタム列　でコメント列を追加します。””で囲ってベタ打ちで表示したいコメントを入力します。

どちらかのクエリを選択した状態で　ホーム＞クエリの追加　をクリックします。
（今回は「クエリを新規クエリとして追加」としました）

（２）いっぺんに出力する（完全外部結合）

二つの表のそれぞれ固有の情報を１つのクエリでまとめて表示したいと思います。
前項では2つのクエリを作成し、ドッキングしましたが、「完全外部結合」を使用することで、一つのクエリで実現できます。

①ファイルの読み込みを行い「source1」「source2」のクエリを用意します。

②source1のクエリを選択した状態で、ホーム＞クエリのマージ＞新規としてクエリをマージ　をクリックします。

③source1クエリとsource2クエリを「完全外部結合」でマージします。

結合キーは「商品ID」
結合の種類は「完全外部（両方の行すべて）」

・・・「表の」を付け加えるたほうがしっくりきますね
「完全外部（両方の表の行すべて）」

④source2がサブテーブルとしてくっつきました。
展開ボタンで「商品ID」「商品名」を表示させます。

2つの表が横方向にドッキングし、片方にない場合はないほうの値にnullが表示されています。

紛らわしいので列名を変更しました。

⑤列を追加して、片方の表にない商品を表示していきます。
列の追加＞条件列で下図のように指定します。

カスタム列でif式で書く場合は以下のように書けばいいですね。

追加する列名「商品ID」
if [商品ID_source1] = null then [商品ID_source2]
else if [商品ID_source2] = null then [商品ID_source1]
else null

追加する列名「商品名」
if [商品名_source1] = null then [商品名_source2]
else if [商品名_source2] = null then [商品名_source1]
else null

⑥もう一つ条件列でコメントを追加します。

商品ID_source1がnullの場合、「source2にありsource1にない」
商品ID_source2がnullの場合、「source1にありsource2にない」
それ以外の場合、nullを表示

⑦そうすると、追加した列にはどちらの表にもある商品にはnullが表示され、片方にしかない商品は商品ID、商品名、コメントが表示されます。

⑧「コメント」列でnullと不一致でフィルタします。
条件列で追加した3列以外は削除します。

⑨「閉じて読み込む」で完成です。

はい。できました。

３．元の表を簡単に入れ替えたい：ファイルパスをパラメーターにする

毎月、元の表を入れ替えたいんだけど、、、という場合、いちいちソースファイルを読み込みなおすのは面倒。エクセルシート上でファイルパスを指定できるようにしておくと、便利です。
パラメーター、というんですね。パラメーターは「外から投入する値」みたいな感じ、です。

パワークエリでのセル値の参照はオフィスTANAKA先生の記事で勉強させていただきました。
セルの値でクエリを変化させる(パラメータクエリの基本)

（１）セルに名前を付けて読取する方法

まず、読み込みしたファイルを見てみましょう。ソースの部分を見てみると、
= Excel.Workbook(File.Contents(“XXXXXXX\2023\3月.xlsx”), null, true)
のようになっています。
パスを指定している赤字の部分を入れ替えればよさそうですね。

直接ここに書いてもよいのですが、「空のクエリ」を使ってファイルパスを取得する方法でやってみます。

①まず、エクセルシートにインポートしたいファイルのファイルパスを入力し、名前ボックスでセルの名前を定義します。今回はセルC3を「ファイルパス1」、セルC4を「ファイルパス2」の名前にしました。

②パワークエリエディタを起動し、ホーム＞新しいソース＞空のクエリ

③作成したクエリを選択して「詳細エディター」を開きます。

④詳細エディターの中身を書き換えます。

名前を付けたセルの値を取得するには以下のように書きます。

= Excel.CurrentWorkbook(){[Name=“ファイルパス1”]}[Content]{0}[Column1]

今のエクセルブックの名前が「ファイルパス」という範囲の中身の1行目、1列目を持ってくるよ、ということが書いてあると理解しています。

ついでにイコールの左側も「ファイルパス取得」と変更しました。
inの後も「ファイルパス取得」と書き換えました。取得した内容をハイっと返すことになります。

⑤クエリの結果が表示されています。ちゃんとシートに入力したファイルパスが取れていますね。

⑥さて。これを普通にインポートしたクエリのほうに移植します。

これでOK！

ちなにみ、空のクエリを使わないで、以下のようにも書けます。

入れ替えしたいクエリの詳細エディターでソースのところを書き換えします。

変更前

let
    ソース = Excel.Workbook(File.Contents("C:xxxx\2023\3月.xlsx"), null, true),
    売上実績_Sheet = ソース{[Item="売上実績",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(売上実績_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名", type text}, {"販売個数", Int64.Type}})
in
    変更された型

書き換え後：ファイルパスを取得する行を追加して、File.Contentsのカッコ内を入れ替え

let
    ファイルパス = Excel.CurrentWorkbook(){[Name="ファイルパス1"]}[Content]{0}[Column1],
    ソース = Excel.Workbook(File.Contents(ファイルパス), null, true),
    売上実績_Sheet = ソース{[Item="売上実績",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(売上実績_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名", type text}, {"販売個数", Int64.Type}})
in
    変更された型

または、ソースの部分に直接名前を付けたセルの値を取得する式を入力

let
    ソース = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="ファイルパス1"]}[Content]{0}[Column1]), null, true),
    売上実績_Sheet = ソース{[Item="売上実績",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(売上実績_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名", type text}, {"販売個数", Int64.Type}})
in
    変更された型

（２）テーブルから読み込みする方法

①エクセルシートにファイルパスを入力し、入力した範囲をテーブルに変換します（Ctrl＋LまたはCtrl＋Tでテーブルにできます）
「先頭行をテーブルの見出しとして使用する」にはチェックをいれます。

②リボン　データ＞テーブルまたは範囲から　をクリックしてパワークエリに読み込みします。

③パワークエリディタが開きます。テーブルの内容が読み込まれています。
読み込みしたテーブルのクエリの名前を「ファイルパス指定TB」に変更しました

④さて、ここから「ドリルダウン」でセルの内容を取得するのが常套手段ですが、２つのセルの内容を使用したいので、ドリルダウンは使用しない方法でやっていきます。
（ドリルダウンだと１つしか値を取得できないので）

テーブルの内容を取得するには。。。空のクエリで試してみます。

空のクエリを起動して
= ファイルパス指定TB{0}[ファイルパス]
と入力しました。
すると、入力してあるセルの値が取得できました。

テーブルの内容を取得するには、
= テーブル名{行番号}[列名]
と指定すればいいようですね。行番号は1行目が 0 ですね。

⑤さて、テーブルの値の取得方法がわかったので、source1のクエリに書いていきます。

sourse1クエリの「詳細エディター」を開きます。

ソースの前に1行書き足して、テーブルからファイルパスを取得し、ソースの行の File.Contents()のカッコ内を書き換えます。

変更前の詳細エディター

let
    パス = ファイルパス指定TB{0}[ファイルパス],
    ソース = Excel.Workbook(File.Contents(パス), null, true),
    売上実績_Sheet = ソース{[Item="売上実績",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(売上実績_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名", type text}, {"販売個数", Int64.Type}})
in
    変更された型

変更後

let
    パス = ファイルパス指定TB{0}[ファイルパス],
    ソース = Excel.Workbook(File.Contents(パス), null, true),
    売上実績_Sheet = ソース{[Item="売上実績",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(売上実績_Sheet, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"商品ID", type text}, {"商品名", type text}, {"販売個数", Int64.Type}})
in
    変更された型

これで完了です。

テーブルの2段目に入力されたファイルパスを取得するには
パス = ファイルパス指定TB{1}[ファイルパス]
と、行数の部分を変更するだけです。