LoginSignup
17
18

More than 1 year has passed since last update.

@spumoni(spumoni)

[Power BI] Power Queryでデータをマージする様々な方法

Last updated at Posted at 2023-03-18

※この記事は、2023年3月18日の「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #24」で使用しました。

Power QueryはPower BIだけでなく、Excelでも使用でき、様々なデータの結合を行うことができますので、Excel利用者にも覚えてほしい技だと思います。

マージの基本 1

　２つの表を、キーを使ってマージしたいという場合、Power Queryエディターの　ホームタブ -> 結合グループ -> クエリのマージ　を選択します。
　クエリのマージには、現在選択してるクエリに続けてマージ処理を行う 「クエリのマージ」 と、新たにクエリを作成する 「新規としてクエリをマージ」 の２つが選択できます。
image.png

　マージのダイアログが表示され、結合のキーとなる列の選択を行い、結合の種類を選択します。Power Queryのマージでは６種類が選択できます

　そして、マージを実行すると、マージされた内容が「Table」として新しい列に追加されます。

　これを展開すると、以下のような結果を得ることができます。

　この操作で使われる関数は Table.NestedJoin2 です。

Table.NestedJoin構文
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

JoinKindには、以下のような種類があります。

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

　keyEqualityComparersについては、内部で使用するためのものであるということで、詳細な説明はありません。

　Table.NestedJoin 以外に、 Table.Join3 という関数もあります。これは、UIからの操作では使えませんが、結合の結果をテーブルではなく、展開された形で取得します。Table.NestedJoinは、内部で 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

joinAlgorithmは、結合時の処理の種類が選択できます。4

多対多の結合

　エクセルのVLOOKUPでは、１対１あるいは多対１の結合のみしか行うことができません。複数の一致するデータがあった場合、最初に一致したものしか表示できないのです。（いわゆるSemi Join）
　例えば、以下のように柿の種に複数のメーカーが存在していても、表示できるのは最初の１社のみです。

　Power Queryでは、マージ部分が表で返されるため、以下のように一致したすべてのデータが入っています。

　したがって、商品マスターの列を展開すると、以下のように、柿の種が複数のデータに分かれて結合されてしまいます。

　データをマージする際は、参照される表のキーがユニークかどうか、注意して結合する必要があります。

結合の種類

　結合には複数の種類があるということを純粋なExcelユーザーには知らされていませんでした。表の結合を行うには、VLOOKUP（あるいはINDEXとMATCH）で左外部結合を行うしかなかったからです。Power Queryでは、様々な結合タイプが選択でき、一致するデータだけでなく、一致しないデータも見つけることができます。これは、データの一致や照合を試みる場合、非常に重要なことです。

　今回の例では、左の表として「購入履歴」、右の表として「商品マスター」を使います。購入履歴には「ホワイトロリータ」がありますが、商品マスターに該当の商品がありません。逆に、「ルマンド」は商品マスターにありますが、購入履歴がないというデータになっています。結合する項目は、購入履歴の日付、商品名、商品マスターの種類、製造の項目で結合します。

左外部(Left Outer Join) 5

Power Queryの結合の種類では、「左外部（最初の行すべて、および２番目の行のうち一致するもの）」と書かれており、左右とか上下とか混乱してしまいます。書き直すと、以下のようになります。

マージのダイアログで上で選択した表の行と、下で選択した表の行を「左外部」結合します。上の表のすべての行と、下の表は一致した行のみ表示します。

　今回、購入履歴（左の表）にある商品名「ホワイトロリータ」が商品マスター（右の表）ないというケースを見てみます。以下のように、商品マスターから持ってきている項目の商品の種類と製造の項目がnullになり、商品マスターにレコードが不足していることが分かります。

　また、マージ後に購入履歴のレコードが増えていたら、商品マスターのキーに重複があることが分かります。

右外部(Right Outer Join) 6

　反対に、「右外部」の場合は右の表（商品マスター）にはあるが、左の表（購入履歴）がないものについてです。商品名「ルマンド」は購入履歴にありませんが、商品マスターに載っています。購入履歴から持ってきている項目の購入日、製品名がnullになっています。一回も購入されなかった商品です。

完全外部(Full Outer Join) 7

　2つの表のすべての項目が結合されます。一致しないものについては、nullが入ります。

内部(Inner Join) 8

　内部結合は、2つの表で一致しているもののみ結合されます。一方の表のみにあるものは入りません。先の「ルマンド」も「ホワイトロリータ」も除外されます。

左反(Left Anti Join) 9

　購入履歴にあって、商品マスターにない「ルマンド」が入ります。

右反(Right Anti Join) 10

　商品マスターにあって、購入履歴にない「ホワイトロリータ」が入ります。

その他の結合方法

　以下は、「クエリのマージ」で行うことはできない結合方法です。

完全アンチ結合 (Full Anti Join)

　一致しないすべてのレコードです。

　これを行うには、「左反」結合と「右反」結合の２つを「クエリの追加」で1つの表にします。

交差結合(Cross Join) 11

　交差結合、クロスジョイン、多対多ジョイン、またはカルテジアン結合、デカルト積など様々な呼び方がああるようですが、2つの集合のすべての組み合わせを作成します。
　片方の表に2個、もう片方の表に3個の値がある場合、結合の結果、 2 × 3 = 6 で、6個の値が返されます。
image.png
　これを実現するのは、簡単です。
　まず、1つのテーブルを参照するクエリ「交差結合」を作成します。

　「列の追加」タブから、「全般」グループの「カスタム列」を選択し、式の部分にもう１つのテーブル名を書きます。

　追加された列を展開すれば、交差結合した表が完成します。

総当たり表

　以下のようなテーブルの項目を使って、全ての組み合わせの表を作りたい場合です。
image.png
image.png
　まず、交差結合をつくります。
image.png
　次に、フィルターの機能をちょっといじって、左の列と右の列の名前が同じものを削除します。

　この部分のコードは以下のようになります。

#"Filtered Rows" = 
    Table.SelectRows(
        #"Expanded {0}", 
        each ([名前] <> [名前.1])
    )

　ただし、これでは「パンダ」対「ペンギン」の左右入れ替えた「ペンギン」対「パンダ」の対戦カードが含まれてしまいます。「パンダ」対「ペンギン」の組み合わせが１回のみになるように作成してみます。
image.png
　まず、「動物」テーブルにインデックスを作成します。
image.png
　次に、列の追加を行いますが、現在の行のインデックス番号より大きいインデックス番号のレコードがテーブルに含まれるよう関数を書きます。操作は、以下のように行います。

　最後に、対戦カードが出尽くしてnullになったキリンの対戦を削除して出来上がりです。
　項目を追加した部分のコードは、以下のようになります。（自動で作成されたコードを整形してます）

#"Added Custom" = 
    Table.AddColumn(
        Source, 
        "カスタム", 
        (x) =>  Table.SelectRows(
                    動物2,
                    each _[インデックス] > x[インデックス]
        )
    )

　全10試合の対戦表が出来上がりました。
image.png

この話を「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #24」で行ったところ、Kagata先生(
@PowerBIxyz )から、「インデックス作らなくてもいけるよ」とアドバイスをもらったので、以下のように作り変えました。

#"Added Custom" = 
    Table.AddColumn(
        Source, 
        "カスタム", 
        (x) =>  Table.SelectRows(
                    動物2,
                    each _[名前] > x[名前]
        )
    )

　名前を比較し、大小を判定しています。元の名前が昇順ではないのできれいな並びになっていませんが、きちんと出来上がりました。
image.png

近似一致

　ExcelのVLOOKUPにある、近似一致を行いたい場合です。
　以下のように注文数に応じて単価が変わる場合、VLOOKUPを使って簡単に表を作成することができます。
image.png

クエリの追加を使う

　2つのテーブルを１つに合わせ、フィル・ダウンで単価を求めます。

　全データの並び替えを行うため、注文履歴のデータ数が大量になった場合、速度が低下します。

関数を使う

　先ほどの総当たりでは、(x) => ~~~~ のように、クエリの内部に関数を書きましたが、今回は注文数を与えると単価が返ってくる関数をクエリから作成してみます。
　まず、注文数をパラメータに設定します。値はサンプルなので何でもいいです。
image.png
　パラメータを使って単価を返すクエリをサクッと書きます。

単価計算
let
    SelectedTable = 
        Table.SelectRows(
            単価テーブル,
            each _[注文数] <= Units
        ),
    Result = Table.Last(SelectedTable)[単価]
in
    Result

　このクエリを関数に変換して「Fx単価計算」を作ります。


　「列の追加」で、「カスタム関数の呼び出し」に「Fx単価計算」を関数クエリとして使用し、パラメータとして「注文数」を設定します。

　これで、以下のような表が出来上がります。
image.png

VLOOKUP風のSemi Join

　VLOOKUPの完全一致では、一致した最初の値のみ返されます。これと同じ動作をPower Queryで作成します。
　これも、関数を使った方が簡単なので、まずパラメータを作成します。
image.png
　このパラメータを使って、商品マスターから該当するレコードを最初の1つだけ探し出すクエリを作ります。

VLOOKUP
let
    SelectedRow = Table.SelectRows(
        商品マスター,
        each [製品名] = Value
    )
in
    Table.First(SelectedRow)

　このクエリを使って関数を作ります。
image.png

　購入履歴の表に列の追加で「カスタム関数の呼び出し」を行います。
image.png
　追加された列は、以下のようなレコードになります。
image.png
　これを展開すれば、以下のようになります。柿の種は亀田製菓のみになります。

まとめ

注意しなければならないのはデータモデルの設計をすることで、それよってマージをどのように使うか決まります。

  1. クエリのマージの概要 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-overview) ↩

  2. Table.NestedJoin (https://learn.microsoft.com/ja-jp/powerquery-m/table-nestedjoin) ↩

  3. Table.Join (https://learn.microsoft.com/ja-jp/powerquery-m/table-join) ↩

  4. JoinAlgorithm.Type (https://learn.microsoft.com/ja-jp/powerquery-m/joinalgorithm-type) ↩

  5. 左外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-left-outer) ↩

  6. 右外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-right-outer) ↩

  7. 完全外部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-full-outer) ↩

  8. 内部結合 (https://learn.microsoft.com/ja-jp/power-query/merge-queries-inner) ↩

  9. Left Anti Join (https://learn.microsoft.com/ja-jp/power-query/merge-queries-left-anti) ↩

  10. Right Anti Join (https://learn.microsoft.com/ja-jp/power-query/merge-queries-right-anti) ↩

  11. クロス結合 (https://learn.microsoft.com/ja-jp/power-query/cross-join) ↩

17
18
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
17
18