2つのテーブルのマージ｜Power Query（M言語）入門

公開日：2023-02-15　最終更新日：2023-02-17

2つのテーブルのマージ

「パワク8本目」
Power Query（M言語）の問題と解答・解説

本シリーズは、ツイッターと連動企画になります。
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。

ページ内目次

パワク8本目
解答コード
GUI操作での作成
Table.FuzzyNestedJoin
Table.ExpandTableColumn
Table.RemoveColumns
Table.ReorderColumns

パワク8本目

※ツイート画像ALTのサンブルデータは文字数制限で途中で切れています。

名前	性別	誕生日	都道府県
対馬くるみ	女	2022/6/24	茨城
富沢早紀	女	2022/12/21	鳥取
中川めぐみ	女	2022/3/10	茨城
佐川長利	男	2022/4/22	埼玉
渡部朝陽	女	2022/6/1	神奈川
橘さとみ	女	2022/12/21	北海道
桜田亮	男	2022/4/19	北海道
波多野里穂	女	2022/3/1	大阪府
藤森芽以	女	2022/4/9	青森
湊優一	男	2022/12/28	広島
吉村ひろみ	女	2022/9/18	東京都
彼方洋介	男	2023/1/13	香川
川端明慶	男	2022/6/27	大阪府
立花まさし	男	2022/10/10	岩手
芦田仁晶	男	2022/7/24	青森
野口涼子	女	2023/1/2	福岡
小松勇介	男	2022/8/14	山梨
村上奈々	女	2022/12/30	埼玉
中尾ひかり	女	2022/4/14	静岡
山川光洋	男	2022/5/24	長崎
佐川新太	男	2022/7/23	大阪府
奥田裕次郎	男	2022/5/31	兵庫
金山正敏	男	2022/12/18	千葉
高畑秀樹	男	2022/5/27	山梨
熊田なつみ	女	2022/9/25	東京都
浦田茂樹	男	2022/4/21	福島
岡田仁晶	男	2022/12/27	東京都
野上翔太	男	2022/3/15	埼玉
今西一恵	女	2023/2/7	埼玉
松岡勇一	男	2022/4/18	長野
多田翔太	男	2022/11/5	広島
曽根かおり	女	2022/4/5	三重
熊沢豊	男	2022/5/17	長崎
大矢恵梨香	女	2022/7/5	大阪府
近藤美優	女	2022/3/8	広島
豊原達士	男	2022/8/8	静岡
高木佑	男	2022/10/2	鹿児島
堀江めぐみ	女	2022/12/13	千葉
西野恵美	女	2022/7/15	兵庫
合田翔太	男	2022/10/18	愛知
吹石寛治	男	2022/9/27	滋賀
野本亮介	男	2022/12/23	愛知
早川康文	男	2022/2/27	北海道
パンツェッタ優	男	2022/12/24	東京都
平林花	女	2022/5/26	兵庫
坂本獅童	男	2023/2/12
戸田真悠子	女	2022/12/21	福岡
三沢恵梨香	女	2022/11/19	秋田
門脇美里	女	2022/5/26	東京都
笹川右京	男	2022/8/12	島根

コード	都道府県
1	北海道
2	青森県
3	岩手県
4	宮城県
5	秋田県
6	山形県
7	福島県
8	茨城県
9	栃木県
10	群馬県
11	埼玉県
12	千葉県
13	東京都
14	神奈川県
15	新潟県
16	富山県
17	石川県
18	福井県
19	山梨県
20	長野県
21	岐阜県
22	静岡県
23	愛知県
24	三重県
25	滋賀県
26	京都府
27	大阪府
28	兵庫県
29	奈良県
30	和歌山県
31	鳥取県
32	島根県
33	岡山県
34	広島県
35	山口県
36	徳島県
37	香川県
38	愛媛県
39	高知県
40	福岡県
41	佐賀県
42	長崎県
43	熊本県
44	大分県
45	宮崎県
46	鹿児島県
47	沖縄県

#パワク8本目
ブック内に、「tbl個人」「tbl都道府県」があります。
この2つのテーブルを結合して、添付サンブルのように出力してください。
※「tbl個人」の都道府県には「県」の文字が入っていません。出力は「県」の入った県名で。
※サンブルデータはALT
#PowerQuery #M言語

解答コード

あいまい一致でマージ

let
    tbl個人 = Excel.CurrentWorkbook(){[Name="tbl個人"]}[Content],
    tbl都道府県 = Excel.CurrentWorkbook(){[Name="tbl都道府県"]}[Content],
    tbl個人2 = Table.TransformColumnTypes(tbl個人,{{"誕生日", type date}, {"都道府県", type text}}),
    tbl都道府県2 = Table.TransformColumnTypes(tbl都道府県,{{"都道府県", type text}}),
    あいまい結合 = Table.FuzzyNestedJoin(tbl個人2, {"都道府県"}, tbl都道府県2, {"都道府県"}, "テーブル都道府県", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    結合展開 = Table.ExpandTableColumn(あいまい結合, "テーブル都道府県", {"コード", "都道府県"}, {"テーブル都道府県.コード", "テーブル都道府県.都道府県"}),
    列削除 = Table.RemoveColumns(結合展開,{"都道府県"}),
    列名変更 = Table.RenameColumns(列削除,{{"テーブル都道府県.都道府県", "都道府県"}, {"テーブル都道府県.コード", "都道府県コード"}}),
    列順変更 = Table.ReorderColumns(列名変更,{"名前", "性別", "誕生日", "都道府県", "都道府県コード"})
in
    列順変更

あいまい一致には、
Table.FuzzyNestedJoin
これを使います。
ただし、キーは文字列型である必要があるので、その前にTable.TransformColumnTypesで変換しています。

Table.FuzzyNestedJoin
Table.ExpandTableColumn
Table.RemoveColumns
Table.ReorderColumns
以上については後述します。

先頭2桁の一致でマージ

let
    tbl個人 = Excel.CurrentWorkbook(){[Name="tbl個人"]}[Content],
    tbl都道府県 = Excel.CurrentWorkbook(){[Name="tbl都道府県"]}[Content],
    tbl個人2 = Table.TransformColumns(tbl個人, {{"都道府県", each Text.Start(_, 2), type text}}),
    tbl都道府県2 = Table.AddColumn(tbl都道府県, "都道府県2", each Text.Start([都道府県], 2)),
    完全一致結合 = Table.NestedJoin(tbl個人2, {"都道府県"}, tbl都道府県2, {"都道府県2"}, "テーブル都道府県", JoinKind.LeftOuter),
    結合展開 = Table.ExpandTableColumn(完全一致結合, "テーブル都道府県", {"コード", "都道府県"}, {"テーブル都道府県.コード", "テーブル都道府県.都道府県"}),
    列削除 = Table.RemoveColumns(結合展開,{"都道府県"}),
    列名変更 = Table.RenameColumns(列削除,{{"テーブル都道府県.都道府県", "都道府県"}, {"テーブル都道府県.コード", "都道府県コード"}}),
    列順変更 = Table.ReorderColumns(列名変更,{"名前", "性別", "誕生日", "都道府県", "都道府県コード"})
in
    列順変更

都道府県の先頭2文字だけでの完全一致で結合しています。
それ以外はあいまい一致と同じです。

GUI操作での作成

結合する2つのテーブルに対して、それぞれのクエリを作成します。
作製するテーブル内のセルを選択してから、

日付に時刻はいらないので、上記では型を「日付」に変更しました。
カスタム列を追加するには、

列名は任意（重複しない）の名前を付けてください。
列の式については、関数を知る必要がありますが、これは徐々に覚えて行けば良いでしょう。

テーブルの列値を使う場合は、右側の「使用できる列」から選択できます。

このようにして、「tbl個人」「tbl都道府県」の2つを作成します。
tbl個人

let
    ソース = Excel.CurrentWorkbook(){[Name="tbl個人"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"名前", type text}, {"性別", type text}, {"誕生日", type date}, {"都道府県", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "都道府県2", each Text.Start([都道府県],2))
in
    追加されたカスタム

tbl都道府県

let
    ソース = Excel.CurrentWorkbook(){[Name="tbl都道府県"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"コード", Int64.Type}, {"都道府県", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "都道府県2", each Text.Start([都道府県],2))
in
    追加されたカスタム

tbl個人とtbl都道府県をマージします。

結合するテーブルと結合する列を選択します。
あいまい一致を使用してマージさせることもできます。
指定するテーブルの順序を意識してください。
上に指定するテーブルが左、下に指定するテーブルが右になります。

下に指定した（右テーブル）が最終列に結合されます。

左右開きのアイコンをクリックして列を展開します。

以上で作成されるスクリブトは、

let
    ソース = Table.NestedJoin(tbl個人, {"都道府県2"}, tbl都道府県, {"都道府県2"}, "tbl都道府県", JoinKind.LeftOuter),
    #"展開された tbl都道府県" = Table.ExpandTableColumn(ソース, "tbl都道府県", {"コード", "都道府県"}, {"tbl都道府県.コード", "tbl都道府県.都道府県"})
in
    #"展開された tbl都道府県"

解答スクリブトは、これらを元にして、スクリブトを直接書いたものになります。

Table.FuzzyNestedJoin

※Learn Table.FuzzyNestedJoin

構文

Table.FuzzyNestedJoin(table1 as table, key1 as any, table2 as table, key2 as any, newColumnName as text, optional joinKind as nullable number, optional joinOptions as nullable record) as table

key1 (table1) および key2 (table2) によって選択されたキー列の値のあいまい一致に基づいて、table1 の行が table2 の行と結合されます。 結果は、newColumnName という名前の新しい列に返されます。
あいまい一致は、テキストの等価性ではなく、テキストの類似性に基づく比較です。

省略可能な joinKind によって、実行する結合の種類が指定されます。
既定では、joinKind が指定されていない場合は、左外部結合が実行されます。

JoinKind.Inner
JoinKind.LeftOuter
JoinKind.RightOuter
JoinKind.FullOuter
JoinKind.LeftAnti
JoinKind.RightAnti

キー列の比較方法を指定するために、省略可能な joinOptions のセットを含めることができます。次のオプションがあります。

ConcurrentRequests	あいまい一致に使用するパラレルスレッドの数に指定できる、1 から 8 の数値。既定値は 1 です。
Culture	カルチャ固有のルールに基づいて、レコードを一致させることができます。有効な任意のカルチャ名を指定できます。たとえば、"ja-JP" というカルチャオプションでは、日本語カルチャに基づいてレコードを一致させます。既定値は " であり、インバリアント (英語) カルチャに基づいて一致させます。
IgnoreCase	大文字と小文字が区別されないキーの一致ができる論理 (true/false) 値。たとえば、true の場合、"Grapes" は "grapes" に一致します。既定値は true です。
IgnoreSpace	一致を見つけるためにテキスト部分を結合できる論理 (true/false) 値。たとえば、true の場合、"Gra pes" は "Grapes" に一致します。既定値は true です。
NumberOfMatches	各入力行に対し返すことができる、一致する行の最大数を指定する整数。たとえば、値 1 を指定すると、入力行ごとに一致する行が 1 つだけ返されます。このオプションが指定されていない場合は、一致するすべての行が返されます。
SimilarityColumnName	入力値とその入力の代表値の類似性を示す列の名前です。既定値は null です。この場合、類似性を示す新しい列は追加されません。
Threshold	2 つの値が一致される類似性スコアに指定できる、0.00 から 1.00 の数値。たとえば、"Grapes" と "Graes" ("p" がない) が一致するのは、このオプションが 0.90 未満に設定されている場合のみです。しきい値が 1.00 の場合は、完全一致のみが許可されます。 (あいまいな "完全一致" では、大文字と小文字の区別、語順、句読点などの違いが無視される場合があることに注意してください)。既定値は 0.80 です。
TransformationTable	カスタム値のマッピングに基づいて、レコードを一致させることができるテーブル。 "From" 列と "To" 列が含まれている必要があります。たとえば、"From" 列に "Grapes" が含まれ、"To" 列に "Raisins" が含まれる変換テーブルが指定されている場合、"Grapes" は "Raisins" と一致します。変換は、変換テーブル内のすべてのテキストに適用されることに注意してください。上記の変換テーブルでは、"Grapes are sweet" は "Raisins are sweet" とも一致します。

解答スクリブトは、
Table.FuzzyNestedJoin(tbl個人2, {"都道府県"}, tbl都道府県2, {"都道府県"},"テーブル都道府県", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
joinOptionsについては、
[IgnoreCase=true, IgnoreSpace=true]
これだけになります。
GUI画面操作で作成されたスクリブトの意味を調べる時に見れば良いと思います。

Table.ExpandTableColumn

※Learn Table.ExpandTableColumn

構文

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

table[column] 内のテーブルを複数の行と列に展開します。
columnNames は、内側のテーブルから展開する列を選択するために使用します。
既存の列と新しい列が競合しないようにするには、newColumnNames を指定します。

GUI画面で、左右に開いたこのアイコンをクリックした時に作成されるスクリブトになります。

Table.RemoveColumns

※Learn Table.RemoveColumns

構文

Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table

指定された columns を、指定された table から削除します。
列が存在しない場合は、省略可能なパラメーター missingField によって代替手段が指定されていない限り、例外がスローされます (例: MissingField.UseNull、MissingField.Ignore)。

Table.ReorderColumns

※Learn Table.ReorderColumns

構文

Table.ReorderColumns(table as table, columnOrder as list, optional missingField as nullable number) as table

入力 table からテーブルを返しますが、列の順序が columnOrder で指定された順序になります。
リストに指定されていない列の順序は変更されません。
列が存在しない場合は、省略可能なパラメーター missingField によって代替手段が指定されていない限り、例外がスローされます (例: MissingField.UseNull、MissingField.Ignore)。

同じテーマ「PowerQuery（M言語）入門」の記事

別ブックの最終シートの取り込み
 列数不定のCSVの取り込み
 CSVのA列が日付の行だけを取り込む
 A列のヘッダー名を変更する
 「売上」が数値の行のみ取り込む
2つのテーブルのマージ
グルーブ内の最小・最大
 グルーブ内の最小・最大
 有効な最新単価の取得
 新旧マスタの差異比較
 有効な最新単価の取得

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
その他
PowerQuery（M言語）入門
2つのテーブルのマージ

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

Power Query（M言語）入門
2つのテーブルのマージ