Функции List.* и аргумент equationCriteria | Блог NeedForData про Power BI и Excel для интернет-маркетинга

Функции List.* и аргумент equationCriteria

Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Уэбб),
оригинал – The List.* M Functions And The equationCriteria Argument

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

Функции List.*M и аргумент equationCriteria

Недавно в комментариях к нашему блогу был задан вопрос о назначении третьего аргумента в функции List.Contains(). Мы провели небольшое исследование и поняли, что множество функций List.* имеют такой же аргумент. В документации к функции List.Distinct() приведено несколько примеров, но нет разъяснений по нашему вопросу. Там написано:

Подробную информацию по equationCriteria смотрите в Parameter Values.

…но ссылка на раздел Parameter Values отсутствует. Немного больше можно найти в конце официальной страницы.

Equation criteria
Аргумент equationCriteria для значений списка указывается как

– Значение функции, которое:
– Является ключом-селектором для списка значений при сравнении
– Определяет функцию компаратор. Список доступных функций смотрите в разделе Comparer functions

– Значение списка, отвечающее требованиям:
– Строго два значения
– Первый элемент это ключ селектор
– Второй элемент это функция компаратор

Не слишком помогает, не так ли? Надеемся, что наша статья окажется полезной в данном вопросе. Рассмотрим следующие примеры…

Основы

Начнём с основ. Следующее выражение, использующее функцию List.Contains(), вернёт TRUE (истина), так как текстовое значение apples присутствует в списке {"apples", "oranges", "pears"}:


List.Contains({"apples", "oranges", "pears"}, "apples")

Следующее выражение вернёт FALSE (ложь), так как текстовое значение grapes отсутствует в списке {"apples", "oranges", "pears"}:


List.Contains({"apples", "oranges", "pears"}, "grapes")

Однако есть множество способов, которыми можно сравнить текстовые значения. Аргумент equationCriteria определяет, какие правила применяются.

Чувствительность к регистру и язык

Код языка M учитывает регистр. В результате, здесь мы получим FALSE:


List.Contains({"apples", "oranges", "pears"}, "Apples")

Что делать, если нужна нечувствительность к регистру? На помощь приходит функция Comparer. Comparer.FromCulture() возвращает функцию, которая сравнивает значения в соответствии с правилами языка и региональных стандартов.
Чувствительность к регистру устанавливается по выбору. В этом примере мы получим TRUE:


List.Contains(
    {"apples", "oranges", "pears"}, 
    "Apples", 
    Comparer.FromCulture("en-GB", true)

Здесь Comparer.FromCulture("en-GB", true) возвращает функцию, сравнивающую два значения в соответствии с региональными правилами Великобритании (полный список регионов можно посмотреть в колонке Language Tag таблицы). Второй необязательный аргумент определяет нечувствительность к регистру. Функция, которую возвращает Comparer.FromCulture() используется в List.Contains() для сравнения.

Можно не указывать вручную первый аргумент, а использовать функцию Culture.Current. Она вернёт текущий регион. В нашем случае, это мы получаем значение en-GB:

Следующий пример показывает как применять Culture.Current для Comparer.FromCulture. На нашем компьютере результат TRUE:


List.Contains(
    {"apples", "oranges", "pears"}, 
    "Apples", 
    Comparer.FromCulture(
        Culture.Current, 
        true
    )
)

Если вам интересно, как различные региональные настройки влияют на результат сравнения, то вот один пример. Вероятно, в английском языке символ æ трактуется как комбинация символов a и e, чего не происходит в датском. На выходе получаем TRUE:


List.Contains(
    {"aepples", "oranges", "pears"}, 
    "æpples", 
    Comparer.FromCulture(
        "en-GB", 
        true
    )
)

Хотя здесь получаем FALSE:


List.Contains(
    {"aepples", "oranges", "pears"}, 
    "æpples", 
    Comparer.FromCulture(
        "da-DK", 
        true
    )
)

Порядковое сравнение

Если вы не хотите зависеть от языка, региона, можно использовать порядковое сравнение. Функция находит юникод для каждого символа двух строк и сравнивает их. Для этого можно использовать функцию Comparer.Ordinal(). Получаем FALSE:


List.Contains(
    {"apples", "oranges", "pears"}, 
    "Apples", 
    Comparer.Ordinal
)

…потому что юникод символа a и символа A различаются, как следствие apples и Apples не равны.

Пользовательские функции сравнения

С помощью подсказок документации можно написать свою функцию сравнения. Функция компаратор должна иметь два аргумента и возвращает логическое значение. В примере ниже функция принимает два текстовых значения – х и у. И возвращает истину, если три первых символа у них совпадают:


(x as text, y as text)=>Text.Start(x,3)=y

Такую функцию можно использовать в List.Contains(), например, следующий пример вернёт TRUE:


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

Данный код работает следующим образом: функция вызывается три раза, так как в списке три значения. Каждый раз в аргумент х передаётся соответствующее значение {"apples", "oranges","pears"}. На каждый вызов в аргумент у передаётся значение "app". Три первых символа "apples" совпадают с "app", и List.Contains() возвращает истина.

Ключ селекторы

Если вы работаете со списком записей, может понадобиться сравнение только по одному полю. В этом нам помогут ключ селекторы. В данном примере получаем TRUE:


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

…так происходит, потому что сравнивается поле Fruit (фрукт) каждой записи. И в записи [Fruit="apples", Colour="Red"] оно совпадает с таким же полем записи [Fruit="apples", Colour="Russet"]. А вот этот пример вернёт FALSE:


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

…потому что в поле Colour нет повторов "Russet".

Объединение ключ селекторов и функций сравнения

Наконец, как свидетельствует документация, можно объединить два вышеописанных метода. Для чего надо передать список в аргумент equationCriteria: первым элементом списка должен быть ключ селектор, вторым – функция компаратор. Здесь получим TRUE:


List.Contains(
    {[Fruit="apples", Colour="Red"], 
         [Fruit="oranges", Colour="Orange"],
         [Fruit="pears", Colour="Green"]}, 
    [Fruit="Apples", Colour="Russet"],
        {each [Fruit], Comparer.FromCulture("en-GB", true)} 
)

…потому, что просматриваем каждое поле по правилам региона en-GB и независимо от регистра. А в этом случае "apples" и "Apples" равны.