Lost your password?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (18)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (66)
Разное (42)
Баги и глюки Excel (4)

Фильтр без учета регистра в Power Query


Скачать файл с исходными данными, используемый в видеоуроке:

  Фильтр без учета регистра в PowerQwery.xlsx (40,0 KiB, 11 скачиваний)


Представим ситуацию, когда в нашей таблице данные хоть и упорядочены и организованы, но не имеют четкого написания в части регистра:
Исходные данные
И если необходимо отобрать данные по конкретному поставщику(например, ИП Иваныч)
Окно фильтра Power Query
мы получим исключительно те строки, которые в точности равны заданной строке, вплоть до каждой буковки и её регистра. Но у нас же есть и другие строки, которые относятся точно к тому же поставщику, но записаны маленькими(строчными) буквами. В окне фильтрации никаких опций для игнорирования регистра нет. Равно как нет этого и в настройках запроса. С одной стороны мы можем просто выбрать все варианты из выпадающего списка фильтра:
Отбор в фильтре
но что, если нам надо применить условие И/ИЛИ? Или вариантов написания слишком много и все выбирать неудобно или текст может быть более длинным и просто не помещаться в окне условий?
По сути решение очевидно и напрашивается само собой: привести к единому регистру все значения в столбце и после этого отфильтровать.
Конечно, можно сделать все при помощи мышки – создаем дубликат столбца «Поставщик»(правая кнопка мыши – Создать дубликат столбца(Duplicated Column)), переводим его в нижний регистр: правая кнопка мыши на столбце -Преобразование(Transform) -нижний регистр(Lower). Фильтруем по этому столбцу нужные данные и удаляем этот столбец.
Но лично для меня проще в строке формул привести к единому регистру сравниваемые данные. Т.е. выделяем в навигаторе шаг фильтрации -переходим в строку формул
Приводим к нижнему регистру в строке формул
и вместо выражения

= Table.SelectRows(Source, each [Поставщик] = "ИП Иваныч")

Записываем такое:

= Table.SelectRows(Source, each Text.Lower([Поставщик]) = Text.Lower("ИП Иваныч"))

Функция Text.Lower приводит к нижнему регистру текст, который в ней записан. Таким образом мы приводим к нижнему регистру и данные в фильтруемом столбце и текст условия(хотя текст условия по сути можно просто руками прописать в нижнем регистре). В итоге получаем необходимый результат. Тоже самое касается условия «не равно». К слову, можно вместо Text.Lower использовать Text.Upper – это перевод в верхний регистр. Никакой разницы, просто кому как больше нравится.
Но что делать, если нам надо отобрать по условию: начинается с, заканчивается на, содержит и т.п.? В данном случае есть два варианта.
Способ 1
Самый очевидный – так же как в случае с равенством просто приводим к единому регистру условие и данные. Например, мы выбрали условие «Начинается с». Строка будет выглядеть изначально так:

= Table.SelectRows(Source, each Text.StartsWith( [Поставщик], "ИП Иваныч"))

А нам необходимо подправить её на такую:

= Table.SelectRows(Source, each Text.StartsWith(Text.Lower([Поставщик]), Text.Lower("ИП Иваныч")))

В случае с условием «Содержит» правим исходную строку

= Table.SelectRows(Source, each Text.Contains( [Поставщик], "ИП Иваныч"))

На эту:

= Table.SelectRows(Source, each Text.Contains(Text.Lower([Поставщик]), Text.Lower("ИП Иваныч")))

Т.е. просто так же добавляем функцию Text.Lower. Думаю, принцип понятен.

Способ 2
Но во всех этих функциях(Text.StartsWith, Text.EndsWith, Text.Contains) есть очень приятный бонус – последний аргумент Comparer. По умолчанию он не указывается и равен значению Comparer.Ordinal, что означает сравнивать как есть с учетом регистра. Но если мы укажем для этого параметра принудительно значение Comparer.OrdinalIgnoreCase – это будет означать игнорирование регистра и остальные преобразования уже будут лишними.
В итоге наши исходные строки были такие:

= Table.SelectRows(Source, each Text.StartsWith( [Поставщик], "ИП Иваныч"))
= Table.SelectRows(Source, each Text.Contains( [Поставщик], "ИП Иваныч"))

А мы запишем их так:

= Table.SelectRows(Source, each Text.StartsWith( [Поставщик], "ИП Иваныч", Comparer.OrdinalIgnoreCase))
= Table.SelectRows(Source, each Text.Contains( [Поставщик], "ИП Иваныч", Comparer.OrdinalIgnoreCase))

Но тут есть нюанс: как показала практика этот параметр может быть проигнорирован в некоторых версиях Power Query. Причину пока не понял, но упомянуть об этом точно стоит 😊 Так что если фокус с Comparer.OrdinalIgnoreCase не взлетит – используйте топорный, но 100%-ый вариант с приведением к единому регистру через Text.Lower.

Скачать файл с готовым запросом:

  Фильтр без учета регистра в PowerQwery.xlsx (40,0 KiB, 11 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2021 Excel для всех   Войти