LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Power Query へそのゴマAdvent Calendar 2024

Day 16
@spumoni(spumoni)

Power Query へそのゴマ　第１６章　テーブルの結合

Last updated at Posted at 2024-12-15

この章では、Power Queryにおける複数のテーブルをまとめる方法と結合の仕組みについて詳しく解説します。特に、テーブルの結合や不一致データの処理に焦点を当て、実践的な例を交えながら効率的なデータ統合手法を学びます。

16.1. 複数テーブルの追加: Table.Combine

複数のテーブルをまとめるには、Table.Combine 関数を使用します。この関数は、同じ構造を持つ複数のテーブルを1つにまとめる際に便利です。

16.1.1. 基本的な使い方

以下の例では、月別売上データを3つのテーブルとして定義し、それらを1つのテーブルにまとめます。

let
    JanuarySales = Table.FromRecords({
        [Date = #date(2023, 1, 1), Sales = 100],
        [Date = #date(2023, 1, 2), Sales = 200]
    }),
    FebruarySales = Table.FromRecords({
        [Date = #date(2023, 2, 1), Sales = 150],
        [Date = #date(2023, 2, 2), Sales = 250]
    }),
    MarchSales = Table.FromRecords({
        [Date = #date(2023, 3, 1), Sales = 300],
        [Date = #date(2023, 3, 2), Sales = 400]
    }),
    CombinedSales = Table.Combine({JanuarySales, FebruarySales, MarchSales})
in
    CombinedSales

image.png

結合するテーブルの列の順番が違っていても、同じ列名同士で統合が行われます。

let
    JanuarySales = Table.FromRecords({
        [Date = #date(2023, 1, 1), Sales = 100],
        [Date = #date(2023, 1, 2), Sales = 200]
    }),
    FebruarySales = Table.FromRecords({
        [Sales = 150, Date = #date(2023, 2, 1)],
        [Sales = 250, Date = #date(2023, 2, 2)]
    }),
    CombinedSales = Table.Combine({JanuarySales, FebruarySales})
in
    CombinedSales

image.png

16.1.2. 異なる構造のテーブルを追加

異なる列名を持つテーブル同士を統合する場合、Table.Combine は欠損列を NULL で補完します。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"]
    }),
    Table2 = Table.FromRecords({
        [ID = 3, Age = 25],
        [ID = 4, Age = 30]
    }),
    Combined = Table.Combine({Table1, Table2})
in
    Combined

image.png

列名の大文字、小文字も区別されます。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"]
    }),
    Table2 = Table.FromRecords({
        [Id = 3, Age = 25],
        [Id = 4, Age = 30]
    }),
    Combined = Table.Combine({Table1, Table2})
in
    Combined

image.png

16.1.3 列名が異なるテーブルの追加

構造は同じでも、列名に差がある場合、項目名をそろえてから統合します。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice", Rank = 5],
        [ID = 2, Name = "Bob",   Rank = 4]
    }),

    Table2 = Table.FromRecords({
        [Id = 3, Name = "Chalry", Level = 3],
        [Id = 4, Name = "Devid",  Level = 4]
    }),

    // Table2の列名をTable1の列名に変更する
    ChangeColumnName = Table.RenameColumns(
        Table2,
        // 変換元と変換先のペアを作成
        List.Zip(
            {
                Table.ColumnNames(Table2),   // 変換元
                Table.ColumnNames(Table1)    // 変換先
            }
        )
    ),

    Combined = Table.Combine({Table1, ChangeColumnName})
in
    Combined

image.png

16.1.4. 大規模データセットでの注意点

Table.Combine を使用する際、大規模データセットでは以下の点に注意してください。

  • テーブルが非常に大きい場合、パフォーマンスに影響を与えることがあります。
  • 結合するテーブルの列構造が一致していることを事前に確認することで、不要なNULL値を防げます。

16.2. テーブル結合の種類

Power Queryでは、2つ以上のテーブルを結合して新しいデータセットを作成できます。これにより、異なるデータソースを統合し、一貫したビューを得ることが可能です。

Table.Join と Table.NestedJoin では、joinKind オプションで示される以下の6種類の結合がサポートされています。SQLに精通している人なら、これらの種類のジョインに馴染みがあるでしょう。

  • JoinKind.Inner: Inner Join（内部結合）
  • JoinKind.LeftOuter: Left Outer Join（左外部結合）
  • JoinKind.RightOuter: Right Outer Join（右外部結合）
  • JoinKind.FullOuter: Full Outer Join（完全外部結合）
  • Anti Join（アンチ結合）
    • JoinKind.LeftAnti: Left Anti Join（左反結合）
    • JoinKind.RightAnti: Right Anti Join（右反結合）
  • Semi Join（半結合）
    • JoinKind.LeftSemi: Left Semi Join（左半結合）
    • JoinKind.RightSemi: Right Semi Join（右半結合）

https___qiita-image-store.s3.ap-northeast-1.amazonaws.com_0_864075_92eb55b7-51a1-bc46-2437-8a18e42bf177.png

Left Semi Join は、Inner Join と同じく、両方のテーブルに共通するキーが存在するデータがマッチしますが、返されるのは左側のテーブルだけで、右側のテーブルのデータは null になっています。Right Semi Join は、返されるのは右側のテーブルだけです。

16.3. Table.Join

Table.Join と関数は、どちらもキー列を選択し、これらのキー列を比較して一致する行を見つけるジョイン操作を実行します。これら二つの関数は互いに似ていますが、主な違いは出力された結果が異なります。

Table.Join はフラットな結合テーブルが作成されますが、Table.NestedJoin はネストされたテーブルが作成されます。

16.3.1 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

16.3.2 JoinKind.Inner

JoinKind.Innerは、両方のテーブルに共通するキーを持つ行のみを取得します。
joinKindオプションが省略された場合、JoinKind.Innnerになります。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 4, Age = 18]
    }),

    Join = 
        Table.Join(
            Table1, "ID",
            Table2, "ID"
        )
in
    Join

image.png

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 1, Name = "Charie"],
        [ID = 3, Name = "David"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 2, Age = 18],
        [ID = 4, Age = 40]
    }),

    Join = 
        Table.Join(
            Table1, "ID",
            Table2, "ID"
        )
in
    Join

image.png

複数の一致があった場合、複数行に展開されます。

16.3.3 JoinKind.LeftOuter

JoinKind.LeftOuterは、左のテーブルのすべての行と、右のテーブルに一致する行を取得します。一致しない場合、NULLが入ります。

JoinKind.Inner以外のオプションでは、キー列の重複が許可されません。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 4, Age = 18]
    }),

    Join = 
        Table.Join(
            Table1, "ID",
            Table2, "ID",
            JoinKind.LeftOuter
        )
in
    Join

image.png

そこで、キーとなる列を別の名前に変更します。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 4, Age = 18]
    }),

    Join = 
        Table.Join(
            Table1, "ID",
            Table.RenameColumns(Table2, {"ID", "T2.ID"}), "T2.ID",
            JoinKind.LeftOuter
        )
in
    Join

image.png

16.3.4 複数のキー

キー列は複数選択することができます。キー列（第二および第四のパラメータ）はリストとして指定されていることに注意してください。

let
    Table1 = Table.FromRecords({
        [Team = 1, Member = 1, Name = "Alice"],
        [Team = 1, Member = 2, Name = "Bob"],
        [Team = 2, Member = 1, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [Team = 1, Member = 1, Age = 25],
        [Team = 1, Member = 2, Age = 30],
        [Team = 2, Member = 2, Age = 18]
    }),

    Join = 
        Table.Join(
            Table1, {"Team", "Member"},
            Table.PrefixColumns(Table2, "T2"), {"T2.Team", "T2.Member"},
            JoinKind.LeftOuter
        ),
    RemovedOtherColumns = Table.SelectColumns(Join,{"Team", "Member", "Name", "T2.Age"})
in
    RemovedOtherColumns

image.png

Team と Member の2つの項目をキーとして結合しています。

16.3.5 アルゴリズム

オプションの joinAlgorithm で、マージする際のアルゴリズムを指定することできます。

  • 既定の設定
    • JoinAlgorithm.Dynamic: 使用するアルゴリズムを自動的に決定します
  • バッファリング
    • JoinAlgorithm.PairwiseHash: 両方のテーブルをバッファリングする
      ※小規模なテーブルにのみ推奨されます。
    • JoinAlgorithm.LeftHash: 左テーブルの行をバッファリングする
    • JoinAlgorithm.RightHash: 右テーブルの行をバッファリングする
      ※バッファリングは ディメンションテーブル に対して行います。もう一方のテーブルのほとんどの行が一致する場合に推奨されます。
  • バッチ
    • JoinAlgorithm.LeftIndex: 左テーブルのキーを使用し右テーブルをバッチ処理する
    • JoinAlgorithm.RightIndex: 右テーブルのキーを使用し左テーブルをバッチ処理する
      ※バッチ処理されるテーブルが大きく、少数の一致が期待される場合に推奨されます。
  • ストリーミング
    • JoinAlgorithm.SortMerge: 非常に高速なストリーミングマージを行う
      ※注意点：両方のテーブルのキー列がソートされていなければいけない。ソートされていない場合は誤った結果を返します

16.3.6 keyEqualityComparers

このオプションは公開されていません。

16.4 Table.NestedJoin

16.4.1 構文

Table.NextedJoinは、新しい列にマージしたデータをテーブルで返します。

構文
Table.NestedJoin(
    table1 as table, 
    key1 as any, 
    table2 as any, 
    key2 as any, 
    newColumnName as text, 
    optional joinKind as nullable number, 
    optional keyEqualityComparers as nullable list
) as table

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 4, Age = 18]
    }),

    Join = 
        Table.NestedJoin(
            Table1, "ID",
            Table2, "ID",
            "NewColumn",
            JoinKind.LeftOuter
        )
in
    Join

1対多の関係になるので、結合結果は、テーブルで返されます。

image.png

従って、複数の一致があった場合、そのまま展開すると複数の行になります。

let
    Table1 = Table.FromRecords({
        [ID = 1, Name = "Alice"],
        [ID = 2, Name = "Bob"],
        [ID = 3, Name = "Charie"]
    }),
    Table2 = Table.FromRecords({
        [ID = 1, Age = 25],
        [ID = 2, Age = 30],
        [ID = 2, Age = 18]
    }),

    Join = 
        Table.NestedJoin(
            Table1, "ID",
            Table2, "ID",
            "NewColumn",
            JoinKind.LeftOuter
        ),
    #"Expanded {0}" = 
        Table.ExpandTableColumn(
            Join, 
            "NewColumn", 
            {"Age"}, 
            {"Age"}
        )
in
    #"Expanded {0}"

image.png

16.6 Table.FazzyNestedJoin および Table.FuzzyJoin

16.6.1 あいまい一致とは

Power Query には、あいまい一致（fuzzy matching）を利用してテーブルを結合するための Table.FuzzyJoin と Table.FuzzyNestedJoin 関数があります。これらの関数は、厳密一致ではなく、近似的な一致を基にテーブルを結合します。

Table.FuzzyJoin構文
Table.FuzzyJoin(
    table1 as table, 
    key1 as any, 
    table2 as table, 
    key2 as any, 
    optional joinKind as nullable number, 
    optional joinOptions as nullable record
) as table
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

これらの関数のパラメータは、Table.NestedJoin および Table.Join と類似していますが、joinAlgorithm パラメータをサポートしていません。

ペア間の類似性を測定するために、テキストマイニングでも使用されているJaccard類似度アルゴリズムを使用しています。

joinOptions パラメータは、テーブルをジョインする際に使用されるファジーマッチング基準を制御するオプションレコードです。

フィールド名 内容
ConcurrentRequests 使用するパラレル スレッドの数（1~8）既定は1
Culture カルチャ固有のルールに基づいて、レコードを一致させる。既定値は "" で、インバリアント カルチャに基づいて一致させます。ja-JP、en-US、en-UK などがあります。
IgnoreCase 大文字と小文字の区別 (true/false)
IgnoreSpace テキストの間のスペースを無視する (true/false)
NumberOfMatches 一致する行の最大数を指定する整数。このオプションが指定されていない場合は、一致するすべての行が返されます。
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" とも一致します。

16.6.2 基本的な使い方

let
    Table1 = 
        Table.FromRecords(
            {
                [CustomerID = 1, FirstName1 = "Bob", Phone = "555-1234"],
                [CustomerID = 2, FirstName1 = "Robert", Phone = "555-4567"]
            },
            type table [CustomerID = nullable number, FirstName1 = nullable text, Phone = nullable text]
        ),

    Table2 = 
        Table.FromRecords(
            {
                [CustomerStateID = 1, FirstName2 = "Bob", State = "TX"],
                [CustomerStateID = 2, FirstName2 = "bOB", State = "CA"]
            },
            type table [CustomerStateID = nullable number, FirstName2 = nullable text, State = nullable text]
        ),

    Join = 
        Table.FuzzyJoin(
            Table1, {"FirstName1"},
            Table2, {"FirstName2"},
            JoinKind.LeftOuter,
            [IgnoreCase = true, IgnoreSpace = false]
        )
in
    Join

image.png

上記の例では、Bob は、Bob と bOB に一致しています。

let
    Table1 = Table.FromRecords(
        {
            [
                Name = "Café", 
                Date = #date(2023, 1, 1), 
                Value = 100
            ]
        },
        type table [Name = text, Date = date, Value = number]
    ),
    Table2 = Table.FromRecords(
        {
            [
                Name = "Cafe", 
                Date = #date(2023, 1, 1), 
                Score = 90
            ]
        },
        type table [Name = text, Date = date, Score = number]
    ),
    FuzzyJoinedTable = Table.FuzzyJoin(
        Table1,
        {"Name"},
        Table.PrefixColumns(Table2, "T2"),
        {"T2.Name"},
        JoinKind.LeftOuter,
        [IgnoreCase=true, IgnoreSpace=true]
    )
in
    FuzzyJoinedTable

image.png

Café と Cafe も一致します。

16.6.3 日本語の揺れをどこまで把握できるか

標準の Threshold=0.8 でどこまで日本語の揺れをはあくできるのでしょうか。

  • 漢字の漢字の表記揺れ、異体字は一致しません。（例：「吉田」と「𠮷田」）
  • ひらがなとカタカナの違いも一致しません。（例：「よしだ」と「ヨシダ」）
  • 半角スペースと全角スペースは一致します（例：「み　ほ」と「み ほ」あるいは「みほ」）
  • ひらがな、カタカナ同士の場合、
    • 濁音、半濁音のある・なしの違いは無視されます。（例：「ぎんぱ」と「きんは」）
  • カタカナ同士の場合
    • 全角、半角は無視されます。（例：「スズキ」と「ｽｽﾞｷ」）

左テーブルのキーを「スズキ」にして以下のマッチングを調べました。

左テーブル 右テーブル Threshold=0.8 Threshold=1.0
スズキ すずき ☓ ☓
スズキ ｽｽﾞｷ 〇 ☓
スズキ ススギ 〇 〇
スズキ スギキ 〇 ☓
スズキ 鈴木 ☓ ☓
スズキ すゞき ☓ ☓

既定の Threshold=0.8では、「スギキ」さんも一致してしまいますので、あいまい一致を使う時には注意が必要です。

16.6.4 変換テーブルを使用したあいまい一致 TransformationTable

TransformationTable は、Table.FuzzyJoin や Table.FuzzyNestedJoin のオプションとして使用され、あいまい一致における置換ルールをカスタマイズするための仕組みです。このテーブルを指定することで、特定の文字列や単語を変換し、あいまい一致がより正確に行われるようになります。

TransformationTable は以下のようなテーブル形式で定義します：

  • From: 検索対象となる文字列を指定します。
  • To: 変換後の文字列を指定します。
let
    // 基準データ
    Table1 = Table.FromRecords(
        {
            [T1.名前="山田太郎", 部署="営業部"], 
            [T1.名前="佐藤花子", 部署="総務部"], 
            [T1.名前="鈴木次郎", 部署="開発部"]
        },
        type table [T1.名前 = text, 部署 = text]
    ),
    
    // 結合データ
    Table2 = Table.FromRecords(
        {
            [T2.名前="山田タロウ", 出勤状況="出勤"], 
            [T2.名前="佐藤はなこ", 出勤状況="休み"], 
            [T2.名前="鈴木ジロウ", 出勤状況="出勤"]
        },
        type table [T2.名前 = text, 出勤状況 = text]
    ),
    
    // TransformationTable の作成
    TransformationTable = Table.FromRecords(
        {
            [From="山田タロウ", To="山田太郎"],
            [From="佐藤はなこ", To="佐藤花子"],
            [From="鈴木ジロウ", To="鈴木次郎"]
        },
        type table [From = text, To = text]
    ),
    
    // FuzzyJoinの実行
    FuzzyJoinedTable = 
        Table.FuzzyJoin(
            Table1, 
            {"T1.名前"}, 
            Table2, 
            {"T2.名前"}, 
            JoinKind.LeftOuter, 
            [
                TransformationTable = TransformationTable, // 変換テーブルを指定
                IgnoreCase=true, 
                IgnoreSpace=true, 
                Threshold=0.8
            ]
    )
in
    FuzzyJoinedTable

image.png

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?