PowerQuery【09】キー重複の場合に適切な行のデータを取り出す

（今までの記事）
■全体像説明
PowerQuery【01】PowerQuery（パワークエリー）で出来ること
 PowerQuery【02】PowerQuery（パワークエリー）の動かし方

■PowerQueryの実際のインポート方法
PowerQuery【03】CSVファイルのインポート
 PowerQuery【04】Excelファイルのインポート
 PowerQuery【05】フォルダから読み込み（Excel・CSV等の複数ファイルを同時にインポートする）
PowerQuery【06】2つのテーブルを結合する
 PowerQuery【07】ピボットテーブルの「値」覧に文字を表示する
 PowerQuery【08】フィル（Fill）機能（空白を埋める）

どんなことがしたいか？

表題だと、わかりにくいのですが、↓こういうことがしたいのです。（クリックすると拡大します）

ExcelのVLOOKUP関数だと、キー合致が複数ある場合、一番上の行のデータしか参照してくれませんが、
PowerQueryは重複分全て拾ってくれます。
重複した分から必要データを上手く抽出すれば良い訳です。

上手く抽出のところは、ちょっと難しいのですが、一度作れば、後々のデータも更新できる、つまり「プロセスの自動化」が出来るのがPowerQueryの良いところです。

では、やり方です。

「結合の種類」は「内部（一致する行のみ）を選択する

まずは、マージデータを作ります。
マージ方法は、PowerQuery【06】2つのテーブルを結合するを参照してください。

クエリ設定した「結合元データ」を選択して、クエリタブ／結合
マージ画面で、結合元・結合先・キー列を指定する。

「結合の種類」は「内部（一致する行のみ）を選択します。

テーブル展開ボタンを押すと行が増える

PowerQueryエディタ上に読み込まれた後、テーブル展開ボタンを押すと、右側のデータが全て表示されるので、行が増えます。
左（結合元）データ行数×右（結合先）データ行数なので、1対1で無いときは、倍数で増えています。
（例：1×2なら2行、2×2なら4行。2×3なら6行）
以下の例では、左2件×2件なので、4行になっています。

このデータを絞り込んでいきます。

絞り込み作業の手順

知っておきたいこと

M言語で式を書く

PowerQueryは、M言語」を使っており、Excel・Access・VBAとは異なるため、慣れるまでは少し時間がかかります。
あと、分からないことがあれば、レッツ検索です。

ちなみに、なお、VBAとは異なり、大文字小文字の違いがあるとエラーになります。
null ○　Null　×

また、式は、一つ前の「適用したステップ」の名称も含むので、単純にどこか他から式のコピーペーストではエラーになってしまいます。
例：「= Table.SelectRows(#”展開された商品TBL”, each [販売日] >= [開始日] )」
この場合は”展開された商品TBL”が一つ前の「適用したステップ」名称です。

2つのパターン

1．右側（参照先）のテーブル情報だけで絞り込みができるとき

1行の条件式で対応可能です。

こんな例です。
・左側：商品の売上履歴
・右側：商品毎の単価テーブル（期間によって単価が変わる）
・開始日と終了日がある

例：

条件式の作り方

①まずは式のドラフトを作る

販売日>=開始日　and 販売日<=終了日
または　販売日>=開始日　and 終了日　がブランク

②条件式の形に直す

販売日>=開始日　and （販売日<=終了日　or 終了日=null）

③PowerQuery向きに式を展開する

each [販売日] >= [開始日] and ([販売日]　>=[終了日] or [終了日] is null)

※nullは　「=null」では無く、「is null」。Accessに近いが、AccessはNullで大文字始まりなので注意。

④実際の数式バーに展開する式

= Table.SelectRows(#”展開された商品テーブル終了日アリ”, each [販売日] >= [開始日] and ([販売日]　<=　[終了日] or [終了日] is null))
↑一つ前の「適用したステップ」

⑤式の作り方

式を作る時は「詳細エディタ」で加工しても良いのですが、わかりにくいのでとりあえず、一つ式を作ります（=「適用したステップ」を一つ増やす）
今回は販売日がターゲットなので、「販売日」のフィルタボタンで、適当な日付2つを選択します。
そうすると「数式バー」にそれらしき式が展開されるので、この式を修正していきます。
（eachの後に条件式が入ることや、列名は[]で囲うことがわかるので、その部分を直すことになります）
(↓クリックすると拡大します）

each以降を直して数式バーの「v」をクリックします。エラーになったら修正します。

正しい結果が出ていることを確認します。

この結果を利用して計算列を追加

ここまで出来たところで、売上計算を実施します。
「閉じて読み込み」してExcel上でやってもいいですが、そんなに難しくないので、式を追加していきましょう。

売上列まで出来たので、ホーム/閉じて読み込むでPowerQueryエディタを閉じます。
この後、クエリ名とシート名の修正をお忘れ無く。

注意事項（行数を必ず確認する）

左の参照元の行数が減っていないか必ず確認してください。
減っている場合は、右の参照先の開始日と終了日に誤りがある可能性があります。

その場合の調査の際には、再度PowerQueryでの照合が有効です。
・【左の参照元】：今回作った「マージデータ」
・【右の参照先】：「売上データ」
・【キー】：を「販売日」と「商品コード」
・【結合の種類】：一番下の「右半（2番目の行のみ）」を選択
※この翻訳：2番目の行のみ　ってイケてないですよね。
→これのことです