Power BIであいまい一致を使用してクエリをマージする

去年12月のアップデートで実装されたFuzzy merge – support for Top N best matches。
日本語UIだと「あいまい一致」または「あいまい結合」になります。

猫型ロボットのひみつ道具一覧を作ってたら使う機会に恵まれたので、分かった所を書いていきます。

あいまい一致を使用してクエリをマージするとは

Microsoft Docsに詳細な解説が…無い。
2019/3/8現在、なんと日本語は愚か英語ですら公式リファレンスがありません。

仕方ないので非公式リファレンスを読んでみましょう。

なになに、「指定された列のテーブル間であいまい結合を実行し、結合結果を新しい列に生成します。」
どういうことだってばよ…

自分なりに噛み砕くと、「テーブルをマージする際に、照合列が完全一致でなく類似性によって評価される機能」です。
すまねぇ、ロシア語はサッパリなんだ

何ができるのか

あいまい一致とは似た者同士をくっ付ける機能であると言えます。

今回は猫型ロボットのひみつ道具一覧を作る過程で、テレ朝とWikipediaのひみつ道具一覧をマージするのに使っています。

二つの一覧は同じひみつ道具を扱ってはいますが、別個に作成されているので編述ルールは統一されていません。
その為、テーブルをマージするには多くの例外を処理する必要がありました。

主な例外パターンは以下の通りです。

例外パターン	テレ朝名称	Wiki名称
振り仮名が付いている	Ｅ・Ｓ・Ｐ（エスパー）訓練ボックス	E・S・P訓練ボックス
スペースの有無	異説クラブメンバーズ　バッジとマイク	異説クラブメンバーズバッジとマイク
全角と半角	Ｎ・Ｓワッペン	N・Sワッペン
何か足りない	国際保護動物スプレー	国際保護スプレー

これら例外パターンを全て把握し、例外処理するのは現実的ではありません。
しかし同じ物を扱っている一覧であれば、中のレコードも類似性が高い事は容易に想像がつきます。

であれば、ひみつ道具名称の類似している部分を探し、類似性が設定した閾値を超えたら一致として扱えばいい訳です。

これを上手く使うと例外処理の手間を大幅に削減する事が出来ます。

本題を話す前に:予備知識

あいまい一致の説明の前に予備知識として、Power Queryにおけるクエリのマージについて少し説明します。

通常のクエリのマージ

Power Queryにおけるクエリのマージは、特に設定を行わなければ完全一致のロジックで動作します。

完全一致

完全一致ではキーとなる列の値が、文字数から何から何まで全て一致しなければ同一の値として扱われません。

完全一致でありがちなハマりポイント

漏れがあるかもしれませんが、ハマりやすい一致しない条件は以下の通りです。

一致しない理由	表A	表B
大文字と小文字	Yellow	yellow
1バイト文字と2バイト文字	Y	Ｙ
前か後にスペースがある場合	Yellow	Yellow

今回は割愛しますが、Power Queryにはこれらハマりポイントをクレンシングするのに便利な機能が色々とあるので探してみてください。

あいまい一致を使用してクエリをマージする

ここからはあいまい一致を利用する方法を説明します。

あいまい一致を利用する

あいまい一致は、Power Query上で[クエリのマージ]内で利用できます。

まずダイヤログ上で[あいまい一致を使用してマージを実行する]のチェックボックスを有効にします。

あいまい結合オプションの説明

あいまい結合で利用できるオプションは以下の通りです。

• 類似性のしきい値(省略可能)
  • 二つの列の値が一致として見なされるために必要な類似性を設定します。
  • 詳細は後述
• 大文字と小文字を区別しない
  • 有効にするとYellowとyellowを一致として返します。
• テキストの一部を結合して一致させます
  • 有効にするとPower BIとPowerBIを一致として返します。
• 一致の最大数(省略可能)
  • 一致として返された値を類似性が高い順に最大何個返すかを設定します。
• 変換テーブル(省略可能)
  • カスタムマッピングに利用する変換テーブルを指定します。
  • 詳細は後述

類似性のしきい値(省略可能)の詳細

しきい値の最大値	しきい値の最小値	既定値
1.00	0.00	0.80

1.00では完全一致で動作します。
0.00では無条件で全てが一致と返され、CROSS JOINの動きをします。

0.90未満で文字数の違いによる類似を結果に含みます。
つまり0.90未満に設定されている場合のみ、YellowはYelowと一致します。

変換テーブル(省略可能)の詳細

[From]列と[To]列で作られた変換テーブルを読み込ませることで、特定の値にカスタム値をマッピングできます。

変換テーブルを使わない場合

以下の元テーブルと判定テーブルをあいまい一致で、変換テーブルを使わずにマージします。

結果は一行のみ一致として判定されました。

変換テーブルを使う場合

次に同じ元テーブルと判定テーブルを以下の変換テーブルを使ってマージします。

利用する変換テーブルをプルダウンで選択します。

結果は変換テーブルでマッピングされた通り、全てが江戸川コナンに一致として判定されました。

事前に変換テーブルを用意することで、略語や類似語などを特定の語句に関連付けることが出来るでしょう。

利用上の注意

正確性や完全一致が求められる業務には絶対に使用しないで下さい。

Fuzzyな処理はデータの正確さや健全性を大きく損ないます。
そもそも元から綺麗なデータを作っていれば、Fuzzyな処理なんてする必要がない

今回のひみつ道具一覧の場合は、一致に誤りがあっても特に実害が無いので使っています。
あったとしても、一覧からWikipediaの似た名前の道具ページに飛ばされるだけです。

まとめ

• あいまい一致は似た者同士をくっ付ける機能。
  • しかも自分である程度ルールをコントロールできる。
• 完全一致を理解してから使おう。
  • Power Queryにおける一致の条件はExcelと違うので注意が必要です。
• 業務に限らず、よく用途を考えてから使う。
  • 「便利」である事と「効果的」である事は違います。

因みに筆者は冒頭で書いた猫型ロボットひみつ道具一覧を作っている最中です。
レポート画面が何時完成する事やら…(´・ω・｀)

使っているひみつ道具一覧データはこちらです。
テレビ朝日
Wikipedia
皆さんも自分の好きなジャンルで是非チャレンジしてみてください。