[Power BI] Power Queryでデータをマージする様々な方法

※この記事は、2023年3月18日の[「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #24」](https://powerbi.connpass.com/event/276814/)で使用しました。


:::note
Power QueryはPower BIだけでなく、Excelでも使用でき、様々なデータの結合を行うことができますので、Excel利用者にも覚えてほしい技だと思います。
:::

# マージの基本 [^1] 
　２つの表を、キーを使ってマージしたいという場合、Power Queryエディターの　**ホームタブ -> 結合グループ -> クエリのマージ**　を選択します。
　クエリのマージには、現在選択してるクエリに続けてマージ処理を行う **「クエリのマージ」** と、新たにクエリを作成する **「新規としてクエリをマージ」** の２つが選択できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/56fda7d6-f9ff-50bc-b22e-13a6d0c49e9c.png)

　マージのダイアログが表示され、結合のキーとなる列の選択を行い、結合の種類を選択します。Power Queryのマージでは６種類が選択できます
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/726a42a6-97e6-ca15-6a9b-d504090d131a.png"/>

　そして、マージを実行すると、マージされた内容が「Table」として新しい列に追加されます。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c9932f7d-c269-4d4b-7bc9-c7217ffe89ba.png"/>
　これを展開すると、以下のような結果を得ることができます。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ff2d4eba-cc9d-d8b7-d072-5a6921d51a9c.png"/>
　この操作で使われる関数は `Table.NestedJoin`[^1.2] です。

```text:Table.NestedJoin構文
Table.NestedJoin(
    table1 as table, 
    key1 as any, 
    table2 as any, 
    key2 as any, 
    newColumnName as text, 
    optional joinKind as nullable number, 
    optional keyEqualityComparers as nullable list
) as table
```
 `JoinKind`には、以下のような種類があります。
- JoinKind.Inner
- JoinKind.LeftOuter
- JoinKind.RightOuter
- JoinKind.FullOuter
- JoinKind.LeftAnti
- JoinKind.RightAnti

　`keyEqualityComparers`については、内部で使用するためのものであるということで、詳細な説明はありません。

　`Table.NestedJoin` 以外に、 `Table.Join`[^1.3] という関数もあります。これは、UIからの操作では使えませんが、結合の結果をテーブルではなく、展開された形で取得します。`Table.NestedJoin`は、内部で `Table.Join` を呼び出しています。

```text:Table.Join構文
Table.Join(
    table1 as table, 
    key1 as any, 
    table2 as table, 
    key2 as any, 
    optional joinKind as nullable number, 
    optional joinAlgorithm as nullable number, 
    optional keyEqualityComparers as nullable list
) as table
```

`joinAlgorithm`は、結合時の処理の種類が選択できます。[^1.1]

# 多対多の結合
　エクセルのVLOOKUPでは、１対１あるいは多対１の結合のみしか行うことができません。複数の一致するデータがあった場合、最初に一致したものしか表示できないのです。（いわゆるSemi Join）
　例えば、以下のように柿の種に複数のメーカーが存在していても、表示できるのは最初の１社のみです。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2414145e-b8d4-9030-c0cd-62a9cec4081e.png"/>
　Power Queryでは、マージ部分が表で返されるため、以下のように一致したすべてのデータが入っています。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/96f3c409-bd95-3552-5cc7-a535bf4bcbd0.png"/>
　したがって、商品マスターの列を展開すると、以下のように、柿の種が複数のデータに分かれて結合されてしまいます。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a4f76911-f330-b578-2830-e29a8c735a54.png"/>
　データをマージする際は、参照される表のキーがユニークかどうか、注意して結合する必要があります。

# 結合の種類
　結合には複数の種類があるということを純粋なExcelユーザーには知らされていませんでした。表の結合を行うには、VLOOKUP（あるいはINDEXとMATCH）で左外部結合を行うしかなかったからです。Power Queryでは、様々な結合タイプが選択でき、一致するデータだけでなく、**一致しないデータも見つけることができます**。これは、データの一致や照合を試みる場合、非常に重要なことです。

<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/92eb55b7-51a1-bc46-2437-8a18e42bf177.png"/>

　今回の例では、左の表として「購入履歴」、右の表として「商品マスター」を使います。購入履歴には「ホワイトロリータ」がありますが、商品マスターに該当の商品がありません。逆に、「ルマンド」は商品マスターにありますが、購入履歴がないというデータになっています。結合する項目は、購入履歴の日付、商品名、商品マスターの種類、製造の項目で結合します。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/754930af-6671-814f-6f8e-ee9c21d6c01a.png"/>

## 左外部(Left Outer Join) [^2]
 Power Queryの結合の種類では、`「左外部（最初の行すべて、および２番目の行のうち一致するもの）」`と書かれており、左右とか上下とか混乱してしまいます。書き直すと、以下のようになります。

|マージのダイアログで上で選択した表の行と、下で選択した表の行を「左外部」結合します。上の表のすべての行と、下の表は一致した行のみ表示します。|
|--|

　今回、購入履歴（左の表）にある商品名「ホワイトロリータ」が商品マスター（右の表）ないというケースを見てみます。以下のように、商品マスターから持ってきている項目の商品の種類と製造の項目がnullになり、商品マスターにレコードが不足していることが分かります。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e03b0b75-8c6e-c2cf-260a-8cc39f2f051d.png"/>
　また、マージ後に購入履歴のレコードが増えていたら、商品マスターのキーに重複があることが分かります。

## 右外部(Right Outer Join) [^3]
　反対に、「右外部」の場合は右の表（商品マスター）にはあるが、左の表（購入履歴）がないものについてです。商品名「ルマンド」は購入履歴にありませんが、商品マスターに載っています。購入履歴から持ってきている項目の購入日、製品名がnullになっています。一回も購入されなかった商品です。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/047ba1b2-ee09-76b5-54dd-afc154960f71.png"/>

## 完全外部(Full Outer Join) [^4]
　2つの表のすべての項目が結合されます。一致しないものについては、nullが入ります。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c85daac6-9e81-2ac3-ea0e-b028f263a815.png"/>

## 内部(Inner Join) [^5]
　内部結合は、2つの表で一致しているもののみ結合されます。一方の表のみにあるものは入りません。先の「ルマンド」も「ホワイトロリータ」も除外されます。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/643e1e7d-c49f-7b87-032f-8d61bdd34563.png"/>

## 左反(Left Anti Join) [^6]
　購入履歴にあって、商品マスターにない「ルマンド」が入ります。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/02cf8e99-708f-9770-f468-b1e9da7416a4.png"/>

## 右反(Right Anti Join) [^7]
　商品マスターにあって、購入履歴にない「ホワイトロリータ」が入ります。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ac8c2c5c-97d7-6f95-27a1-26e8e0dcaaab.png"/>

# その他の結合方法
　以下は、「クエリのマージ」で行うことはできない結合方法です。

## 完全アンチ結合 (Full Anti Join)
　一致しないすべてのレコードです。
<img width="300" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/20c69934-d150-889d-05c2-578c4559f5f2.png"/>
　これを行うには、「左反」結合と「右反」結合の２つを「クエリの追加」で1つの表にします。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0a4383cb-8a32-0a3c-a2b3-7f5f6d0b83d4.png"/>
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/81c359ed-0b65-62f9-bd19-87a724287294.png"/>

## 交差結合(Cross Join) [^8]
　交差結合、クロスジョイン、多対多ジョイン、またはカルテジアン結合、デカルト積など様々な呼び方がああるようですが、2つの集合のすべての組み合わせを作成します。
　片方の表に2個、もう片方の表に3個の値がある場合、結合の結果、 `2 × 3 = 6` で、6個の値が返されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c944335d-8cd8-350d-2c65-eb7b17e76b41.png)
　これを実現するのは、簡単です。
　まず、1つのテーブルを参照するクエリ「交差結合」を作成します。
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9024404e-5a41-0b5a-947c-51d1f302b70f.png"/>
　「列の追加」タブから、「全般」グループの「カスタム列」を選択し、式の部分にもう１つのテーブル名を書きます。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/aee1a5d8-7d0d-7323-84ae-fd920bf084f1.png"/>
　追加された列を展開すれば、交差結合した表が完成します。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/4cdcf94b-d8ea-a5a4-b818-e398e16f5e44.png"/>

## 総当たり表
　以下のようなテーブルの項目を使って、全ての組み合わせの表を作りたい場合です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/22647973-3dca-31ce-f09c-e5deef927980.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/50e0cb49-41c5-35ce-6b98-841a9d102b9f.png)
　まず、交差結合をつくります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ef1c3d15-5707-d274-7418-37467a163687.png)
　次に、フィルターの機能をちょっといじって、左の列と右の列の名前が同じものを削除します。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3397cc32-5eca-b935-c2fe-32160f390caf.gif"/>
　この部分のコードは以下のようになります。

```
#"Filtered Rows" = 
    Table.SelectRows(
        #"Expanded {0}", 
        each ([名前] <> [名前.1])
    )
```


　ただし、これでは「パンダ」対「ペンギン」の左右入れ替えた「ペンギン」対「パンダ」の対戦カードが含まれてしまいます。「パンダ」対「ペンギン」の組み合わせが１回のみになるように作成してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/8862f12b-8d08-b3be-edc7-712b85bcd87f.png)
　まず、「動物」テーブルにインデックスを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/702d4e21-b41f-bcdd-30cd-553857806b00.png)
　次に、列の追加を行いますが、現在の行のインデックス番号より大きいインデックス番号のレコードがテーブルに含まれるよう関数を書きます。操作は、以下のように行います。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/08c39225-5264-2b24-ef9b-442b3316b6d7.gif"/>
　最後に、対戦カードが出尽くしてnullになったキリンの対戦を削除して出来上がりです。
　項目を追加した部分のコードは、以下のようになります。（自動で作成されたコードを整形してます）

```
#"Added Custom" = 
    Table.AddColumn(
        Source, 
        "カスタム", 
        (x) =>  Table.SelectRows(
                    動物2,
                    each _[インデックス] > x[インデックス]
        )
    )
```
　全10試合の対戦表が出来上がりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/59b78bd7-7ea7-5961-1649-573fc7e09ef7.png)


 この話を[「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #24」](https://powerbi.connpass.com/event/276814/)で行ったところ、Kagata先生( 
@PowerBIxyz )から、「インデックス作らなくてもいけるよ」とアドバイスをもらったので、以下のように作り変えました。

```
#"Added Custom" = 
    Table.AddColumn(
        Source, 
        "カスタム", 
        (x) =>  Table.SelectRows(
                    動物2,
                    each _[名前] > x[名前]
        )
    )
```
　名前を比較し、大小を判定しています。元の名前が昇順ではないのできれいな並びになっていませんが、きちんと出来上がりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/64ccf416-07a4-015d-2ebe-e41c0638425f.png)

# 近似一致
　ExcelのVLOOKUPにある、近似一致を行いたい場合です。
　以下のように注文数に応じて単価が変わる場合、VLOOKUPを使って簡単に表を作成することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/df73fb29-7530-8de4-e31d-da290ced929f.png)

## クエリの追加を使う
　2つのテーブルを１つに合わせ、フィル・ダウンで単価を求めます。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5fe937bf-6cfb-4879-478e-90cd85c97e33.gif"/>
　全データの並び替えを行うため、注文履歴のデータ数が大量になった場合、速度が低下します。

## 関数を使う
　先ほどの総当たりでは、`(x) => ~~~~` のように、クエリの内部に関数を書きましたが、今回は注文数を与えると単価が返ってくる関数をクエリから作成してみます。
　まず、注文数をパラメータに設定します。値はサンプルなので何でもいいです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b02ee1ba-1f37-8af0-51b9-62ae11dfda13.png)
　パラメータを使って単価を返すクエリをサクッと書きます。

```text:単価計算
let
    SelectedTable = 
        Table.SelectRows(
            単価テーブル,
            each _[注文数] <= Units
        ),
    Result = Table.Last(SelectedTable)[単価]
in
    Result
```
　このクエリを関数に変換して「Fx単価計算」を作ります。
<img width="200" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/920e57f0-53da-7142-db5b-f7e8a2817402.png"/>
<img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2691ea82-6176-3b0c-e3c7-0d5a769821a1.png"/>
　「列の追加」で、「カスタム関数の呼び出し」に「Fx単価計算」を関数クエリとして使用し、パラメータとして「注文数」を設定します。
<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/8af3e17c-f13c-8051-ce3a-c358e8edfe32.png"/>
　これで、以下のような表が出来上がります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/35e547c2-f5d9-f2dd-419b-fd213c1af746.png)

# VLOOKUP風のSemi Join
　VLOOKUPの完全一致では、一致した最初の値のみ返されます。これと同じ動作をPower Queryで作成します。
　これも、関数を使った方が簡単なので、まずパラメータを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/31da3b76-42e6-9a5b-f217-ad1e34a7bfa1.png)
　このパラメータを使って、商品マスターから該当するレコードを最初の1つだけ探し出すクエリを作ります。

```text:VLOOKUP
let
    SelectedRow = Table.SelectRows(
        商品マスター,
        each [製品名] = Value
    )
in
    Table.First(SelectedRow)
```
　このクエリを使って関数を作ります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5258763f-90ac-3452-2d4d-5fb8c1ae31c4.png)

　購入履歴の表に列の追加で「カスタム関数の呼び出し」を行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/57a953d4-d7fe-6ff1-5d7e-75cea5d65e6f.png)
　追加された列は、以下のようなレコードになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/123e8c0f-e666-a2f0-f46d-a6c00b378ecf.png)
　これを展開すれば、以下のようになります。柿の種は亀田製菓のみになります。
<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d1da211f-0d29-e29a-8e43-84d5b9c55413.png"/>

# まとめ

<img width="800" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/31952e19-4988-fabf-e06a-789987164497.png"/>

 注意しなければならないのはデータモデルの設計をすることで、それよってマージをどのように使うか決まります。

[^1]: クエリのマージの概要 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-overview)
[^1.2]: Table.NestedJoin (https://learn.microsoft.com/ja-jp/powerquery-m/table-nestedjoin)
[^1.3]: Table.Join (https://learn.microsoft.com/ja-jp/powerquery-m/table-join)
[^1.1]: JoinAlgorithm.Type (https://learn.microsoft.com/ja-jp/powerquery-m/joinalgorithm-type)
[^2]: 左外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-left-outer)
[^3]: 右外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-right-outer)
[^4]: 完全外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-full-outer)
[^5]: 内部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-inner)
[^6]: Left Anti Join (https://learn.microsoft.com/ja-jp/power-query/merge-queries-left-anti)
[^7]: Right Anti Join (https://learn.microsoft.com/ja-jp/power-query/merge-queries-right-anti)
[^8]: クロス結合 (https://learn.microsoft.com/ja-jp/power-query/cross-join)