2つのテーブルのマージ
「パワク8本目」
Power Query(M言語)の問題と解答・解説
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。
ページ内目次
パワク8本目
名前 | 性別 | 誕生日 | 都道府県 |
対馬 くるみ | 女 | 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 | 沖縄県 |
ブック内に、「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.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
列順変更
それ以外はあいまい一致と同じです。
GUI操作での作成
作製するテーブル内のセルを選択してから、
カスタム列を追加するには、
列の式については、関数を知る必要がありますが、これは徐々に覚えて行けば良いでしょう。
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
追加されたカスタム
let
ソース = Excel.CurrentWorkbook(){[Name="tbl都道府県"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"コード", Int64.Type}, {"都道府県", type text}}),
追加されたカスタム = Table.AddColumn(変更された型, "都道府県2", each Text.Start([都道府県],2))
in
追加されたカスタム
あいまい一致を使用してマージさせることもできます。
指定するテーブルの順序を意識してください。
上に指定するテーブルが左、下に指定するテーブルが右になります。
let
ソース = Table.NestedJoin(tbl個人, {"都道府県2"}, tbl都道府県, {"都道府県2"}, "tbl都道府県", JoinKind.LeftOuter),
#"展開された tbl都道府県" = Table.ExpandTableColumn(ソース, "tbl都道府県", {"コード", "都道府県"}, {"tbl都道府県.コード", "tbl都道府県.都道府県"})
in
#"展開された tbl都道府県"
Table.FuzzyNestedJoin
あいまい一致は、テキストの等価性ではなく、テキストの類似性に基づく比較です。
既定では、joinKind が指定されていない場合は、左外部結合が実行されます。
JoinKind.LeftOuter
JoinKind.RightOuter
JoinKind.FullOuter
JoinKind.LeftAnti
JoinKind.RightAnti
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
columnNames は、内側のテーブルから展開する列を選択するために使用します。
既存の列と新しい列が競合しないようにするには、newColumnNames を指定します。
Table.RemoveColumns
列が存在しない場合は、省略可能なパラメーター missingField によって代替手段が指定されていない限り、例外がスローされます (例: MissingField.UseNull、MissingField.Ignore)。
Table.ReorderColumns
リストに指定されていない列の順序は変更されません。
列が存在しない場合は、省略可能なパラメーター missingField によって代替手段が指定されていない限り、例外がスローされます (例: MissingField.UseNull、MissingField.Ignore)。
同じテーマ「PowerQuery(M言語)入門」の記事
別ブックの最終シートの取り込み
列数不定のCSVの取り込み
CSVのA列が日付の行だけを取り込む
A列のヘッダー名を変更する
「売上」が数値の行のみ取り込む
2つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新旧マスタの差異比較
有効な最新単価の取得
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.繰り返し処理(For Next)|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.ブック・シートの選択(Select,Activate)|VBA入門
- ホーム
- その他
- PowerQuery(M言語)入門
- 2つのテーブルのマージ
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。