Условия IF-THEN-ELSE (ЕСЛИ-ТО) в Power Query

В 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):

Рассмотрим, как работает условный столбец: 

1. Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия. 
2. Для остальных значений из множества будет проверено следующее условие. И так далее до тех пор, пока не будут проверены все условия. 
3. Если для элементов множества не будет выполнено ни одно условие, то в условный столбец попадет значение из поля «В противном случае».

Получаем новый столбец с нужными курсами валют:

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

Присваиваем имя новому столбцу «Сумма заказа в рублях», прописываем формулу и нажимаем Ок:

Осталось выгрузить получившийся отчет в 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:

1. Условие "if" в Power Query пишется в нижнем регистре, формулы Power Query чувствительны к регистру.
2. Вместо запятых, разделяющих аргументы значение_если_истина и значение_если_ложь, используем then и else (в нижнем регистре).
3. При попытке выполнить операции сравнения с null появится ошибка. Сначала проверяем на равенство null, а затем записываем последующие условия.
4. Текстовый тип данных может сравниваться только с текстом, а числа сравниваются с числами.
5. Для чисел можно использовать следующие логические операторы:

• =  равно
• <>  не равно
• >  больше, чем
• >=  больше или равно
• <  меньше, чем
• <=  меньше или равно

6. Для текста и операций сравнения используем разные функции:

• Text.Contains - содержит
• not Text.Contains - не содержит
• Text.StartsWith - начинается с
• not Text.StartsWith - не начинается с
• Text.EndsWith - заканчивается на
• not Text.EndsWith - не заканчивается на