Привет, коллега! На связи Ленивый аналитик, а значит, мы снова говорим о том, как облегчить нашу excel-ную рутину. Конечно же, в Power Query.
И сегодня обсудим фильтрацию в Power Query - поделюсь своим приемом, как быстрее фильтровать данные.
Фильтруем данные в Power Query "как обычно"
Чтобы отфильтровать данные редакторе Power Query при помощи интерфейса (без кода):
- щелкаем на значок выпадающего списка в заголовке столбца
- выбираем нужные значения
- нажимаем Ок
Если нужно отфильтровать несколько столбцов, соответственно, проделываем эту операцию с каждым столбцом.
Чем это может быть неудобно
Такой способ фильтрации в Power Query вполне подходит, если фильтры не будут меняться.
Предположим, в данном отчете нам нужны данные только по отмененным заказам. В этом случае, мы можем спокойно фильтровать внутри интерфейса стандартным способом.
А что если фильтры будут динамическими?
Самый жизненный пример из практики - фильтрация данных только по текущему месяцу.
Если фильтровать "как обычно" через интерфейс (без написания кода М), то при смене месяца нужно каждый раз заходить в редактор Power Query и менять фильтр.
А это чревато тем, что:
- тратим лишнее время на лишнюю операцию, тем более Power Query не всегда отрабатывает быстро, особенно в сложных запросах.
- рискуем забыть поменять фильтр - все мы человеки, даже самые ответственные.
Делаем управляемые фильтры в Power Query
На самом деле, в Power Query можно фильтровать... не заходя в Power Query (ну, один раз придется зайти, чтобы настроить 😀)
Для этого нужно настроить панель управления фильтрами.
Звучит как-то сложно, а по факту это обычные таблицы, которые:
- отдельными запросами загружаются в Power Query
- соединяются с основной таблицей, отрезая в ней только те данные, которые указаны в этих вспомогательных таблицах-фильтрах
Рассмотрим на примере.
Создадим две таблицы:
- фильтр по месяцу
- фильтр по статусу заказа
Загрузим обе фильтровочные таблицы в Power Query.
Чтобы отфильтровать нашу основную таблицу, переходим в нее:
Вкладка Главная - Объединить запросы
В окне Слияние:
1. Выбираем первую таблицу-фильтр
2. Отмечаем ключевой столбец в каждой таблице: в основной таблице это столбец, который совпадает со столбцом в таблице-фильтре.
3. Выбираем Тип соединения: Внутреннее (только совпадающие строки)
Третий пункт и формирует механизм фильтрации.
Таблица отфильтрована по месяцу, указанному в таблице-фильтре (только заказы июня)
Аналогично присоединим к основной таблице вторую таблицу-фильтр по ключевому столбцу "статус заказа".
Загрузим результат на новый лист (вкладка Главная - Закрыть и загрузить в... - Таблица - Новый лист)
А теперь самое интересное - то, что делает отчет интерактивным.
Фильтруем данные управляемыми фильтрами
Поменяем в фильтрующих таблица значения фильтров:
Месяц = май, Статус = Отгружен
И перейдем на вкладку Данные - Обновить все
Основная таблица отфильтрована по новым фильтрам
При этом фильтры могут иметь несколько значений: например, месяц май и июнь.
В следующей статье расскажу, как точно так же фильтровать данные, которые выгружаются SQL-запросом.
Мои каналы в ТГ и MAX Ленивый аналитик - подписывайтесь, кому где удобнее. Там не только уведомления о новых статьях, но разные дополнительные полезности 😉