はじめに
PowerQuery初心者です。
単純な、複数列の同値判定で色々な方法を試したため、備忘録として記事を投稿します。
前提
<システム環境>
- Windows OS
- Microsoft Excel 2019
本記事ではMicrosoft 365版のExcelを使用します。
Excel for Macは、PowerQueryの機能に制限があるため使用しません。
使用するデータと目的
下記の表を使用します。
| A | B | C |
|---|---|---|
| a | b | c |
| a | a | A |
| A | A | A |
ヘッダを除く1〜2行目がFALSE、3行目がTRUEとなる比較列を追加します。
まずは数式と関数
PowerQueryを使う前に、まずは数式と関数で比較列を追加する例を見ます。
同値判定に比較演算子(=)を使用するとアルファベットの大文字小文字が区別されないため、ここではEXACT関数1を使用して判定しています。
IFネスト
IFネストで作成する場合。
D2の数式 = IF(EXACT(A2,B2),IF(EXACT(A2,C2), "TRUE", "FALSE"), FALSE)
配列数式
配列数式(CSE)で作成する場合。2
D2の数式 = IF(AND(EXACT(A2:C2,A2)), TRUE, FALSE)
* 数式はCtrl + Shift + Enterキーで確定操作する。式が{}で囲まれる。
スピル 3
Microsoft 365のExcelに実装されたスピルを使う場合。
D2の数式のみ = IF(EXACT(A2:A4,B2:B4),IF(EXACT(A2:A4,C2:C4), "TRUE", "FALSE"), FALSE)
配列数式のように短縮できそうですが、試した範囲では実現できませんでした。
構造化参照
表をテーブルへ変換し、構造化参照4を使用する場合。
テーブルの名前に「テーブル1」を付けています。
D2の数式 =IF(AND(EXACT(テーブル1[@[A]:[C]],[@A])),TRUE,FALSE)
PowerQuery
比較列をPowerQueryで作成してみます。
クエリ作成
表をテーブルへ変換して、テーブルの名前に「テーブル2」を付けます。
テーブルを選択した状態で「データの取得」→「その他のデータソースから」→「テーブルまたは範囲から」を実行し、クエリを作成します。
データはテキストのみのため、テキスト型としてPowerQueryに認識されています。
もし数値も含まれていた場合は、any型として認識されます。
「列の追加」→「カスタム列」からカスタム列を新規に追加し、IFネストで式を作成します。
if [A]=[B] then
if [A]=[C] then
true
else
false
else
false
結果は画像の通り、アルファベットの大文字小文字が別個の値として認識されています。
記号=は、PowerQueryのM言語5では等値演算子6として定義されています。
余談ですが、「カスタム列」ではなく「条件列」の作成でも同様の結果を得られます。
クエリの改良
IFネストは、比較対象の列が増えるとif-expressionも増えるため、改良の余地があります。
EXACT関数の範囲指定のように短縮ができれば良いのですが、探した範囲では見当たりませんでした。
このため、関数とListを使って作成します。
カスタム列を新規に追加し、下記式を作成します。
((s) => List.MatchesAll({[A],[B],[C]}, each _ = s))([A])
範囲指定は出来ませんが、データの列が増えた場合はListである{}内の項目を増やすだけで対応できます。
コメントより
本記事のコメント欄に下記の方法をご紹介いただきました。
・Record.ToListを使って全ての列を指定する。
他にも、良い方法があればシェア頂ければありがたいです。
おわりに
バージョンを重ねるに連れExcelの機能も増えており、単純な列の同値判定だけでも、過去より書き方のバリエーションが増えていました。
VBAやカスタム関数を含めると更にバリエーションが増えそうですが、PowerQueryで複数列の同値判定はこの方法だと言うものが、見つかればいいなと思っております。
参考
-
https://nyanto.jimdofree.com/%EF%BD%B4%EF%BD%B8%EF%BD%BE%EF%BE%99%E9%96%A2%E6%95%B0-%E5%95%8F%E9%A1%8C%E8%A7%A3%E6%B1%BA/if%E9%96%A2%E6%95%B0%E3%81%AE%E8%AB%96%E7%90%86%E5%BC%8F%E3%81%A7%E8%A4%87%E6%95%B0%E5%88%97%E5%85%A8%E3%81%A6%E3%81%8C%E4%B8%80%E8%87%B4%E3%81%AE%E6%9D%A1%E4%BB%B6%E3%82%92%E7%B0%A1%E5%8D%98%E3%81%AB%E6%8C%87%E5%AE%9A%E3%81%99%E3%82%8B%E3%83%86%E3%82%AF%E3%83%8B%E3%83%83%E3%82%AF/ ↩
-
https://support.microsoft.com/ja-jp/office/%E5%8B%95%E7%9A%84%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%A8%E3%80%81%E3%81%93%E3%81%BC%E3%82%8C%E3%81%9F%E9%85%8D%E5%88%97%E5%8B%95%E4%BD%9C-205c6b06-03ba-4151-89a1-87a7eb36e531 ↩
-
https://support.microsoft.com/ja-jp/office/excel-%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%A7%E3%81%AE%E6%A7%8B%E9%80%A0%E5%8C%96%E5%8F%82%E7%85%A7%E3%81%AE%E4%BD%BF%E3%81%84%E6%96%B9-f5ed2452-2337-4f71-bed3-c8ae6d2b276e ↩
-
Power Query Formula Languageが正式名称です。https://qiita.com/spumoni/items/9b89555dfd8b550c177f ↩
-
https://docs.microsoft.com/ja-jp/powerquery-m/m-spec-operators#equality-operators ↩