【Power Query M式言語】２つの表を結合する

Power Query のM式言語で２つの表を結合する方法を説明した記事です。

M式言語についてはこちら⇒(まとめエントリ執筆中・・・)

環境

Windows10
Excel2019

Table.Join関数

Table.Join関数を使うと結合キーを指定して２つの表を簡単に結合することができます。

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

joinKindは結合の種類です。左右の表を結合するのに、一致したレコードだけを結果として表示するのか、一致しなかったレコードも表示するのか、どちらのテーブルのレコードも全部表示するのか・・・。専門用語だと、内部結合や外部結合といったりします。

デフォルトだと内部結合です。結合の種類に指定できるのは以下の６つになります。

//内部結合。どちらも一致したレコードだけ
JoinKind.Inner
//table1は全てのレコード、table2は条件が一致したレコードだけ
JoinKind.LeftOuter
//LeftOuterの左右逆。
JoinKind.RightOuter
//完全外部結合。どちらも全部のレコード
JoinKind.FullOuter
//左反結合。table2に一致するものがないtable1の全てのレコード
JoinKind.LeftAnti
//右反結合。table1に一致するものがないtable2の全てのレコード
JoinKind.RightAnti

2つの表を結合する

次の例では、２つの表を作成し、それぞれを出身コードをキーに結合しています。

 

let
レコード= Table.FromRecords({[名前 = "山田　太郎", 出身コード= 12], [名前 = "佐藤　由香", 出身コード=32 ], [名前 = "鈴木　一郎", 出身コード=4 ],[名前 = "石井　理沙", 出身コード= 1]}),
コードテーブル=Table.FromRecords({[出身コード=4,コード名=" 宮城県"],[出身コード=12,コード名=" 千葉県"],[出身コード=32,コード名=" 島根県"],[出身コード=42,コード名="沖縄県"]}),
結合=Table.Join(レコード,"出身コード",コードテーブル,"出身コード")
in
結合

結果

デフォルトの挙動は内部結合のため、レコードテーブルの石井理沙さんと、コードテーブルの沖縄県は無視されました。

完全外部結合のJoinKind.FullOuterを使ってみます。

 

let
人テーブル= Table.FromRecords({ [名前 = "山田　太郎",好きな食べ物= 1], [名前 = "佐藤　由香", 好きな食べ物= 2 ], [名前 = "鈴木　一郎",好きな食べ物= 3 ],[名前 = "石井　理沙",好きな食べ物= 1],[名前 = "山田　次郎",好きな食べ物= 10]}),
食べ物テーブル=Table.FromRecords({[食べ物コード=1,食べ物="りんご"],[食べ物コード=2,食べ物="カレー"],[食べ物コード=3,食べ物="寿司"],[食べ物コード=4,食べ物="ごはん"],[食べ物コード=5,食べ物="焼き魚"]}),
結合=Table.Join(人テーブル,"好きな食べ物",食べ物テーブル,"食べ物コード",JoinKind.FullOuter)
in
結合

結果

両方のテーブルのレコードが全て表示されました。指定したキーで一致しないレコードについても表示されています。例えば人テーブルには、ごはんと焼き魚を好きな人がいなかったので、その分5・6レコード目の名前と好きな食べ物はnullになっています。また、山田次郎さんは10の食べ物が好きということでしたが、食べ物テーブルに10の値が設定されていなかったため、食べ物がnullになっています。

Table.NestedJoin関数

結合するテーブルに重複したカラム名がある場合にはTable.Join関数を実行しようとするとエラーになります。その場合はTable.NestedJoin関数とTable.ExpandTableColumn関数を使用することで回避することができます。

Table.NestedJoin関数はNestedという言葉の通り、テーブルの中にテーブルがある表を作るときに使います。

ここからは推測ですが、結合しようとしているテーブル間でカラム名が重複している場合、素直に結合しようとすると同じカラム名が存在してしまいます。Excelの表とは違い、テーブルでは同じカラム名は許されません。SQLではテーブル名.カラム名と処理してよしなに結合してくれますが、ある種の柔軟性を持たせるために一度、テーブルの中にテーブルを持たせて、そして展開させることでテーブル名.カラム名と表示させているのでは？と一瞬考えたのですが、私もSQLの内部アルゴリズムに詳しいわけでもないし、M式言語のドキュメントもまだ少ない状況なので結論よくわかりません。

Table.NestedJoin関数の構文は次の通りです。

Table.NestedJoin(table1as table,key1as any,table2as any,key2as any,newColumnNameas text, optionaljoinKindas nullable number, optionalkeyEqualityComparersas nullable list) as table

基本的にはTable.Join関数と同じですが、newColumnNameというパラメータが追加されています。これは、新しいカラムの中にテーブルが入ってくるのですが、そのカラム名になります。まさにNestなわけですね。

Table.ExpandTableColumn関数

カラム内のテーブルを展開する関数です。構文は次の通り。

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

tableは展開対象のテーブル、columnは展開する列名、columnNamesは展開するカラム名、そしてnewColumnNamesは、新しいカラムの名前をつける場合に指定します。重複するカラム名がある場合はここで必ずnewColumnNamesを指定する必要があります。

重複するカラム名があるテーブル同士を連結する

Table.NestedJoin関数とTable.ExpandTableColumn関数を組み合わせて、重複するカラム名があるテーブル同士を連結してみます。今回は左外部結合(JoinKind.LeftOuter)を指定します。

 

let
レコード= Table.FromRecords({ [名前 = "山田　太郎", 出身コード= 12], [名前 = "佐藤　由香", 出身コード=32 ], [名前 = "鈴木　一郎", 出身コード=4 ],[名前 = "石井　理沙", 出身コード= 1]}),
コードテーブル=Table.FromRecords({[出身コード=4,コード名=" 宮城県"],[出身コード=12,コード名=" 千葉県"],[出身コード=32,コード名=" 島根県"],[出身コード=42,コード名="沖縄県"]}),
結合=Table.NestedJoin(レコード,"出身コード",コードテーブル,"出身コード","コード", JoinKind.LeftOuter),
展開=Table.ExpandTableColumn(結合, "コード", {"出身コード", "コード名"}, {"コード.出身コード", "コード.コード名"})
in
展開

結果

コードの動きがよくわからないときは、ステップを取消したりしてひとつひとつのステップを確認するといいかと思います。

をクリックするとテーブルが展開されます。

そして自動生成されるコードにもTable.ExpandTableColumn関数が使われていることがわかります。

終わりに

M式言語で2つの表を結合する方法を紹介しました。

これを使えば、別のExcelの表を結合したり、別のCSVファイルを結合するといったことが簡単にできるようになります。

とは言ってみたものの、いざ詳細エディタでM式言語のコードを書いてみると、Excel2019の環境だと詳細エディタがただのテキストエディタなのでちょっと面倒・・・。これならGUIでカチカチやった方が速いかも・・・。エディタのこれからの進化に期待してます！！

というわけでここまでお読みいただき、ありがとうございました。