Регистро НЕ чувствительность в Power Query (и Power BI)
Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.
Её синтаксис прост:
=Comparer.OrdinalIgnoreCase(Текст1, Текст2)
Она сравнивает два текста, заданные в качестве аргументов, причём делает это без учёта регистра. Если тексты равны друг другу (одинаковы), то функция выдаёт ноль. Если не равны, то выдаёт 1 или -1 в зависимости от того, какой текст "больше" (с точки зрения кодов символов, т.е. расположения букв в алфавите) - первый или второй.
Нас, как легко сообразить, интересует именно первый случай, т.е. ноль в качестве желаемого результата сравнения. Давайте рассмотрим несколько примеров использования этой функции в типичных рабочих задачах Power Query.
Пример 1. Фильтр по значению без учёта регистра
Предположим, что у нас есть вот такая загруженная в редактор Power Query таблица, в которой мы хотим отфильтровать все джемперы Lacoste вне зависимости от их регистра:
Для начала выполним простую фильтрацию по любому из вариантов написания. Получим в строке формул следующую конструкцию:
Обратите внимание на выражение в скобках после слова each - это, по факту, и есть условие, которое проверяется для каждого (each!) значения из столбца Товар, чтобы решить - фильтровать эту строку её или нет.
Заменим это выражение на нашу функцию регистронечувствительного сравнения Comparer.OrdinalIgnoreCase. Если она возвращает 0, то очередное проверяемое значение равно (без учета регистра) искомому джемперу Lacost, то мы хотим показать эту строку в результатах фильтрации:
Получаем в результате наши джемперы Lacoste в любом варианте регистра. Всех делов :)
Пример 2. Фильтр по частичному совпадению без учёта регистра
Интересный и не совсем очевидный момент заключается в том, что иногда нашу функцию Comparer.OrdinalIgnoreCase можно использовать без аргументов и даже без скобок - в качестве дополнительного необязательного аргумента для других функций в Power Query.
Предположим, что в той же таблице мы хотим отфильтровать все строки, где название товара содержит слово "джинсы". Как и в прошлый раз, давайте сначала отфильтруем джинсы привычным образом, используя опцию Текстовые фильтры - Содержит (Text filters - Contains):
Получаем в строке формул конструкцию с уже знакомой функцией Table.SelectRows:
Здесь, как легко сообразить, условием на отбор строк будет результат М-функции Text.Contains, проверяющей содержится ли в очередном (each) названии товара слово "джинсы".
Фишка в том, что у функции Text.Contains, на самом деле, не два, а три аргумента - третий (необязательный) отвечает за метод сравнения и тут как раз можно указать нашу функцию (без аргументов и даже без скобок):
Совершенно аналогично, кстати, можно реализовать регистроНЕчувствительность и при фильтрации текста в режиме "начинается с", "заканчивается на" и т.п. - просто дописываем нашу функцию третьим аргументом и всё.
Пример 3. Удаление дубликатов без учёта регистра
Ещё одна классическая задача, где мы можем столкнуться со сложностями из-за регистра - это поиск и удаление дубликатов.
Предположим, что всё в той же таблице мы хотим оставить только уникальный набор покупателей. Сначала делаем это обычным образом - щёлкнув по шапке столбца Покупатель, выбираем команду Удалить дубликаты (Remove Duplicates). Получаем покупателей в разном регистре и в строке формул выражение с функцией Table.Distinct, которая в языке М отвечает за удаление повторов:
Обратите внимание, что вторым аргументом функции Table.Distinct указывается список (в фигурных скобках через запятую) имён столбцов, по которым идёт проверка уникальности.
Неочевидный нюанс в том, что для каждого столбца в этом списке можно задать свою функцию сравнения, которая должна использоваться при обнаружении дубликатов - она указывается через запятую непосредственно после имени столбца, что легко решает нашу задачу:
Если вы удаляете дубликаты не по одному, а сразу по нескольким столбцам (например по связке столбцов Товар - Покупатель, выделив их предварительно с Ctrl), то для каждого столбца функцию нужно прописать отдельно, заключив дополнительно каждую пару "столбец-функция" в фигурные скобки:
Пример 4. Объединение (merge) таблиц без учёта регистра
Ну и, наконец, давайте разберём ещё один типовой случай - объединение таблиц без учёта регистра. Тут всё проще - не нужно дописывать вручную никаких функций, а просто использовать нечёткий текстовый поиск, о котором я уже делал отдельную статью и видео с подробным разбором.
Если, например, нам нужно подтянуть к той же самой таблице статусы покупателей из отдельного справочника (уже загруженного в Power Query заранее), то используем команду Главная - Объединить (Home - Merge). В открывшемся окне внизу выбираем таблицу-справочник, выделяем пару столбцов для связи в обеих таблицах, а затем включаем флаг Использовать нечеткие соответствия для слияния (Use fuzzy matching):
В параметрах нечёткого соответствия задаём Порог подобия = 1 (чтобы поиск был точным и на "Иван" у нас не нашёлся, например, "Иванов" или "Иванович") и включаем флажок Игнорировать регистр (Ignore case), ради которого тут всё и задумано.
Если в вашей версии Excel нет галочки Использовать нечеткие соответствия для слияния, то можно попробовать создать новый шаг (нажать кнопку fx в строке формул) и вписать туда нужную функцию вручную - возможно, что в вашу версию Excel она уже добавлена, хотя галочки в окне слияния ещё нет:
Здесь первый аргумент - таблица (обычно с предыдущего шага), а затем идут две пары "имя столбца - таблица" (куда и откуда подтягиваем данные). Последний аргумент - это запись (record) в квадратных скобках, описывающая все параметры слияния.
Вот и все премудрости. Надеюсь, что теперь регистрочувствительность Power Query не будет составлять для вас большой проблемы - её можно обойти, если нужно, не создавая при этом лишних шагов.
Ссылки по теме
- Нечёткий текстовый поиск в Power Query
- Поиск точных совпадений с учётом регистра
- Поиск ключевых слов в тексте
Как применить данную функцию при группировке?
Появился вопрос: можно ли этот приём объединить с другой очень полезной функцией, которую вы также замечательно преподали вот - массовой замене текста функциями List.Accumulate + Text.Replace: чтобы замена осуществлялась без учета регистра в исходных данных и столбце "Найти" справочника? Видел, вам уже задавали подобный вопрос, но ответа, кажется, не было. Сам пробовал, но получаю ошибку "Не удается преобразовать значение типа List в тип Text", скорее всего что-то делаю не так. Заранее благодарю за помощь!