В Power Query существует функция для проверки условия – if. Чтобы записать определенное условие в Power Query, используется структура с оператором if-then-else.
Создать условие можно двумя способами:
- Через создание условного столбца,
- Написать на Power Query через редактор кода.
Рассмотрим все способы работы с конструкцией if-then-else.
Условный столбец в Power Query
Необходимо проставить статусы для заказов. Статус «выполнен», если % выполнения – 100%. Если меньше 100%, то статус «в работе».
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. Для новых версий Excel: вкладка Данные → Из таблицы/диапазона:
Функцию if очень легко создать с помощью условного столбца. На вкладке Добавление столбца выбираем Условный столбец:
В диалоговом окне заполняем все необходимые поля:
- В поле «Имя нового столбца» вводим имя будущего столбца «Статус».
- В поле «Имя столбца» указываем столбец для оценки условия. Выбираем столбец «% выполнения заказа».
- В поле «Оператор» представлен список операторов. Для нашего примера выбираем оператор «равно».
Для разных типов данных будет предложен разный список операторов сравнения:
- Текст: начинается с, не начинается с, равняется, содержит и т. д.
- Номера: равно, не равно, больше или равно и т. д.
- Время: до, после, равно, не равно и т. д.
Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, им будет присвоено значение по результатам выполнения первого условия.
- В поле «Значение» вводим конкретное значение для сравнения.
- В полях «Значение», «Имя столбца» и «Оператор» составляем наше условие.
- В поле «Вывод» указываем значение, которое будет возвращено, если условие выполнено.
- В поле «В противном случае» указываем другое значение, которое нужно вернуть, если условие не выполняется.
В результате получаем новый столбец со статусами:
Осталось выгрузить получившийся отчет в Excel с помощью кнопки "Закрыть и загрузить" на вкладке Главная:
С помощью опции «Условный столбец» вы можете вставить дополнительные условия.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Выбранный столбец по условию
В таблице заказов необходимо перевести все суммы в рубли. Имеются данные по суммам заказов, в какой валюте заказ выполнен и конкретные курсы валют на дату заказа.
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. После загрузки данных в Power Query создаем условный столбец. На вкладке Добавление столбца выбираем Условный столбец:
В диалоговом окне заполняем все необходимые поля:
- В поле «Имя нового столбца» вводим имя будущего столбца «Курс валюты».
- В поле «Имя столбца» указывается столбец для оценки условия. Из выпадающего списка столбцов выбираем столбец «Валюта».
- В поле «Оператор» представлен список операторов. Выбираем для нашего примера оператор «содержит».
- В поле «Значение» вводим конкретное значение для сравнения USD.
Помните, что Power Query чувствителен к регистру: строчные и прописные буквы для него разные символы!
В поле «Вывод» выбираем из выпадающего списка команду «Выберите столбец»:
Далее из выпадающего списка имен столбцов выбираем столбец «USD»:
Теперь пропишем еще два условия. Нажимаем на кнопку «Добавить предложение». После этого появляется новая строчка «Иначе если», которую заполняем по аналогии с первым условием.
В поле «Имя столбца» выбираем столбец «Валюта». В поле «Оператор» выбираем оператор «содержит». В поле «Вывод» выбираем из выпадающего списка команду «Выберите столбец», далее указываем столбец EUR:
Добавляем аналогичное условие для валюты CNY. Осталось заполнить последнее поле в случае невыполнения ни одного из 3-х наших условий. В поле «В противном случае» указываем значение 1, которое нужно вернуть, если условия не выполняются. Это значение будет присвоено, если заказ был сделан в рублях (RUB):
Рассмотрим, как работает условный столбец:
- Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия.
- Для остальных значений из множества будет проверено следующее условие. И так далее до тех пор, пока не будут проверены все условия.
- Если для элементов множества не будет выполнено ни одно условие, то в условный столбец попадет значение из поля «В противном случае».
Получаем новый столбец с нужными курсами валют:
Для решения нашей задачи создаем настраиваемый столбец, в котором перемножаем столбец с суммами заказов и столбец с нужным курсом:
Присваиваем имя новому столбцу «Сумма заказа в рублях», прописываем формулу и нажимаем Ок:
Осталось выгрузить получившийся отчет в Excel с помощью кнопки Закрыть и загрузить на вкладке Главная:
Базовые условия можно проверить с помощью условного столбца, но задать более сложные условия с вычислениями можно только на языке М через написание кода. Рассмотрим на примере ниже.
Настраиваемый столбец c конструкцией if-then-else
Необходимо присвоить скидку в 10% всем заказчикам со способом оплаты «аванс» и рассчитать цену со скидкой.
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона. Для решения нашей задачи создаем настраиваемый столбец, в котором прописываем конструкцию if…then…else:
Присваиваем имя новому столбцу «Цена со скидкой» и прописываем формулу: если способ оплаты «аванс», то высчитываем цену со скидкой (Цена – Цена*10%). Если другие способы оплаты, то выводим обычную цену.
Обратите внимание, что в качестве разделителя десятичной части используется точка вместо запятой:
В результате работы нашей формулы получаем новый столбец уже с корректной финальной ценой:
Выгружаем получившийся отчет в Excel с помощью кнопки Закрыть и загрузить на вкладке Главная.
Условие с обработкой пустой ячейки null
Функция if имеет особенности в работе с пустыми ячейками (null). При попытке выполнить операции сравнения с null появится ошибка. Значения null можно проверить на равенство, но null равен только null. Если вы хотите сравнить null с любым другим значением при помощи относительного оператора (например, <, >, <=, >=), тогда результат сравнения будет не логическим значением типа true или false, а именно null. В этом случае выражение if…then…else покажет ошибку. Как избавиться от этой ошибки, если ваши данные содержат null, и замена его на другое значение не подходит?
Нужно увеличить стоимость заказа на стоимость доставки (200 руб.) во всех случаях, кроме самовывоза.
Загружаем данные в Power Query с помощью команд Данные → Получить данные → Из таблицы/диапазона. Создаем настраиваемый столбец, выбрав Добавление столбца – Настраиваемый столбец. В диалоговом окне присваиваем имя будущего столбца «Полная стоимость заказа» и прописываем формулу. В столбце «Доставка» имеются пустые ячейки, которые в Power Query считываются как null:
Сначала проверяем значения на равенство с null. Если ячейка в столбце «Доставка» пустая, то выводим значение из столбца «Стоимость заказа». На языке М эта запись будет выглядеть так:
if [Доставка] = null then [Стоимость заказа] else
Теперь записываем следующее условие:
Выражение if…then…else выполняет последовательное вычисление условий. Если первым условием будет идти относительное сравнение, ошибка снова появится и останется до конца расчета выражения. Именно поэтому сначала проверяем значение на наличие null.
В итоге получаем готовый расчет, который выполнен с помощью всего одного шага:
Заключение
Мы разобрали, как писать простые условия с помощью условного столбца и рассмотрели более сложные варианты написания конструкции if…then…else.
Выделим следующие особенности работы с функцией if:
- Условие "if" в Power Query пишется в нижнем регистре, формулы Power Query чувствительны к регистру.
- Вместо запятых, разделяющих аргументы значение_если_истина и значение_если_ложь, используем then и else (в нижнем регистре).
- При попытке выполнить операции сравнения с null появится ошибка. Сначала проверяем на равенство null, а затем записываем последующие условия.
- Текстовый тип данных может сравниваться только с текстом, а числа сравниваются с числами.
- Для чисел можно использовать следующие логические операторы:
- = равно
- <> не равно
- > больше, чем
- >= больше или равно
- < меньше, чем
- <= меньше или равно
6. Для текста и операций сравнения используем разные функции:
- Text.Contains - содержит
- not Text.Contains - не содержит
- Text.StartsWith - начинается с
- not Text.StartsWith - не начинается с
- Text.EndsWith - заканчивается на
- not Text.EndsWith - не заканчивается на
Комментарии