Глава 22. Продвинутая условная логика Power Query
Это продолжение перевода книжки Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независящими, потому рекомендую читать поочередно.
В главе 18 вы узнали, как перенести в Power Query способности функций Excel ЕСЛИ() и ЕСЛИОШИБКА(). Перенос остальных функций из библиотеки условной логики Excel труднее. Он основан на использовании списков и пользовательских функций.
Рис. 22.1. Начальные данные
Скачать заметку в формате Word или pdf, примеры в формате архива
Время от времени для вас будет необходимо отфильтровать данные по нескольким аспектам. Откройте файл Multi-Column Logic.xlsx (см. рис. 22.1).
Репликация функции Excel ИЛИ()
Допустим для вас необходимо отфильтровать строчки для которых Inventory Item = Talkative Parrot ИЛИ Sold By = Fred. Если это данные Таблицы Excel, можно применять расширенный фильтр или добавить столбец фильтрации:
Рис. 22.2. Столбец фильтрации на базе логического ИЛИ
Поглядим, что можно создать в Power Query. Удалите столбец Filter. Изберите всякую ячейку в Таблице. Пройдите по меню Данные –> Из таблицы/спектра. В редакторе Power Query щелкните правой клавишей мыши столбец Date –> Тип конфигурации –> Дата. Так как вы не сможете фильтровать данные без утраты части записей, для вас необходимо добавить пользовательский столбец и применить формулу для проверки каждой строчки.
Добавление столбца –> Настраиваемый столбец. Назовите столбец Match (поиск, совпадение). Вспомяните, в главе 18 был описан синтаксис условной логики Power Query:
=if <logical test> then <result> else <alternate result>
К огорчению, в Power Query нет функции ИЛИ(). Напомню, чтоб обратиться к списку функций, кликните на ссылку Сведения о формулах Power Query в нижней части диалогового окна Настраиваемый столбец. Вы окажитесь на страничке веб-сайта Microsoft с обзором всех функций Power Query. В группы List functions можно отыскать функцию List.AnyTrue, которая возвращает ИСТИНА, если хоть одно выражение перечня поистине. В документации по функции приведено два примера:
Описывает, является ли хотя бы одно из выражений в перечне
… и возвращает true.
Описывает, является ли хотя бы одно из выражений в перечне <2 = 0, false, 2 < 0>настоящим…
… и возвращает false.
Попробуем применять эту функцию в качестве теста в пользовательском столбце:
Не забудьте поделить аспекты запятыми, а перечень критериев окружить фигурными скобками, поэтому что функция List.AnyTrue() в качестве параметра просит перечень. Нажав Ok, вы увидите, что тест возвращает корректные результаты:
Рис. 22.3. Аналог функции Excel ИЛИ() в Power Query – List.AnyTrue(); чтоб прирастить изображение кликните на нем правой клавишей мыши и изберите Открыть картину в новейшей вкладке
Отфильтруйте 100лбец Match. Измените имя запроса – pqOR. Основная –> Закрыть и загрузить. Power Query возвращает таблицу, содержащую в общей трудности 88 строк из начальных 332. Если столбец Match для вас не нужен, его можно удалить. Так как Power Query обрабатывает команды поочередно, удаление столбца Match не приведет к снятию фильтра:
Рис. 22.4. Отобранные по аспекты ИЛИ записи в Таблице на листе Excel
Репликация функции Excel И()
Для этих целей в Power Query есть функция List.AllTrue(). Эта функция возвращает настоящее значение лишь в том случае, если любой логический тест возвращает настоящее значение. В Excel щелкните правой клавишей мыши запрос pqOR –> Дублировать. Переименуйте запрос – pqAND. Если вы удалили столбец Match, удалите этот шаг, чтоб возвратить столбец Match в запрос. Щелкните значок шестеренки рядом с шагом Добавлен пользовательский столбец (для редактирования формулы). Поменять List.AnyTrue на List.AllTrue. Изберите шаг Строчки с примененным фильтром. Осталось только 6 строк:
Рис. 22.5. Внедрение функции List.AllTrue() для отбора записей
Хотя в этих примерах мы очевидно отфильтровали данные на базе столбца Match, самое замечательное в функциях List.AnyTrue и List.AllTrue заключается в том, что вы сможете помечать записи без фильтрации. Это добавит для вас гибкости при построении наиболее сложной логики, с возможностью сохранения всех начальных данных (чего же недозволено получить просто фильтруя столбцы).
Репликация функции Excel ВПР()
Репликация зависит от того, какая версия ВПР/VLOOKUP для вас нужна. При поиске четкого совпадения репликация быть может получена обычным объединением 2-ух таблиц (см. главу 9). Репликация ориентировочного соответствия ВПР() просит достаточно сложной логики (подробнее о функции ВПР в Excel см. Билл Джелен. Всё о ВПР: от первого внедрения до экспертного уровня). В примере вы не будете создавать сценарий Power Query с нуля но увидите как он работает. Откройте файл Emulating VLOOKUP.xlsx. В нем есть две таблицы:
Рис. 22.6. Таблица подстановки
Рис. 22.7. Таблица данных
Столбцы B:D таблицы данных содержат функции VLOOKUP() надлежащие заголовкам столбцов. Любой столбец отыскивает значение, из столбца A для данной нам строчки в таблице подстановки. Столбцы B и D возвращают значение из столбца 2 (G) таблицы подстановки, а столбец C – из столбца 3 (Н). Также направьте внимание, что столбцы B и C возвращают ориентировочные совпадения, так как 4-ый параметр функции VLOOKUP = True или опущен. Столбец D запрашивает четкое совпадение (4-ый параметр = False), в итоге чего же все записи возвращают #N/A, кроме крайней строчки.
Давайте поместим сценарий Power Query в файл, а потом поглядим, как он реплицирует функцию VLOOKUP() Excel. В проводнике Windows кликните на файл pqVLOOKUP.txt Он раскроется в Блокноте. Выделите и скопируйте в буфер все содержимое файла. Вернитесь в Excel. Данные –> Получить данные –> Из остальных источников –> Пустой запрос –> Расширенный редактор. Выделите всю заготовку кода в окне. Ctrl+V (вставив из буфера ранее скопированный код). Нажмите клавишу Готово. Переименуйте функцию pqVLOOKUP. Основная –> Закрыть и загрузить (функции сохраняются лишь в режиме подключения).
При работе с функцией для вас пригодится указатель на таблицу подстановки BandingLevels. Изберите всякую ячейку в ней –> Данные –> Из таблицы/спектра. Основная –> Закрыть и загрузить… –> Лишь сделать подключение.
Сейчас всё готово, чтоб поглядеть, как это работает. Удалите из таблицы данных (DataTable) все формулы Excel (ячейки В3:D10). Изберите всякую ячейку в таблице DataTable –> Данные –> Из таблицы/спектра. Щелкните правой клавишей мыши столбец Values –> Удалить остальные столбцы:
Рис. 22.8. Запрос готов к использованию функции pqVLOOKUP
Чтоб проверить, работает ли функция PQ VLOOKUP вам, вы сможете испытать повторить последующую формулу: =VLOOKUP ([Values], BandingLevels, 2, true)
Для этого можно выполнить последующие деяния:
Добавление столбца –> Настраиваемый столбец. Назовите столбец 2,True. Используйте формулу:
Рис. 22.9. Репликация VLOOKUP() с четвертым параметром равным true
Опять перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите столбец 3,default. Используйте формулу:
Рис. 22.10. Репликация VLOOKUP() с опущенным четвертым параметром (по дефлоту = true, ориентировочное совпадение)
Сейчас обусловьте четкое совпадение со вторым столбцом таблицы подстановки. Добавление столбца –> Настраиваемый столбец. Назовите его 2,false. Используйте формулу:
Рис. 22.11 Репликация VLOOKUP() с четким совпадением
Невзирая на то, что вы сможете применять эту функцию для эмуляции четкого соответствия VLOOKUP(), лучше этого не созодать, а пользоваться объединением таблиц. Окончите запрос. Основная –> Закрыть и загрузить.
Вы должны знать о одном незначимом отличии меж функцией VLOOKUP() Excel и pqVLOOKUP Power Query: значение #N/A, возвращаемое pqVLOOKUP, по сути является текстом, а не значением ошибки. Так как настоящую ошибку #N/A в Power Query возвратить недозволено.
