Глава 18. Условная логика в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

По мере усложнения ваших решений в Power Query вы столкнетесь со сценарием, в котором вам нужно выполнить в столбце некую логику. И хотя в Power Query есть инструмент для этого, он отличается от того что ожидает встретить профессионал Excel.

Допустим вы импортируете расписание из текстового файла:

Ris. 18.1. Tekstovyj fajl soderzhit problemy

Рис. 18.1. Текстовый файл содержит проблемы

Скачать заметку в формате Word или pdf, примеры в формате архива

Имя сотрудника не включено в строки. Как его извлечь из шапки? Для решения этой задачи будет применена условная логика. Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из текстового/CSV-файла. Выберите файл 2015-03-14.txt. Кликните Импортировать. В окне предварительного просмотра кликните Преобразовать данные. В редакторе Power Query –> Главная –> Удалить строки –> Удаление верхних строк –> 4. Кликните Использовать первую строку в качестве заголовков.

Ris. 18.2. Familiya menedzhera popala v stolbtse Out

Рис. 18.2. Имя менеджера попала в столбце Out; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

У вас может возникнуть соблазн перенести имя Джона Томпсона в строки. Но есть и другие менеджеры, и вы понятия не имеете, сколько их. Решение может заключаться в том, чтобы добавить столбец с формулой, проверяющей, являются ли данные в столбце Out временем, и извлекающей данные, если тест не выполняется.

Поэкспериментируйте. Щелкните правой кнопкой мыши столбец Out –> Тип изменения –> Время. Как и следовало ожидать, все строки конвертируются красиво, но имя сотрудника возвращает ошибку:

Ris. 18.3. U Dzhona Tompsona net vremeni

Рис. 18.3. У Джона Томпсона нет времени))

Это ожидаемо, но можно ли это как-то использовать? Вы можете применить функцию Time.From(), чтобы преобразовать данные в допустимое время. И основываясь на знаниях Excel, вы бы ожидали, что это сработает:

(1) =IFERROR(Time.From([Out]),null)

К сожалению, эта формула вернет ошибку, так как Power Query не распознает функцию IFERROR (ЕСЛИОШИБКА). Power Query имеет собственную функцию для такой проверки, хотя и с совершенно иным синтаксисом:

=try <operation> otherwise <alternate result>

Оператор try пытается выполнить операцию. Если это удастся, то возвратит результат операции. Если, результатом является ошибка, то try вернет иное значение (или иную логику), указанное в части otherwise.

Это означает, что формула (1) может быть записана в Power Query следующим образом:

(2) =try Time.From([Out]) otherwise null

Такая формула вернет значение null для любой строки, содержащей имя сотрудника в столбце Out, и время для любой строки, в которой есть допустимое время.

В редакторе Power Query удалите шаг Измененный тип 1. Перейдите на вкладку Добавление столбца, кликните Настраиваемый столбец. Введите формулу (2). Нажмите Ok.

Ris. 18.4. Novyj stolbets vozvrashhaet vremya i null vmesto oshibki

Рис. 18.4. Новый столбец возвращает время и null вместо ошибки

Теперь можно добавить еще один столбец с простой логикой: если Пользовательская содержит null, верни значение из столбца Out, если это не так, верни null. Power Query использует для этого следующий синтаксис:

=if <logical test> then <result> else <alternate result>

Добавление столбца –> Настраиваемый столбец –> Присвойте ему имя Employee. Введите формулу:

=if [Custom]=null then [Out] else null

Ris. 18.5. Nakonets u Dzhona Tompsona est svoya sobstvennaya kolonka

Рис. 18.5. Наконец, у Джона Томпсона есть своя собственная колонка

Любопытно, если нажать шестеренку рядом со строкой Добавлен пользовательский столбец, появится окно, подсказывающее, как работает условный оператор:

Ris. 18.6. Dobavlenie uslovnogo stolbtsa

Рис. 18.6. Добавление условного столбца

Сейчас вы можете заполнить имя сотрудника в пустые строки. Щелкните правой кнопкой мыши столбец Employee (сотрудник) –> Заполнить –> Вниз.

Поскольку Power Query обрабатывает шаги последовательно, вам не нужно хранить промежуточные вычисления. Вы можете удалить столбец Пользовательская и очистить остальные данные. Щелкните правой кнопкой мыши столбец Пользовательская –> Удалить. Щелкните правой кнопкой мыши столбец Work Date –> Тип изменения –> Используя локаль –> Дата –> Языковый стандарт –> Английский (США). Перейдите на вкладку Главная. Выберите столбец Work Date –> Удалить строки –> Удалить ошибки. Щелкните правой кнопкой мыши столбец Out –> Тип изменения –> Используя локаль –> Время –> Языковый стандарт –> Английский (США). Выберите столбцы с Reg Hrs по Expense –> Тип изменения –> Используя локаль –> Десятичное число –> Языковый стандарт –> Английский (США). Переименовать запрос в Timesheet. Запрос готов к загрузке:

Ris. 18.7. Tabel ucheta rabochego vremeni sotrudnikov

Рис. 18.7. Табель учета рабочего времени сотрудников


Прокомментировать