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

Power Query(パワークエリ)とM言語を練習問題を通して学んでいきます
公開日:2023-02-15 最終更新日:2023-02-17

2つのテーブルのマージ


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


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

パワク8本目

Excel エクセル Power Query M言語
※ツイート画像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つのテーブルに対して、それぞれのクエリを作成します。
作製するテーブル内のセルを選択してから、

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

このようにして、「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都道府県をマージします。

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

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

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

以上で作成されるスクリブトは、
let
    ソース = Table.NestedJoin(tbl個人, {"都道府県2"}, tbl都道府県, {"都道府県2"}, "tbl都道府県", JoinKind.LeftOuter),
    #"展開された tbl都道府県" = Table.ExpandTableColumn(ソース, "tbl都道府県", {"コード", "都道府県"}, {"tbl都道府県.コード", "tbl都道府県.都道府県"})
in
    #"展開された tbl都道府県"
解答スクリブトは、これらを元にして、スクリブトを直接書いたものになります。


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画面操作で作成されたスクリブトの意味を調べる時に見れば良いと思います。

Excel エクセル Power Query M言語


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画面で、左右に開いたこのアイコンをクリックした時に作成されるスクリブトになります。

Excel エクセル Power Query M言語


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

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つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新旧マスタの差異比較
有効な最新単価の取得


新着記事NEW ・・・新着記事一覧を見る

無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.セルのクリア(Clear,ClearContents)|VBA入門




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


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



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