あまり非開発の方には馴染みのない、データ結合についてです。
結合についての一般的な説明はいろいろありますが、こちらの記事がわかりやすいかと思います。
今回はPower Appsの枠組みで、5つのJOIN (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS)をどのように実現するかを説明します。
関連ポスト；https://qiita.com/h-nagao/items/f4a65e7c84357599b8b8
#準備
簡単のために、以下のような2つのCollectionを考えます。
Customer
| CustID | CustomerName | Country |
|---|---|---|
| 1 | Alfreds Futterkiste | Germany |
| 2 | Ana Trujillo | Mexico |
| 3 | Antonio Moreno Taquería | Mexico |
Order
| OrderID | CustID | OrderDate | ShipperID |
|---|---|---|---|
| 10308 | 2 | 1996-09-18 | 3 |
| 10309 | 37 | 1996-09-19 | 1 |
| 10310 | 77 | 1996-09-20 | 2 |
結合の条件は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})
どの結合でも以下のようなテーブルが得られます。
| CustID | CustomerName | Country | OrderID | OrderDate | ShipperID |
|---|
では早速、データを結合してみましょう。
#JOIN two collections
LEFT OUTER
実は一番簡単なのはLEFT OUTER JOINです。
LEFTをCustomerテーブルとすると、
DropColumns(
Ungroup(
AddColumns(
Customer,
"order",
Filter(
RenameColumns(
Order,
"CustID",
"OCustID"
),
CustID = OCustID
)
),
"order"
),
"OCustID"
)
LEFTテーブルのすべてのデータと、RIGHTの一致部分が表示されました。
RIGHT OUTER
RIGHT OUTERは左と右の役割を入れ替えるだけです。
DropColumns(
Ungroup(
AddColumns(
RenameColumns(
Order,
"CustID",
"OCustID"
),
"order",
Filter(
Customer,
CustID = OCustID
)
),
"order"
),
"OCustID"
)
本当に、テーブルの位置が変わっているだけです。
INNER JOIN
FULL OUTERの前にINNER JOINから。
INNER JOINはLEFT/RIGHTの共通部分を抽出する操作なので、LEFT/RIGHT OUTERのあとにFilterしてあげればよいです。
DropColumns(
Filter(
Ungroup(
AddColumns(
Customer,
"order",
Filter(
RenameColumns(
Order,
"CustID",
"OCustID"
),
CustID = OCustID
)
),
"order"
),
!IsBlank(OCustID)
),
"OCustID"
)
ポイントは、Filter(....,!IsBlank(OCustID))です。結合時に参照につかったカラム（RIGHT由来）が、Blankでないレコードを取得することで、一致部分のみ取得しています。
FULL OUTER
FULL OUTERは、ステップとしては、LEFT OUTER+(RIGHT OUTER - INNER)の形式で結果を得ることにします。
ほかにもうまい方法はあるかもしれません。
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"
)
すごく長いですが、方法としては
- テーブル型の列からなるレコードを2つ用意する
- それぞれには、LEFT OUTER と (RIGHT OUTER -INNER)の結果を入れる
- Ungroupでテーブルを解く
という感じです。2,3部分は私の前の投稿を参照してください。こちら
CROSS JOIN
CROSS JOINだけは少し違って、すべての組み合わせを網羅します。つまり、参照する列はありません。
この場合には、非常に簡単に、LEFTテーブルにRIGHTテーブルを列として追加し、その列をUngroupで展開するという方法をとります。
Ungroup(
AddColumns(
Customer,
"OrderTab",
RenameColumns(
Order,
"CustID",
"OCustID"
)
),
"OrderTab"
)
おわりに
Power Appsで開発する人のメインはこういったテーブル結合等にあまり慣れていないかもしれませんが、知っておくといろいろ便利ですので、ぜひ手元でも試してみてください。
発展的には、2つのSharePointリストを使ってみるというのがよいトレーニングになるかもしれません。