#PowerApps におけるデータ結合 (JOIN)

あまり非開発の方には馴染みのない、データ結合についてです。
結合についての一般的な説明はいろいろありますが、[こちら](https://sql55.com/t-sql/t-sql-join-1.php)の記事がわかりやすいかと思います。

今回はPower Appsの枠組みで、5つのJOIN (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS)をどのように実現するかを説明します。

関連ポスト；https://qiita.com/h-nagao/items/f4a65e7c84357599b8b8

#準備
簡単のために、以下のような2つのCollectionを考えます。

**Customer**

<table class="w3-table-all notranslate">
  <tbody><tr>
    <th style="width:15%">CustID</th>
    <th>CustomerName</th>
    <th>Country</th>
  </tr>
  <tr>
    <td>1</td>
    <td>Alfreds Futterkiste</td>
    <td>Germany</td>
  </tr>
  <tr>
    <td>2</td>
    <td>Ana Trujillo</td>
    <td>Mexico</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Antonio Moreno Taquería</td>
    <td>Mexico</td>
  </tr>
</tbody></table>

**Order**

<table class="w3-table-all notranslate">
  <tbody><tr>
    <th style="width:15%">OrderID</th>
    <th>CustID</th>
    <th>OrderDate</th>
    <th>ShipperID</th>
  </tr>
  <tr>
    <td>10308</td>
    <td>2</td>
    <td>1996-09-18</td>
    <td>3</td>
  </tr>
  <tr>
    <td>10309</td>
    <td>37</td>
    <td>1996-09-19</td>
    <td>1</td>
  </tr>
  <tr>
    <td>10310</td>
    <td>77</td>
    <td>1996-09-20</td>
    <td>2</td>
  </tr>
</tbody></table>

結合の条件はCustIDが一致しているかどうかとします。
この場合、見てわかるとおり、CustID=2のレコードが共通部分になります。

2つのCollectionの生成は以下の通りです。何らかのボタンで生成してください。

```
ClearCollect(Customer,
{CustID:1,CutstomerName:"Alfreds Futterkiste",Country:"Germany"},
{CustID:2,CutstomerName:"Ana Trujillo",Country:"Mexico"},
{CustID:3,CutstomerName:"Antonio Moreno Taquería",Country:"Mexico"});
ClearCollect(Order,
{OrderID:10308,CustID:2,ShipDate:Date(1996,9,18),ShipperID:3},
{OrderID:10309,CustID:37,ShipDate:Date(1996,9,19),ShipperID:1},
{OrderID:10310,CustID:77,ShipDate:Date(1996,9,20),ShipperID:2})
```

どの結合でも以下のようなテーブルが得られます。

<table style="width:100%">
  <tbody><tr>
    <th>CustID</th>
    <th>CustomerName</th>
    <th>Country</th>
    <th>OrderID</th>
    <th>OrderDate</th>
    <th>ShipperID</th>
  </tr>
</tbody></table>

では早速、データを結合してみましょう。

#JOIN two collections

## LEFT OUTER
実は一番簡単なのはLEFT OUTER JOINです。
LEFTをCustomerテーブルとすると、

```LeftOuter
DropColumns(
    Ungroup(
        AddColumns(
            Customer,
            "order",
            Filter(
                RenameColumns(
                    Order,
                    "CustID",
                    "OCustID"
                ),
                CustID = OCustID
            )
        ),
        "order"
    ),
    "OCustID"
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/202779/38ff3adc-5634-b11f-b9ce-71ab66b5afa3.png)

LEFTテーブルのすべてのデータと、RIGHTの一致部分が表示されました。

## RIGHT OUTER
RIGHT OUTERは左と右の役割を入れ替えるだけです。

```RightOuter
DropColumns(
    Ungroup(
        AddColumns(
            RenameColumns(
                Order,
                "CustID",
                "OCustID"
            ),
            "order",
            Filter(
                Customer,
                CustID = OCustID
            )
        ),
        "order"
    ),
    "OCustID"
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/202779/c80db611-4676-683e-4c04-6ee8baac6aad.png)
本当に、テーブルの位置が変わっているだけです。

## INNER JOIN
FULL OUTERの前にINNER JOINから。
INNER JOINはLEFT/RIGHTの共通部分を抽出する操作なので、LEFT/RIGHT OUTERのあとにFilterしてあげればよいです。

```InnerJoin
DropColumns(
    Filter(
        Ungroup(
            AddColumns(
                Customer,
                "order",
                Filter(
                    RenameColumns(
                        Order,
                        "CustID",
                        "OCustID"
                    ),
                    CustID = OCustID
                )
            ),
            "order"
        ),
        !IsBlank(OCustID)
    ),
    "OCustID"
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/202779/5282d2e6-2779-8eb1-5eca-f15205014e9f.png)
ポイントは、```Filter(....,!IsBlank(OCustID))```です。結合時に参照につかったカラム（RIGHT由来）が、Blankでないレコードを取得することで、一致部分のみ取得しています。

## FULL OUTER
FULL OUTERは、ステップとしては、LEFT OUTER+(RIGHT OUTER - INNER)の形式で結果を得ることにします。
ほかにもうまい方法はあるかもしれません。

```FullOuter
DropColumns(
    Ungroup(
        Table(
            {
                Tab: Ungroup(
                    AddColumns(
                        Customer,
                        "order",
                        Filter(
                            RenameColumns(
                                Order,
                                "CustID",
                                "OCustID"
                            ),
                            CustID = OCustID
                        )
                    ),
                    "order"
                )
            },
            {
                Tab: Filter(
                    Ungroup(
                        AddColumns(
                            RenameColumns(
                                Order,
                                "CustID",
                                "OCustID"
                            ),
                            "order",
                            Filter(
                                Customer,
                                CustID = OCustID
                            )
                        ),
                        "order"
                    ),
                    IsBlank(CustID)
                )
            }
        ),
        "Tab"
    ),
    "OCustID"
)
```
すごく長いですが、方法としては

1. テーブル型の列からなるレコードを2つ用意する
2. それぞれには、LEFT OUTER と (RIGHT OUTER -INNER)の結果を入れる
3. Ungroupでテーブルを解く

という感じです。2,3部分は私の前の投稿を参照してください。[こちら](https://qiita.com/h-nagao/items/f4a65e7c84357599b8b8)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/202779/b35cf9f6-dbdf-8a9f-7305-b4a4ea6eb74e.png)

## CROSS JOIN
CROSS JOINだけは少し違って、すべての組み合わせを網羅します。つまり、参照する列はありません。
この場合には、非常に簡単に、LEFTテーブルにRIGHTテーブルを列として追加し、その列をUngroupで展開するという方法をとります。

```Cross
Ungroup(
    AddColumns(
        Customer,
        "OrderTab",
        RenameColumns(
            Order,
            "CustID",
            "OCustID"
        )
    ),
    "OrderTab"
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/202779/90a4b691-d73d-4c4d-8051-c7ecbc0eda7b.png)

# おわりに
Power Appsで開発する人のメインはこういったテーブル結合等にあまり慣れていないかもしれませんが、知っておくといろいろ便利ですので、ぜひ手元でも試してみてください。

発展的には、2つのSharePointリストを使ってみるというのがよいトレーニングになるかもしれません。