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番目の行のみ ってイケてないですよね。
→これのことです

 

2.右側(参照側)のテーブル情報だけでは足りないとき

1とは異なり「開始日」のみで「終了日」が無いため、条件式だけでは絞り込みができません。
この場合、条件式の絞り込みに加えて、以下①②で対応することになります。
①並び替えを利用した上で、
②「行の削除/重複の削除」

左側:商品の売上履歴
右側:商品毎の単価テーブル(期間によって単価が変わる)
開始日のみしかない。(終了日がない)

↓この回の冒頭に表示している事例です。(クリックで拡大)

作業手順:(①~⑦の見出しは、この後のスクリーンショットの「クエリの設定」「適用したステップ」の名称に対応しています。)
①ソース:結合時は、PowerQueryエディタが起動時に自動的に出来ている。
②展開された商品TBL:右テーブル展開ボタンを押すと作成される。
③フィルターされた行:絞り込み条件式を入力する
= Table.SelectRows(#”展開された 商品TBL”, each [販売日] >= [開始日] )
④並び替えられた行1
④_1.並び替え 販売日と開始日(これも、一旦サンプルを作った後に式を修正)
④_2.絞り込み式を書いた上で、その式を「Tabale.Buffer()」で囲む
= Table.Buffer( Table.Sort(フィルターされた行,{{“販売日”, Order.Descending}, {“開始日”, Order.Descending}}))
⑤削除された重複
⑤_1.Uniqueにする列を選び(販売日と商品コード)
⑤_2.ホーム/行の削除/重複の削除
⑥指定されたカスタム:売上列を追加
「1.右側(参照先)のテーブル情報だけで絞り込みができるとき」の売上列の追加と同じ手順
⑦並び替えられた列:この後Excelで扱い易いように列を並び替え
↓この場合のPowerQueryエディタ上のクエリのステップを全て表示しています。
(クリックで拡大)

 

慣れるまで、少し大変ですが、一度作れば、作業元データにデータを追加して「更新」していけるのが良いところ。
今後式の事例や、キーが無い時の重複行の削除についても書いていきます。