Power query формула если ошибка

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

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

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

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

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

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

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

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

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

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

Рис. 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.

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

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

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

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

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

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

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

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

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

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

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

В Power Query существует функция для проверки условия – if. Чтобы записать определенное условие в Power Query, используется структура с оператором if-then-else.

Создать условие можно двумя способами:

• Через создание условного столбца,
• Написать на Power Query через редактор кода.

Рассмотрим все способы работы с конструкцией if-then-else.

Необходимо проставить статусы для заказов. Статус «выполнен», если % выполнения – 100%. Если меньше 100%, то статус «в работе».

Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. Для новых версий Excel: вкладка Данные → Из таблицы/диапазона:

Функцию if очень легко создать с помощью условного столбца. На вкладке Добавление столбца выбираем Условный столбец:

В диалоговом окне заполняем все необходимые поля:

• В поле «Имя нового столбца» вводим имя будущего столбца «Статус». 
• В поле «Имя столбца» указываем столбец для оценки условия. Выбираем столбец «% выполнения заказа».
• В поле «Оператор» представлен список операторов. Для нашего примера выбираем оператор «равно». 

Для разных типов данных будет предложен разный список операторов сравнения:

• Текст: начинается с, не начинается с, равняется, содержит и т. д.
• Номера: равно, не равно, больше или равно и т. д.
• Время: до, после, равно, не равно и т. д.

Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, им будет присвоено значение по результатам выполнения первого условия. 

• В поле «Значение» вводим конкретное значение для сравнения. 
• В полях «Значение», «Имя столбца» и «Оператор» составляем наше условие.
• В поле «Вывод» указываем значение, которое будет возвращено, если условие выполнено.
• В поле «В противном случае» указываем другое значение, которое нужно вернуть, если условие не выполняется.

В результате получаем новый столбец со статусами:

Осталось выгрузить получившийся отчет в Excel с помощью кнопки «Закрыть и загрузить» на вкладке Главная:

С помощью опции «Условный столбец» вы можете вставить дополнительные условия. 

Выбранный столбец по условию

В таблице заказов необходимо перевести все суммы в рубли. Имеются данные по суммам заказов, в какой валюте заказ выполнен и конкретные курсы валют на дату заказа.

Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. После загрузки данных в 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 — не заканчивается на

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.

Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise

In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.

Watch the Video

Download Sample Excel Workbook

First up, let’s load data from this table.

I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.

But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?

Let’s load the data into Power Query and call it Errors from Sheet

Straight away you can see the errors in the column.

Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.

Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.

I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]

This creates a new column with a Record in each row

In this record are two fields. HasError states whether or not there’s an error in the [End] column

If there is an Error then the 2nd field is another record containing information about that error

If there isn’t an error, then the 2nd field is the value from the [End] column

If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value

Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.

If I expand this Error column I can see all of these fields.

I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way

The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.

What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.

If there isn’t an error I want the value from the [End] column.

I can do all of this in a new column using an if then else

Add a new Custom Column called Error or Value and enter this code

What this is saying is:

• If the boolean value [HasError] in the [Try_End] column is true then
• return the [Message] in the [Error] record of the [Try_End] column
• else return the [Value] from the [Try_End] column

With that written I can remove both the End and Try_End columns so the final table looks like this

Checking for Errors and Replacing Them With Default Values

In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.

I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step

I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]

this gives me an error as I know it will in rows 1 and 3

so to avoid this, edit the step and use try .. otherwise

now the errors are replaced with 0.

Errors Loading Data from A Data Source

I’ll create a new query and load from an Excel workbook

Navigating to the file I want I load it

and loading this table

I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.

I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.

I don’t have an X: drive so I know this will cause the workbook loading to fail.

So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.

Open the Advanced Editor again and then use try otherwise to specify the backup file’s location

close the editor and now my backup file is loaded.

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.

Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise

In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.

Watch the Video

Download Sample Excel Workbook

First up, let’s load data from this table.

I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.

But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?

Let’s load the data into Power Query and call it Errors from Sheet

Straight away you can see the errors in the column.

Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.

Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.

I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]

This creates a new column with a Record in each row

In this record are two fields. HasError states whether or not there’s an error in the [End] column

If there is an Error then the 2nd field is another record containing information about that error

If there isn’t an error, then the 2nd field is the value from the [End] column

If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value

Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.

If I expand this Error column I can see all of these fields.

I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way

The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.

What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.

If there isn’t an error I want the value from the [End] column.

I can do all of this in a new column using an if then else

Add a new Custom Column called Error or Value and enter this code

What this is saying is:

• If the boolean value [HasError] in the [Try_End] column is true then
• return the [Message] in the [Error] record of the [Try_End] column
• else return the [Value] from the [Try_End] column

With that written I can remove both the End and Try_End columns so the final table looks like this

Checking for Errors and Replacing Them With Default Values

In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.

I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step

I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]

this gives me an error as I know it will in rows 1 and 3

so to avoid this, edit the step and use try .. otherwise

now the errors are replaced with 0.

Errors Loading Data from A Data Source

I’ll create a new query and load from an Excel workbook

Navigating to the file I want I load it

and loading this table

I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.

I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.

I don’t have an X: drive so I know this will cause the workbook loading to fail.

So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.

Open the Advanced Editor again and then use try otherwise to specify the backup file’s location

close the editor and now my backup file is loaded.

In this article we embark on an exciting journey to discover the power of the IF function in Power Query. You’ll learn why mastering this skill can transform your data analysis capabilities.

In this comprehensive guide, we’ll start from the basics and work our way up to advanced techniques. By the end of this article, you’ll be a Power Query IF statement pro.

Introduction

Power Query is an amazing tool within Excel and Power BI that helps you connect, clean, and transform data from various sources. The IF statement is a key building block for adding conditional logic to your Power Query transformations.

The IF Function in Power Query

The IF function is essential in your Power Query toolkit. It enables you to compare a value with a specified condition, leading to two possible results. It’s known as a conditional statement.

The function first checks if a condition is met. If the condition is true, Power Query returns one result. If it’s false, it returns another result.

Why is the If Statement Important?

IF statements are crucial in Power Query because they let you tailor data transformations according to your unique business rules. With the IF statement, you can set up conditional logic that tells Power Query what to do with your data based on specific conditions. This helps you extract valuable insights from your data quickly and efficiently.

For instance, imagine you have a table of sales data and want to create a new column. This column should indicate whether each sale was high or low value. You could use an IF statement to establish this conditional logic and create the new column with the results.

Eager to learn more? Let’s jump right in and build a strong foundation in IF statement basics.

Getting Started with IF Statements

In this chapter, you’ll learn the fundamentals of IF statements. That includes the syntax, basic examples, and how to create your first IF statement.

Syntax

Before we go any further, let’s understand how to write an IF statement in Power Query. Here’s the basic syntax of a Power Query IF statement:

if "condition" then "value-if-true" else "value-if-false"

This can be broken down into three parts:

• condition: This is the condition you’re testing, such as comparing a value to a specific number, or combining multiple conditions using logical operators.
• value-if-true: This is the result or action you want when the condition is true.
• value-if-false: This is the result or action you want when the condition is false.

Remember, Power Query is case-sensitive, so use lowercase for “if”, “then”, and “else”.

Basic Example

Let’s start with a straightforward example to grasp the syntax. Imagine you have a list of numbers and want to label them as “positive value” or “negative value”. You could use an IF statement like this:

if [Value] > 0 then "Positive value" else "Non-positive value"

In this example, the condition checks if the value is greater than 0. If it is, the “Positive value” is returned. If not, the “Non-positive value” is returned.

Creating Your First IF Statement

Now that you’ve got the basics, let’s make an IF statement together. Say you have a list of numbers and want to label them as “high” or “low”. You could use an IF statement like this:

if [Price] > 10 then "High Price" else "Low Price"

Here’s how to create your an IF statement:

• Go to the Add Column tab in the ribbon
• Select Custom Column
• Provide a Column Name
• Enter your IF statement
• Click OK

After pressing the ‘Custom Column’ button in the ‘Add Column’ tab, a new column will be added to your dataset.

The ‘Custom Column’ pop-up will appear, where you can provide both a Column Name and a Column Formula. Be sure to write “if”, “then”, and “else” in lowercase, and click ‘OK’.

Voilà! You now have a table with a newly created column.

Under the hood, Power Query generates this code:

Table.AddColumn(
    #"Changed Type", 
    "Category", 
    each if [Price] > 10 then "High Price" else "Low Price" )

Here’s a quick breakdown:

1. The Table.AddColumn function adds a new column to our table, called “Category”.
2. The each keyword applies the IF statement to every row in the table.
3. The condition checks if the number is greater than 10.
4. If the condition is true, “High Price” is returned. If false, “Low Price” is returned.

As you can see, the IF statement’s conditional logic helps Power Query classify data based on specific conditions. With its easy-to-understand syntax, you can quickly and efficiently categorize data or gain valuable insights.

Conditional Logic in Power Query

With the basics in hand, it’s time to delve deeper into conditional logic in Power Query. Understanding conditions, comparison operators, and logical operators will enable you to create more powerful IF statements.

Comparison Operators

Comparison operators let you compare values within your conditions. Here are the most common operators in Power Query:

For instance, to check if a product’s revenue equals $500, use the equal operator like this:

if [Revenue] = 500 then ... else ...

To classify ages 18 or younger as “Youth” and the rest as “Other“:

if [Age] <= 18 then "Youth" else "Other"

You get the idea. Now what if you want to combine multiple conditions?

Logical Operators

Logical operators allow you to combine multiple conditions. The main logical operators are:

Remember to use lowercase for these operators. Let’s look at some examples.

Applying Operators to IF Statements

IF statement with AND Logic

It’s also useful to know how to add if statements with and logic to test multiple conditions. Let’s say you want to find products with revenue greater than $500 and less than $900. To test this, your conditional if statement should include two conditions. You’d use the and operator like this:

if [Revenue] > 500 and [Revenue] < 900 then "Relevant Products" else "Other"

This example only included a single and operator. You can add more conditions to the same expression:

if [Revenue] > 500 and [Revenue] < 900 and [Category] = "T-Shirts" 
   then "Relevant Products" 
   else "Other"

IF statement with OR Logic

In some cases you may want to test whether one of multiple conditions is true by combining if with or.  Imagine you are looking for the top 2 selling clothing categories. In this case T-Shirt or Sweater. You could use the or operator in this way:

if [Category] = "T-Shirt" or [Category] = "Sweater" then "Best Seller" else "Other"

Do you have a need to incorporate a lot of values? Make sure to check out how to perform the IN operation in Power Query in the upcoming chapter on Advanced Techniques.

IF statement with NOT Logic

Sometimes, you need to test if something is not true, either to exclude a condition or because it’s shorter to write the negative form. For instance, let’s say you want to increase the price of everything except lemons by 10%

You can add the not operator right after the word if. Just make sure to put the entire condition between parentheses.

if not ( [Food] = "Lemon" )  then [Price] * 1.1 else [Price]

These examples create a new column based on logic. If you want to replace values based on conditions, explore replacing values based on conditions. While slightly more complex, it’s closely related to the conditional logic in IF functions.

With a solid understanding of conditional logic, let’s move on to intermediate IF statement examples.

Intermediate IF Statement Examples

This chapter provides examples on how to work with nested IF statements, multiple conditions, and using the IF statement to categorize data effectively.

Nested IF Statements

Sometimes you need to check multiple conditions in sequence, and that’s when nested IF statements come in handy. You can include an IF statement inside another IF statement:

if [Condition1] then [Value1] else if [Condition2] then [Value2] else [Value3]

Without formatting, any code is difficult to read. So from now on I will serve you formatted code. The results are identical, they are simply much easier to read. Here’s the same code but formatted:

if [Condition1]     
   then [Value1] 
   else if [Condition2] 
           then [Value2]  
           else [Value3]

Let’s say you want to categorize products by revenue as “Low”, “Medium”, or “High”. You could use nested IF statements like this:

if [Revenue] < 500 
   then "Low"
   else if [Revenue] < 1000 
        then "Medium"
        else "High"

IF Statement with Multiple Conditions

You can also use logical operators to create more complex conditions. For instance, when you want to find products with revenue between $500 and $1000 and more than 50 units sold. You’d use both and and or operators like this:

if [Revenue] > 500 and [Revenue] < 1000 and [UnitsSold] > 50 
   then "Match" 
   else "No Match"

Using the IF Statement to Categorize Data

IF statements are great for categorizing data. Let’s say you have a table with student grades, and you want to add a column that shows the grade category (A, B, C, D, or F):

if [Grade] >= 90 then "A"
   else if [Grade] >= 80 then "B"
   else if [Grade] >= 70 then "C"
   else if [Grade] >= 60 then "D"
   else "F"

Working with Different Data Types

In this chapter we focus on how to use IF statements with various data types, such as text and dates, to expand your data manipulation toolkit.

Working with Text

Continuing with our IF statement journey, let’s explore how to work with text values. For instance, to check if a product name contains a specific keyword and categorize it you can use Text.Contains:

if Text.Contains( [Product Name], "Widget")  // Does [ProductName] contain "Widget
   then "Widget"   // if yes, return "Widget"
   else "Other"    // else return "Other"

Or test if the combination of two text fields matches another field:

if [FirstName] & [LastName] = [FullName] then true else false

You can also return a value whenever a part of a text string matches your test. Imagine that invoices starting with the text “MAR” relate to market revenue:

if Text.Start( [InvoiceID], 3 ) = "MAR" then "Marketing Revenue" else "Other"

For more inspiration, here’s an article that covers all text functions in Power Query.

Working with Dates

Dates are another common data type you’ll work with in Power Query. You can use IF statements to manipulate and categorize dates. For example, you can check if a date falls within a specific range:

if [OrderDate] >= #date(2023, 1, 1) and [OrderDate] <= #date(2023, 12, 31) 
   then "2023 Order"
   else "Other Year"

Or, you could categorize dates by day of the week using Date.DayOfWeek:

if Date.DayOfWeek([OrderDate]) = 0         then "Sunday"
   else if Date.DayOfWeek([OrderDate]) = 6 then "Saturday"
   else "Weekday"

Advanced IF Statement Techniques

In this section, we’ll explore more advanced techniques for working with IF statements in Power Query. We’ll look at the “in” operator equivalent, the coalesce function, and the type compatibility operator.

In Operator Equivalent

Many programming languages have the in operator. The in operator allows you to check if a value exists in a list. It’s syntax sugar for a set of or statements. This can be helpful when you want to categorize data based on a predefined set of values.

For example, let’s say you have a list of favorite customers, and you want to add a column to your sales data that shows if a customer is your favorite or not. Power Query does not have the in operator, but you can replicate the in operator by using List.Contains:

if List.Contains(  {"Alice", "Bob", "Charlie"}, [Customer Name] ) 
   then "Favorite" 
   else "Regular")

/* ------------------
-- is identical to
------------------ */

if [CustomerName] = "Alice" or [CustomerName] = "Bob" or [CustomerName] = "Charlie"
   then "Favorite" 
   else "Regular"

List.Contains checks whether the “CustomerName” appears in the list of favorite customers. This example only uses three list values. But you can expand this list with as many values as you want. You can even reference a column with values to check!

Want to learn more about lists? Make sure to check out my complete guide to lists with numerous examples.

Coalesce Operator

The coalesce function returns the first non-null value from a list of expressions. This is useful when you have multiple columns with potentially missing data and want to return the first available value.

For example, let’s say you have a table with three columns: “PrimaryPhone”, “SecondaryPhone”, and “TertiaryPhone”. You want to add a new column that displays the first available phone number for each record.

Power Query uses the ?? construct as Coalesce operator. You can use the coalesce operator in combination with an IF statement to achieve this:

[PrimaryPhone] ?? [SecondaryPhone] ?? [TertiaryPhone]??"No Phone"

/* ------------------
-- is identical to
------------------ */

if [PrimaryPhone] = null then
   if [SecondaryPhone] = null then
      if [TertiaryPhone] = null then 
         "No Phone" 
         else [TertiaryPhone]
      else [SecondaryPhone]
   else [PrimaryPhone]

Type Compatibility Operator

Sometimes, columns with mixed data types can cause errors in your IF statements. To handle these situations, use the type compatibility operator to check if a value is of a specific type.

For example, let’s say you have a column called “Data” that contains different data types. You want to create a new column that categorizes the data as “Numeric”, “Text”, “Date” or “Other”:

if [MixedData] is number then "Numeric" 
   else if [MixedData] is text then "Text" 
   else if [MixedData] is date then "Date" 
   else "Other"

In this example, we use the is operator to check if the value in the “Data” column is of number, text or date type. This way, we can handle mixed data types without causing errors.

Now you’re equipped with advanced IF statement techniques in Power Query! Next up we’ll be looking at error messages you might bump into.

Error messages

Power Query and Excel have small but important differences in their conditional statements. Small mistakes can easily cause errors in Power Query, and the error messages are often not very helpful. Let’s explore some common error messages and their causes.

Token Eof expected

You may get the error Token Eof expected when you mistake your capitalization or when using an incorrect function name. For example, you should write the words if, then, and else in lowercase for a working formula.

If you write any of these letters in uppercase in the Custom Column box, Power Query throws the error. The below example shows the word IF capitalized and you can see the error message: Token Eof expected.

Expression.SyntaxError: Token Comma expected

This error can occur when editing your formula in the formula bar. Interestingly, the error message suggests a missing comma is causing the error, which may not be the case. For example, using a capitalized “IF” can result in this error message.

Expression.SyntaxError: Token Literal expected

Another common error is the Token Literal expected. This error means the formula expects a condition, value, column name, or function somewhere in the formula but doesn’t receive one.

When adding conditions to your formula that include words like not, and, and or, you may get this error. In the example below, you can see the word and that suggests another condition is coming. Yet no additional condition is written. The word else follows after and indicates the second argument of the function should begin.

For as this an incorrect expression Power Query returns: “Expression.SyntaxError: Token Literal expected“.

Expression.SyntaxError: Token Then/Else expected

These errors can occur when the words “then” and “else” are missing or misplaced within the IF function. In Power Query, “then” and “else” separate arguments within the IF function. When writing nested IF statements, each statement needs to have a “then” and an “else” clause. If you omit the word and replace it with a separator, you’ll get one of these error messages:

Expression.SyntaxError: Token Then expected.

Expression.SyntaxError: Token Else expected.

These last two errors are a bit clearer, but the term “token” can still confuse users. In this context, a “token” refers to a specific word or symbol used in the Power Query language, such as “then” or “else.” When the error message mentions a missing or expected token, it means that particular word or symbol is missing or misplaced in your formula.

Conclusion

In this article, I showed several examples of how one could leverage if-statements in Power BI. One thing we didn’t cover is creating conditional statements by writing custom M-code using the advanced editor.

If you’re up for a challenge make sure to check out how to return values based on a condition. Mastering that skill will strongly improve the amount of data challenges you can tackle. That’s all I want to share about the Power Query/Power BI if statement. See you next time!

In this article we embark on an exciting journey to discover the power of the IF function in Power Query. You’ll learn why mastering this skill can transform your data analysis capabilities.

In this comprehensive guide, we’ll start from the basics and work our way up to advanced techniques. By the end of this article, you’ll be a Power Query IF statement pro.

Introduction

Power Query is an amazing tool within Excel and Power BI that helps you connect, clean, and transform data from various sources. The IF statement is a key building block for adding conditional logic to your Power Query transformations.

The IF Function in Power Query

The IF function is essential in your Power Query toolkit. It enables you to compare a value with a specified condition, leading to two possible results. It’s known as a conditional statement.

The function first checks if a condition is met. If the condition is true, Power Query returns one result. If it’s false, it returns another result.

Why is the If Statement Important?

IF statements are crucial in Power Query because they let you tailor data transformations according to your unique business rules. With the IF statement, you can set up conditional logic that tells Power Query what to do with your data based on specific conditions. This helps you extract valuable insights from your data quickly and efficiently.

For instance, imagine you have a table of sales data and want to create a new column. This column should indicate whether each sale was high or low value. You could use an IF statement to establish this conditional logic and create the new column with the results.

Eager to learn more? Let’s jump right in and build a strong foundation in IF statement basics.

Getting Started with IF Statements

In this chapter, you’ll learn the fundamentals of IF statements. That includes the syntax, basic examples, and how to create your first IF statement.

Syntax

Before we go any further, let’s understand how to write an IF statement in Power Query. Here’s the basic syntax of a Power Query IF statement:

if "condition" then "value-if-true" else "value-if-false"

This can be broken down into three parts:

• condition: This is the condition you’re testing, such as comparing a value to a specific number, or combining multiple conditions using logical operators.
• value-if-true: This is the result or action you want when the condition is true.
• value-if-false: This is the result or action you want when the condition is false.

Remember, Power Query is case-sensitive, so use lowercase for “if”, “then”, and “else”.

Basic Example

Let’s start with a straightforward example to grasp the syntax. Imagine you have a list of numbers and want to label them as “positive value” or “negative value”. You could use an IF statement like this:

if [Value] > 0 then "Positive value" else "Non-positive value"

In this example, the condition checks if the value is greater than 0. If it is, the “Positive value” is returned. If not, the “Non-positive value” is returned.

Creating Your First IF Statement

Now that you’ve got the basics, let’s make an IF statement together. Say you have a list of numbers and want to label them as “high” or “low”. You could use an IF statement like this:

if [Price] > 10 then "High Price" else "Low Price"

Here’s how to create your an IF statement:

• Go to the Add Column tab in the ribbon
• Select Custom Column
• Provide a Column Name
• Enter your IF statement
• Click OK

After pressing the ‘Custom Column’ button in the ‘Add Column’ tab, a new column will be added to your dataset.

The ‘Custom Column’ pop-up will appear, where you can provide both a Column Name and a Column Formula. Be sure to write “if”, “then”, and “else” in lowercase, and click ‘OK’.

Voilà! You now have a table with a newly created column.

Under the hood, Power Query generates this code:

Table.AddColumn(
    #"Changed Type", 
    "Category", 
    each if [Price] > 10 then "High Price" else "Low Price" )

Here’s a quick breakdown:

1. The Table.AddColumn function adds a new column to our table, called “Category”.
2. The each keyword applies the IF statement to every row in the table.
3. The condition checks if the number is greater than 10.
4. If the condition is true, “High Price” is returned. If false, “Low Price” is returned.

As you can see, the IF statement’s conditional logic helps Power Query classify data based on specific conditions. With its easy-to-understand syntax, you can quickly and efficiently categorize data or gain valuable insights.

Conditional Logic in Power Query

With the basics in hand, it’s time to delve deeper into conditional logic in Power Query. Understanding conditions, comparison operators, and logical operators will enable you to create more powerful IF statements.

Comparison Operators

Comparison operators let you compare values within your conditions. Here are the most common operators in Power Query:

For instance, to check if a product’s revenue equals $500, use the equal operator like this:

if [Revenue] = 500 then ... else ...

To classify ages 18 or younger as “Youth” and the rest as “Other“:

if [Age] <= 18 then "Youth" else "Other"

You get the idea. Now what if you want to combine multiple conditions?

Logical Operators

Logical operators allow you to combine multiple conditions. The main logical operators are:

Remember to use lowercase for these operators. Let’s look at some examples.

Applying Operators to IF Statements

IF statement with AND Logic

It’s also useful to know how to add if statements with and logic to test multiple conditions. Let’s say you want to find products with revenue greater than $500 and less than $900. To test this, your conditional if statement should include two conditions. You’d use the and operator like this:

if [Revenue] > 500 and [Revenue] < 900 then "Relevant Products" else "Other"

This example only included a single and operator. You can add more conditions to the same expression:

if [Revenue] > 500 and [Revenue] < 900 and [Category] = "T-Shirts" 
   then "Relevant Products" 
   else "Other"

IF statement with OR Logic

In some cases you may want to test whether one of multiple conditions is true by combining if with or.  Imagine you are looking for the top 2 selling clothing categories. In this case T-Shirt or Sweater. You could use the or operator in this way:

if [Category] = "T-Shirt" or [Category] = "Sweater" then "Best Seller" else "Other"

Do you have a need to incorporate a lot of values? Make sure to check out how to perform the IN operation in Power Query in the upcoming chapter on Advanced Techniques.

IF statement with NOT Logic

Sometimes, you need to test if something is not true, either to exclude a condition or because it’s shorter to write the negative form. For instance, let’s say you want to increase the price of everything except lemons by 10%

You can add the not operator right after the word if. Just make sure to put the entire condition between parentheses.

if not ( [Food] = "Lemon" )  then [Price] * 1.1 else [Price]

These examples create a new column based on logic. If you want to replace values based on conditions, explore replacing values based on conditions. While slightly more complex, it’s closely related to the conditional logic in IF functions.

With a solid understanding of conditional logic, let’s move on to intermediate IF statement examples.

Intermediate IF Statement Examples

This chapter provides examples on how to work with nested IF statements, multiple conditions, and using the IF statement to categorize data effectively.

Nested IF Statements

Sometimes you need to check multiple conditions in sequence, and that’s when nested IF statements come in handy. You can include an IF statement inside another IF statement:

if [Condition1] then [Value1] else if [Condition2] then [Value2] else [Value3]

Without formatting, any code is difficult to read. So from now on I will serve you formatted code. The results are identical, they are simply much easier to read. Here’s the same code but formatted:

if [Condition1]     
   then [Value1] 
   else if [Condition2] 
           then [Value2]  
           else [Value3]

Let’s say you want to categorize products by revenue as “Low”, “Medium”, or “High”. You could use nested IF statements like this:

if [Revenue] < 500 
   then "Low"
   else if [Revenue] < 1000 
        then "Medium"
        else "High"

IF Statement with Multiple Conditions

You can also use logical operators to create more complex conditions. For instance, when you want to find products with revenue between $500 and $1000 and more than 50 units sold. You’d use both and and or operators like this:

if [Revenue] > 500 and [Revenue] < 1000 and [UnitsSold] > 50 
   then "Match" 
   else "No Match"

Using the IF Statement to Categorize Data

IF statements are great for categorizing data. Let’s say you have a table with student grades, and you want to add a column that shows the grade category (A, B, C, D, or F):

if [Grade] >= 90 then "A"
   else if [Grade] >= 80 then "B"
   else if [Grade] >= 70 then "C"
   else if [Grade] >= 60 then "D"
   else "F"

Working with Different Data Types

In this chapter we focus on how to use IF statements with various data types, such as text and dates, to expand your data manipulation toolkit.

Working with Text

Continuing with our IF statement journey, let’s explore how to work with text values. For instance, to check if a product name contains a specific keyword and categorize it you can use Text.Contains:

if Text.Contains( [Product Name], "Widget")  // Does [ProductName] contain "Widget
   then "Widget"   // if yes, return "Widget"
   else "Other"    // else return "Other"

Or test if the combination of two text fields matches another field:

if [FirstName] & [LastName] = [FullName] then true else false

You can also return a value whenever a part of a text string matches your test. Imagine that invoices starting with the text “MAR” relate to market revenue:

if Text.Start( [InvoiceID], 3 ) = "MAR" then "Marketing Revenue" else "Other"

For more inspiration, here’s an article that covers all text functions in Power Query.

Working with Dates

Dates are another common data type you’ll work with in Power Query. You can use IF statements to manipulate and categorize dates. For example, you can check if a date falls within a specific range:

if [OrderDate] >= #date(2023, 1, 1) and [OrderDate] <= #date(2023, 12, 31) 
   then "2023 Order"
   else "Other Year"

Or, you could categorize dates by day of the week using Date.DayOfWeek:

if Date.DayOfWeek([OrderDate]) = 0         then "Sunday"
   else if Date.DayOfWeek([OrderDate]) = 6 then "Saturday"
   else "Weekday"

Advanced IF Statement Techniques

In this section, we’ll explore more advanced techniques for working with IF statements in Power Query. We’ll look at the “in” operator equivalent, the coalesce function, and the type compatibility operator.

In Operator Equivalent

Many programming languages have the in operator. The in operator allows you to check if a value exists in a list. It’s syntax sugar for a set of or statements. This can be helpful when you want to categorize data based on a predefined set of values.

For example, let’s say you have a list of favorite customers, and you want to add a column to your sales data that shows if a customer is your favorite or not. Power Query does not have the in operator, but you can replicate the in operator by using List.Contains:

if List.Contains(  {"Alice", "Bob", "Charlie"}, [Customer Name] ) 
   then "Favorite" 
   else "Regular")

/* ------------------
-- is identical to
------------------ */

if [CustomerName] = "Alice" or [CustomerName] = "Bob" or [CustomerName] = "Charlie"
   then "Favorite" 
   else "Regular"

List.Contains checks whether the “CustomerName” appears in the list of favorite customers. This example only uses three list values. But you can expand this list with as many values as you want. You can even reference a column with values to check!

Want to learn more about lists? Make sure to check out my complete guide to lists with numerous examples.

Coalesce Operator

The coalesce function returns the first non-null value from a list of expressions. This is useful when you have multiple columns with potentially missing data and want to return the first available value.

For example, let’s say you have a table with three columns: “PrimaryPhone”, “SecondaryPhone”, and “TertiaryPhone”. You want to add a new column that displays the first available phone number for each record.

Power Query uses the ?? construct as Coalesce operator. You can use the coalesce operator in combination with an IF statement to achieve this:

[PrimaryPhone] ?? [SecondaryPhone] ?? [TertiaryPhone]??"No Phone"

/* ------------------
-- is identical to
------------------ */

if [PrimaryPhone] = null then
   if [SecondaryPhone] = null then
      if [TertiaryPhone] = null then 
         "No Phone" 
         else [TertiaryPhone]
      else [SecondaryPhone]
   else [PrimaryPhone]

Type Compatibility Operator

Sometimes, columns with mixed data types can cause errors in your IF statements. To handle these situations, use the type compatibility operator to check if a value is of a specific type.

For example, let’s say you have a column called “Data” that contains different data types. You want to create a new column that categorizes the data as “Numeric”, “Text”, “Date” or “Other”:

if [MixedData] is number then "Numeric" 
   else if [MixedData] is text then "Text" 
   else if [MixedData] is date then "Date" 
   else "Other"

In this example, we use the is operator to check if the value in the “Data” column is of number, text or date type. This way, we can handle mixed data types without causing errors.

Now you’re equipped with advanced IF statement techniques in Power Query! Next up we’ll be looking at error messages you might bump into.

Error messages

Power Query and Excel have small but important differences in their conditional statements. Small mistakes can easily cause errors in Power Query, and the error messages are often not very helpful. Let’s explore some common error messages and their causes.

Token Eof expected

You may get the error Token Eof expected when you mistake your capitalization or when using an incorrect function name. For example, you should write the words if, then, and else in lowercase for a working formula.

If you write any of these letters in uppercase in the Custom Column box, Power Query throws the error. The below example shows the word IF capitalized and you can see the error message: Token Eof expected.

Expression.SyntaxError: Token Comma expected

This error can occur when editing your formula in the formula bar. Interestingly, the error message suggests a missing comma is causing the error, which may not be the case. For example, using a capitalized “IF” can result in this error message.

Expression.SyntaxError: Token Literal expected

Another common error is the Token Literal expected. This error means the formula expects a condition, value, column name, or function somewhere in the formula but doesn’t receive one.

When adding conditions to your formula that include words like not, and, and or, you may get this error. In the example below, you can see the word and that suggests another condition is coming. Yet no additional condition is written. The word else follows after and indicates the second argument of the function should begin.

For as this an incorrect expression Power Query returns: “Expression.SyntaxError: Token Literal expected“.

Expression.SyntaxError: Token Then/Else expected

These errors can occur when the words “then” and “else” are missing or misplaced within the IF function. In Power Query, “then” and “else” separate arguments within the IF function. When writing nested IF statements, each statement needs to have a “then” and an “else” clause. If you omit the word and replace it with a separator, you’ll get one of these error messages:

Expression.SyntaxError: Token Then expected.

Expression.SyntaxError: Token Else expected.

These last two errors are a bit clearer, but the term “token” can still confuse users. In this context, a “token” refers to a specific word or symbol used in the Power Query language, such as “then” or “else.” When the error message mentions a missing or expected token, it means that particular word or symbol is missing or misplaced in your formula.

Conclusion

In this article, I showed several examples of how one could leverage if-statements in Power BI. One thing we didn’t cover is creating conditional statements by writing custom M-code using the advanced editor.

If you’re up for a challenge make sure to check out how to return values based on a condition. Mastering that skill will strongly improve the amount of data challenges you can tackle. That’s all I want to share about the Power Query/Power BI if statement. See you next time!