(Power BI) List.Containsの引数 equationCriteria を使う

※PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #13(2022.04.23)で使用した記事です。

List.Containsの引数にはequationCriteriaと言うものがありますが、使い方がわからなかったので調べてみました。

※ネタ元 The List.* M Functions And The equationCriteria Argument, Chris Webb, Jan 22 2017, CHRIS WEBB's BIBLOG

1.構文

List.Contains構文

= List.Contains(
    list as list,    // リスト
    value as any,    // 値
    optional equationCriteria as any // オプション　比較関数
) as logical 

2.通常（equationCriteriaを使わない方法）

　List.Containsは、リストlistに値valueが含まれているかどうかをtrueあるいはfalseで返します。

= List.Contains(
    {"apples", "oranges", "pears"},
    "apples"
  )  // return true

= List.Contains(
    {"apples", "oranges", "pears"},
    "graoes"
  )  // return false

　通常は、以下の区分で照合が行われます。

• CS (ケース・センシティブ Case-Sensitive) 大文字と小文字の区別
• AS (アクセント・センシティブ Accent-Sensitive) "a"と"á"、"ば"と"ぱ"の区別など
• KS (カナ・センシティブ Kana-Sensitive) ひらがなとカタカナの区別
• WS (幅センシティブ Width-Sensitive) 半角と全角の区別

　この区分が行われない場合はSensitiveがInsensitiveとなり、CI、AI、KI、WIと表記されます。

3.大文字小文字の区別をしない方法 (CI)

　equationCriteriaに比較関数を使うと、大文字小文字の区別や合字(ligature)の区別をしないようにできます。

Comparer.FromCultureを使う

aとAは区別される

= List.Contains(
    {"apples", "oranges", "pears"},
    "Apples"
  )  // return false

Comparer.FromCultureを使ってaとAを区別しない

= List.Contains(
    {"apples", "oranges", "pears"},
    "Apples",
    Comparer.FromCulture(
        "ja-JP", // 日本の言語コード
        true     // ignoreCase
    )
  )  // return true

aeとÆを区別しない

= List.Contains(
    {"aepples", "oranges", "pears"},
    "Æpples",
    Comparer.FromCulture(
        "ja-JP",
        true
    )
  )  // return true

　"Æ"はイオンの社名にも使われているラテン語の文字ですが、日本のカルチャーでも同一として評価されます。"da-DK"（デンマーク語）にするとfalseとなります。

Comparer.FromCulture構文

= Comparer.FromCulture(
    culture as text,                         // 言語コード
    optional ignoreCase as nullable logical  // 大文字小文字を区別するか
) as function

　使用可能な言語コードについては、以下を参照してください。

Comparer.OrdinalIgnoreCaseを使う

　この場合、言語コードは使いません。
　

aとAを区別しない

= List.Contains(
    {"apples", "oranges", "pears"},
    "Apples",
    Comparer.OrdinalIgnoreCase  // 大文字小文字を区別しない比較関数を返す
  )  // return true

aeとÆは区別される

= List.Contains(
    {"aepples", "oranges", "pears"},
    "Æpples",
    Comparer.OrdinalIgnoreCase
  )  // return false

4.厳密な比較

　cultureによるみなし一致を望まない場合は、Comparer.Ordinal関数を使って厳密に比較を行います。

Comparer.Ordinal

= List.Contains(
    {"apples", "oranges", "pears"},
    "Apples",
    Comparer.Ordinal
  )  // return false

5.独自の比較関数を使う

引数が２つの比較のための関数を作成することができます。

頭３文字で比較

= List.Contains(
    {"apples", "oranges", "pears"}, 
    "app",
    (x as text, y as text) => Text.Start(x,3) = y
) // return true

Text.Containsを使って部分一致

= List.Contains(
    {"apples", "oranges", "pears"}, 
    "pp",
    (x as text, y as text) => Text.Contains(x, y)
) // return true

　部分一致で一致した値を取得するにはList.FindTextを使います。

List.FindText

List.FindText(
    {
        "a", 
        "b", 
        "ab"
    }, 
    "a"
) // return {"a", "ab"}

全角文字を半角に変換して比較する

　全角文字を半角に変換して一致を見たい場合、対応表のリストを作成して変換する関数を作成します。この関数についての詳細は、以下をご覧ください。

@PowerBIxyz「Power Query で 全角数字 から 半角数字 に変換するには」

全角半角変換関数

(originalText as text) as text =>
let
    combination = List.Zip(
        {
            List.Combine( { { Character.FromNumber(0x3000) }, { "！".."～" } } ),
            List.Combine( { { Character.FromNumber(0x20) }, { "!".."~" } } )
        }
    ),
    textList = List.ReplaceMatchingItems(
        Text.ToList(originalText),
        combination
    )
in
    Text.Combine(textList)

　List.Containsは、以下のように書きます。

= List.Contains(
    {"apples", "oranges", "pears"}, 
    "ａｐｐｌｅｓ",
    (x as text, y as text) => x = 全角半角変換関数(y)
) // return true

6.キーセレクター

　リストの中がレコードになっている場合、レコード全体の一致を調べます。

Fruitだけでは一致しない

= List.Contains(
        {
            [Fruit="apples",  Colour="Red"], 
            [Fruit="oranges", Colour="Orange"],
            [Fruit="pears",   Colour="Green"]
        }, 
        [Fruit="apples"]
    ) // return false

　比較の対象とする項目をequationCriteriaで示すことができます。

Fruitだけで比較

= List.Contains(
        {
            [Fruit="apples", Colour="Red"], 
            [Fruit="oranges", Colour="Orange"],
            [Fruit="pears", Colour="Green"]
        }, 
        [Fruit="apples"],
        each [Fruit]
    ) // return true

Fruitだけで比較し、大文字小文字の区別もしない

= List.Contains(
        {
            [Fruit="apples", Colour="Red"], 
            [Fruit="oranges", Colour="Orange"],
            [Fruit="pears", Colour="Green"]
        }, 
        [Fruit="Apples"],
        {
            each [Fruit], 
            Comparer.OrdinalIgnoreCase
        }
    ) // return false

7.equationCriteriaを使うList関数

　この引数を使う関数は、以下のものがあります。

関数名	構文
List.Distinct	List.Distinct(list as list, optional equationCriteria as any) as list
List.IsDistinct	List.IsDistinct(list as list, optional equationCriteria as any) as logical
List.RemoveMatchingItems	List.RemoveMatchingItems(list1 as list, list2 as list, optional equationCriteria as any) as list
List.ReplaceMatchingItems	List.ReplaceMatchingItems(list as list, replacements as list, optional equationCriteria as any) as list
List.ContainsAll	List.ContainsAll(list as list, values as list, optional equationCriteria as any) as logical
List.ContainsAny	List.ContainsAny(list as list, values as list, optional equationCriteria as any) as logical
List.PositionOf	List.PositionOf(list as list, value as any, optional occurrence as nullable number, optional equationCriteria as any) as any
List.PositionOfAny	List.PositionOfAny(list as list, values as list, optional occurrence as nullable number, optional equationCriteria as any) as any
List.Difference	List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list
List.Intersect	List.Intersect(lists as list, optional equationCriteria as any) as list
List.Union	List.Union(lists as list, optional equationCriteria as any) as list
List.Mode	List.Mode(list as list, optional equationCriteria as any) as any
List.Modes	List.Modes(list as list, optional equationCriteria as any) as list

おまけ　comparisonCriteria

　List.Sortでは、比較のための引数comparisonCriteriaを使うことができます。

関数名	構文
List.Max	List.Max(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any
List.MaxN	List.MaxN(list as list, countOrCondition as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as list
List.Median	List.Median(list as list, optional comparisonCriteria as any) as any
List.Min	List.Min(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any
List.MinN	List.MinN(list as list, countOrCondition as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as list
List.Sort	List.Sort(list as list, optional comparisonCriteria as any) as list