PowerQueryでXLOOKUP的なことをやりたいときの注意点

Spreadsheet

設例

PowerQueryで「くだものテーブル」に「金額テーブル」をくっつけて、「くだもの金額テーブル」を作りたいと思ったときの注意点をご紹介します。

なお、「くだものテーブル」と「金額テーブル」はID列で紐付けることができる前提です。

使用するテーブルたち
くだものテーブル
ID種類
1いちご
2りんご
3みかん
* 重複データのないテーブルです
金額テーブル
ID金額
1100
2150
2150
* ID2のデータがダブっています
くだもの金額テーブル
ID種類金額
1いちご100
2りんご150
3みかんnull
* IDの重複が排除されています

ちなみに、「くだもの金額テーブル」をXLOOKUP関数で作りたい場合は、まずID列を作った後、D15セルのようにID列で検索し、種類や金額を引っ張ってくるだけでOKです。

XLOOKUPの例

PowerQueryで作ってみよう

では、これと同じことをPowerQueryでやってみましょう!ポイントは結合する方の金額テーブルにデータの重複があることです。

ステップ1

まず、PowerQueryエディターを起動し、くだものテーブルからクエリのマージを実行します。

PowerQueryの例

ステップ2

次に、くだものテーブルのIDと金額テーブルのIDで紐づけるため、それぞれを選択し、結合の種類は「左外部(最初の行すべて~)」とします。

PowerQueryの例

ステップ3

次に、結合した金額テーブルを開き、金額を選択します。今回は「元の列名をプレフィックスとして使用する」のチェックは外しておきます。

PowerQueryの例

これで結合が完了しました。しかし!赤枠部分をよく見てください。なんとID2が2つに増えています。いったい何が起こったのでしょうか?

PowerQueryの例


XLOOKUPでは、結合する方のテーブル(金額テーブル)に重複するレコードがあったとしても、最初に一致したデータのみを返すため、このようなことは起こりません。

しかし、PowerQuery では、結合する方のテーブル(金額テーブル)に重複するレコードがあった場合、全てのデータを紐付けようとするため、上記のように行が増えてしまうのです。

解決策

これを避けるためには、あらかじめ結合する方のテーブル(金額テーブル)から重複を排除し、以下のようにする必要があります。

ID金額
1100
2150
金額テーブル

これくらいであれば、エクセル上で元データを編集すれば良いですが、もっとデータ量が多いテーブルで重複削除もPowerQueryに任せたいとなったときには、重複を判定したい列を選択してから、ホーム > 行の削除 > 重複の削除を実行すればOKです。

PowerQueryの例

コメント