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. Табель учета рабочего времени сотрудников

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.

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.

В Excel Power Query оператор IF — одна из самых популярных функций для проверки условия и возврата определенного значения в зависимости от того, является ли результат ИСТИНА или ЛОЖЬ. Между этим оператором if и функцией ЕСЛИ в Excel есть некоторые различия. В этом уроке я познакомлю вас с синтаксисом этого оператора if и несколькими простыми и сложными примерами.

Базовый синтаксис оператора if в Power Query

Оператор Power Query if с использованием условного столбца

• Пример 1. Базовый оператор if
• Пример 2. Сложный оператор if

Power Query if, написав M-код

• Пример 1. Базовый оператор if
• Пример 2. Сложный оператор if
  • Вложенные операторы if
  • Оператор if с логикой ИЛИ
  • Оператор if с логикой AND
  • Если оператор с логиками ИЛИ и И

---

Базовый синтаксис оператора if в Power Query

В Power Query синтаксис такой:

= если логическая_проверка, то значение_если_истина, иначе значение_если_ложь

• логический_тест: условие, которое вы хотите проверить.
• значение_если_истина: возвращаемое значение, если результат TRUE.
• значение_если_ложь: возвращаемое значение, если результат FALSE.

Внимание: оператор Power Query if чувствителен к регистру, если, то и еще должны быть строчными.

В Excel Power Query существует два способа создания условной логики такого типа:

• Использование функции условного столбца для некоторых основных сценариев;
• Написание M-кода для более сложных сценариев.

В следующем разделе я расскажу о некоторых примерах использования этого оператора if.

---

Оператор Power Query if с использованием условного столбца

 Пример 1. Базовый оператор if

Здесь я расскажу, как использовать этот оператор if в Power Query. Например, у меня есть следующий отчет о продукте, если статус продукта «Старый», отображается скидка 50%; если статус продукта «Новый», отображается скидка 20%, как показано ниже.

1. Выберите таблицу данных на листе, затем в Excel 2019 и Excel 365 щелкните Данные > Из таблицы/диапазона, см. снимок экрана:

Внимание: в Excel 2016 и Excel 2021 нажмите Данные > Из таблицы, см. снимок экрана:

2. Затем в открытом Редактор Power Query окна, нажмите Добавить столбец > Условный столбец, см. снимок экрана:

3. В выскочившем Добавить условный столбец диалоговом окне выполните следующие действия:

• Имя нового столбца: введите имя для нового столбца;
• Затем укажите необходимые критерии. Например, я укажу Если Статус равен Старому, то 50%, иначе 20%.;

4, Затем нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно. Теперь новый скидка столбец добавлен, см. скриншот:

5. Если вы хотите отформатировать числа в процентах, просто нажмите ABC123 значок из скидка заголовок столбца и выберите Процент как вам нужно, см. снимок экрана:

6. Наконец, пожалуйста, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.

---

 Пример 2. Сложный оператор if

С помощью этой опции «Условный столбец» вы также можете вставить два или более условий в поле «Условный столбец». Добавить условный столбец диалог. Пожалуйста, сделайте так:

1. Выберите таблицу данных и перейдите к Редактор Power Query окно, нажав Данные > Из таблицы/диапазона. В новом окне нажмите Добавить столбец > Условный столбец.

2. В выскочившем Добавить условный столбец диалоговом окне выполните следующие действия:

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Укажите первый критерий в поле первого критерия, а затем щелкните Добавить пункт кнопку, чтобы добавить другие поля критериев по мере необходимости.

3. Закончив с критериями, нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно. Теперь вы получите новый столбец с нужным вам результатом. Смотрите скриншот:

4. Наконец, пожалуйста, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.

---

Power Query if, написав M-код

Обычно условный столбец полезен для некоторых базовых сценариев. Иногда вам может понадобиться использовать несколько условий с логикой И или ИЛИ. В этом случае вы должны написать код M внутри пользовательского столбца для более сложных сценариев.

 Пример 1. Базовый оператор if

Возьмем в качестве примера первые данные, если статус товара Старый, отображается скидка 50%; если статус продукта «Новый», отображается скидка 20%. Для написания M-кода сделайте следующее:

1. Выберите таблицу и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Редактор Power Query окно.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец, см. снимок экрана:

3. В выскочившем Пользовательский столбец диалоговом окне выполните следующие действия:

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Затем введите эту формулу: если [Статус] = «Старый», то «50%», иначе «20%» в Пользовательский столбец формула пунктом.

4, Затем нажмите OK чтобы закрыть это диалоговое окно. Теперь вы получите следующий результат, который вам нужен:

5, Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.

---

 Пример 2. Сложный оператор if

Вложенные операторы if

Обычно для проверки подусловий вы можете вложить несколько операторов if. Например, у меня есть таблица данных ниже. Если товар «Платье», сделайте скидку 50% от первоначальной цены; если товар «Свитер» или «Толстовка с капюшоном», дайте скидку 20% от первоначальной цены; и другие товары сохраняют первоначальную цену.

1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Редактор Power Query окно.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открытом Пользовательский столбец диалоговом окне выполните следующие действия:

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Затем введите приведенную ниже формулу в поле Пользовательский столбец формула пунктом.
• = если [Товар] = «Платье», то [Цена] * 0.5 иначе
  если [Товар] = «Свитер», то [Цена] * 0.8 иначе
  если [Товар] = «Толстовка», то [Цена] * 0.8
  еще [Цена]

3. А затем нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно, и вы получите новый столбец с нужными вам данными, см. скриншот:

4, Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.

---

Оператор if с логикой ИЛИ

Логика ИЛИ выполняет несколько логических тестов, и истинный результат возвращается, если какой-либо из логических тестов верен. Синтаксис:

= если логическая_проверка1 или логическая_проверка2 или …, то значение_если_истина иначе значение_если_ложь

Предположим, у меня есть приведенная ниже таблица, теперь я хочу, чтобы новый столбец отображался следующим образом: если продукт «Платье» или «Футболка», то бренд — «ААА», бренд других продуктов — «ВВВ».

1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Редактор Power Query окно.

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

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Затем введите приведенную ниже формулу в поле Формула пользовательского столбца пунктом.
• = если [Продукт] = «Платье» или [Продукт] = «Футболка», то «ААА»
  еще «БББ»

3. А затем нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно, и вы получите новый столбец с нужными вам данными, см. скриншот:

4, Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.

---

Оператор if с логикой AND

Логика И выполняет несколько логических тестов внутри одного оператора if. Все тесты должны быть истинными, чтобы возвращался истинный результат. Если какой-либо из тестов является ложным, возвращается ложный результат. Синтаксис:

= если логическая_проверка1 и логическая_проверка2 и …, то значение_если_истина иначе значение_если_ложь

Возьмите приведенные выше данные, например, я хочу, чтобы новый столбец отображался как: если продукт «Платье» и заказ больше 300, то дайте скидку 50% от исходной цены; в противном случае сохраните первоначальную цену.

1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Редактор Power Query окно.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открытом Пользовательский столбец диалоговом окне выполните следующие действия:

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Затем введите приведенную ниже формулу в поле Формула пользовательского столбца пунктом.
• = если [Продукт] = «Платье» и [Заказ] > 300, то [Цена] * 0.5
  еще [Цена]

3, Затем нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно, и вы получите новый столбец с нужными вам данными, см. скриншот:

4. Наконец, вы должны загрузить эти данные в новый рабочий лист, щелкнув Главная > Закрыть и загрузить > Закрыть и загрузить.

---

Если оператор с логиками ИЛИ и И

Хорошо, предыдущие примеры нам легко понять. Теперь давайте усложним. Вы можете комбинировать И и ИЛИ, чтобы сформировать любое условие, которое вы можете себе представить. В этом типе вы можете использовать скобки в формуле для определения сложных правил.

Возьмите приведенные выше данные в качестве примера. Предположим, я хочу, чтобы новый столбец отображался следующим образом: если продукт «Платье» и его заказ больше 300, или продукт «Брюки» и его заказ больше 300, то показать «A+», иначе отобразите «Другое».

1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Редактор Power Query окно.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открытом Пользовательский столбец диалоговом окне выполните следующие действия:

• Введите имя нового столбца в Имя нового столбца текстовое окно;
• Затем введите приведенную ниже формулу в поле Формула пользовательского столбца пунктом.
• =if ([Продукт] = «Платье» и [Заказ] > 300) или
  ([Товар] = «Брюки» и [Заказ] > 300 )
  потом «А+»
  еще «Другое»

3, Затем нажмите OK кнопку, чтобы вернуться к Редактор Power Query окно, и вы получите новый столбец с нужными вам данными, см. скриншот:

4. Наконец, вы должны загрузить эти данные в новый рабочий лист, щелкнув Главная > Закрыть и загрузить > Закрыть и загрузить.

title	description	author	ms.date	ms.author
Error handling	An article on how to catch and handle errors in Power Query using the syntax try and otherwise.	ptyx507x	12/9/2022	miescobar

Error handling

Similar to how Excel and the DAX language have an IFERROR function, Power Query has its own syntax to test and catch errors.

As mentioned in the article on dealing with errors in Power Query, errors can appear either at the step or cell level. This article will focus on how you can catch and manage errors based on your own specific logic.

[!Note]
To demonstrate this concept, this article will use an Excel Workbook as its data source. The concepts showcased here apply to all values in Power Query and not only the ones coming from an Excel Workbook.

The sample data source for this demonstration is an Excel Workbook with the following table.

This table from an Excel Workbook has Excel errors such as #NULL!, #REF!, and #DIV/0! in the Standard Rate column. When you import this table into the Power Query editor, the following image shows how it will look.

Notice how the errors from the Excel workbook are shown with the [Error] value in each of the cells.

In this article, you’ll learn how to replace an error with another value. In addition, you’ll also learn how to catch an error and use it for your own specific logic.

In this case, the goal is to create a new Final Rate column that will use the values from the Standard Rate column. If there are any errors, then it will use the value from the correspondent Special Rate column.

Provide an alternative value when finding errors

In this case, the goal is to create a new Final Rate column in the sample data source that will use the values from the Standard Rate column. If there are any errors, then it will use the value from the corresponding Special Rate column.

To create a new custom column, go to the Add column menu and select Custom column. In the Custom column window, enter the formula try [Standard Rate] otherwise [Special Rate]. Name this new column Final Rate.

The formula above will try to evaluate the Standard Rate column and will output its value if no errors are found. If errors are found in the Standard Rate column, then the output will be the value defined after the otherwise statement, which in this case is the Special Rate column.

After adding the correct data types to all columns in the table, the following image shows how the final table looks.

[!NOTE]
As an alternative approach, you can also enter the formula try [Standard Rate] catch ()=> [Special Rate], which is equivalent to the previous formula, but using the catch keyword with a function that requires no parameters.

The catch keyword was introduced to Power Query in May of 2022.

Provide your own conditional error logic

Using the same sample data source as the previous section, the new goal is to create a new column for the Final Rate. If the value from the Standard Rate exists, then that value will be used. Otherwise the value from the Special Rate column will be used, except for the rows with any #REF! error.

[!Note]
The sole purpose of excluding the #REF! error is for demonstration purposes. With the concepts showcased in this article, you can target any fields of your choice from the error record.

When you select any of the whitespace next to the error value, you get the details pane at the bottom of the screen. The details pane contains both the error reason, DataFormat.Error, and the error message, Invalid cell value '#REF!':

You can only select one cell at a time, so you can effectively only see the error components of one error value at a time. This is where you’ll create a new custom column and use the try expression.

Use try with custom logic

To create a new custom column, go to the Add column menu and select Custom column. In the Custom column window, enter the formula try [Standard Rate]. Name this new column All Errors.

The try expression converts values and errors into a record value that indicates whether the try expression handled an error or not, as well as the proper value or the error record.

You can expand this newly created column with record values and look at the available fields to be expanded by selecting the icon next to the column header.

This operation will expose three new fields:

• All Errors.HasError—displays whether the value from the Standard Rate column had an error or not.
• All Errors.Value—if the value from the Standard Rate column had no error, this column will display the value from the Standard Rate column. For values with errors this field won’t be available, and during the expand operation this column will have null values.
• All Errors.Error—if the value from the Standard Rate column had an error, this column will display the error record for the value from the Standard Rate column. For values with no errors, this field won’t be available, and during the expand operation this column will have null values.

For further investigation, you can expand the All Errors.Error column to get the three components of the error record:

• Error reason
• Error message
• Error detail

After doing the expand operation, the All Errors.Error.Message field displays the specific error message that tells you exactly what Excel error each cell has. The error message is derived from the Error Message field of the error record.

Now with each error message in a new column, you can create a new conditional column with the name Final Rate and the following clauses:

• If the value in the All Errors.Errors.Message column equals null, then the output will be the value from the Standard Rate column.
• Else, if the value in the All Errors.Errors.Message column doesn’t equal Invalid cell value '#REF!'., then the output will be the value from the Special Rate column.
• Else, null.

After keeping only the Account, Standard Rate, Special Rate, and Final Rate columns, and adding the correct data type for each column, the following image demonstrates what the final table looks like.

Use try and catch with custom logic

Alternatively, you can also create a new custom column using the try and catch keywords.

try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null

More resources

• Understanding and working with errors in Power Query
• Add a Custom column in Power Query
• Add a Conditional column in Power Query

In this post, I am going to talk about the IFERROR function in Power Query. There is no explicit IFERROR function in Power Query but we have try and otherwise statement which works similar to the IFERROR function in Excel.

IFERROR in Power Query (try and otherwise) – Video

Power Query try and otherwise – Example 1

1. Consider this sample data. I have Employee ID, Total Pay, and Hours.
2. I created a Custom Column to calculate Pay per Hour

= [Total Pay] / [Hours]

Note – Emp ID 6 returns an error because 200 is divided by a text = “nil”. To correct the above error, I am going use try and otherwise within the formula

= try [Total Pay]/[Hours] otherwise null

As an output, I got a null instead of an Error.

Power Query try and otherwise – Example 2

The try and otherwise statement can also be applied to the entire step.

1. I deleted the Custom Column for Pay/Hours
2. And Changed Type, results in an error since it wasn’t able to find the previous step

This is a step-level error that happened at the Changed Type Step.

To fix this error I will surround my entire formula with the try and otherwise statement in the formula bar.

= try
    Table.TransformColumnTypes(Source, {{'Pay/Hours', type number}})
otherwise
    #'Removed Columns'

If the above formula (which is trying to change the type of Pay/Hours) returns an error then the otherwise statement returns the previous step – Removed Columns. The query throws no errors.

Power Query try and otherwise – Example 3

This time let’s write the try statement (without otherwise). Let’s calculate Pay/Hours but this time with only the try keyword.

Create a new Custom column with the following formula,

= try [Total Pay]/[Hours]

As a result, it gives me a column that contains records.

Expanding Custom column is going to return 3 more columns.

1. HasError – Shows TRUE if the formula resulted in an Error.
2. Value – Is the output of the formula with no errors.
3. Error – Again contains records that will describe the error upon expanding.

Upon further expanding the Error column.

We again get 3 more columns,

1. Reason – This tells the reason for the error.
2. Message – This shows what actually the error is.
3. Detail – Tells the information of the error, message about the error.

On further expanding Detail column we get even more details about the error on that particular row.

So this is a very crude way of doing some very basic error reporting on your data in case your Power Query steps result in any errors.

More on Power Query

Promote Double Headers in Power Query

5 Tricks to Reduce Steps in Power Query

Remove Top Rows and Combine Data from Multiple Excel Files

Quick VLOOKUP in Power Query

Dynamic Unpivoting in Power Query

Аналог функции Excel ИЛИ() в Power Query – List.AnyTrue()

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

К сожалению, в Power Query нет функции ИЛИ(). Напомню, чтобы обратиться к списку функций, кликните на ссылку Сведения о формулах Power Query в нижней части диалогового окна Настраиваемый столбец. Вы окажитесь на странице сайта Microsoft с обзором всех функций Power Query. В категории List functions можно найти функцию List.AnyTrue, которая возвращает ИСТИНА, если хоть одно выражение списка истинно. В документации по функции приведено два примера:

Определяет, является ли хотя бы одно из выражений в списке {true, false, 2 > 0} истинным…

List.AnyTrue({true, false, 2>0})

… и возвращает true.

Определяет, является ли хотя бы одно из выражений в списке {2 = 0, false, 2 < 0} истинным…

List.AnyTrue({2 = 0, false, 2 < 0})

… и возвращает false.

Попробуем использовать эту функцию в качестве теста в пользовательском столбце:

    if List.AnyTrue(

      {

        [Column1] = «TRUE»,

        [Sold By] = «FALSE»

      }

    )

    then

      «TRUE»

    else

      «FALSE»

Не забудьте разделить критерии запятыми, а список критериев окружить фигурными скобками, потому что функция List.AnyTrue() в качестве параметра требует список.

Репликация функции Excel И()

Для этих целей в Power Query есть функция List.AllTrue(). Эта функция возвращает истинное значение только в том случае, если каждый логический тест возвращает истинное значение. В Excel щелкните правой кнопкой мыши запрос pqOR –> Дублировать. Переименуйте запрос – pqAND. Если вы удалили столбец Match, удалите этот шаг, чтобы вернуть столбец Match в запрос. Щелкните значок шестеренки рядом с шагом Добавлен пользовательский столбец (для редактирования формулы). Заменить List.AnyTrue на List.AllTrue. Выберите шаг Строки с примененным фильтром.

Хотя в этих примерах мы явно отфильтровали данные на основе столбца Match, самое замечательное в функциях List.AnyTrue и List.AllTrue заключается в том, что вы можете помечать записи без фильтрации. Это добавит вам гибкости при построении более сложной логики, с возможностью сохранения всех исходных данных (чего нельзя получить просто фильтруя столбцы).

Репликация функции SWITCH() Power Pivot

Power Pivot имеет функцию SWITCH(), которая позволяет выполнять логику с несколькими условиями. Функция ищет указанное значение индекса и возвращает соответствующий результат. Эта функция проще, чем несколько уровней вложенных операторов ЕСЛИ(), поэтому полезно реплицировать функцию SWITCH() в Power Query.

Синтаксис этой функции в PowerPivot выглядит следующим образом:

=SWITCH(expression,value_1,result_1,[value_2,result_2],…,[Else])

Допустим, вы выставляете счета клиентам на основе кодированного шаблона, где каждый символ что-то значит. Например, в счете MP010450SP девятый символ может принимать следующие значения:

E = Employee, S = Yacht Club, N = Non-Taxable, R = Restricted, I = Inactive, L = Social, M = Medical, U = Regular

В Excel вы можете построить формулу с многократными вложениями оператора ЕСЛИ или использовать ВПР(). В Power Pivot, это намного проще с функцией SWITCH():

=SWITCH(

[Column],

«E», «Employee»,

«S», «Yacht Club»,

«N», «Non-Taxable»,

«R», «Restricted»,

«I», «Inactive»,

«L», «Social»,

«M», «Medical»,

«U», «Regular»,

«Undefined»

)

Построение функции Power Query SWITCH()

Откройте файл Emulating SWITCH.xIsx. Данные –> Получить данные –> Из других источников –> Пустой запрос. Назовите запрос – fnSWITCH. Главная –> Расширенный редактор. Введите код:

Ключевые части этого кода:

• result_1 – это первая из возможностей, которую вы можете передать в функцию
• return_value_1 – это значение, которое вернет функция, если первое значение = result_1
• Если вам нужно больше значений, вы добавляете запятую после строки {result_1, return_value_1} и вставляете строки {result_2, return_value_2}, {result_3, return_value_3} и т.д.
• Вы можете добавить столько значений, сколько вам нужно
• Если в предоставленном списке нет переданного значения функция вернет текст Undefined (это часть Else конструкции SWITCH).

Используя эту структуру, вы можете изменить функцию fnSWITCH для нашего сценария:

=fnSWITCH(Text.Range([Column1],8,1))

//Помните, что вам нужно начать извлечение текста с символа 8, чтобы получить девятый символ, потому что Power Query начинает отсчет с нуля

//fnSWITCH

Вы не ограничены поиском отдельных символов. Вы можете также искать значения (числа) или более длинные текстовые строки. Просто убедитесь, что ваши параметры всегда вводятся парами между фигурными скобками и имеют запятую в конце строки.

Когда вы закончите вносить изменения в Расширенном редакторе, нажмите кнопку Готово. Главная –> Закрыть и загрузить. Теперь вы можете использовать функцию fnSWITCH для извлечения типа выставленного счета из каждой записи.

Репликация функции Excel ВПР()

Репликация зависит от того, какая версия ВПР/VLOOKUP вам нужна. При поиске точного совпадения репликация может быть получена простым объединением двух таблиц (см. главу 9). Репликация приблизительного соответствия ВПР() требует довольно сложной логики (подробнее о функции ВПР в Excel см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). В примере вы не будете создавать сценарий Power Query с нуля но увидите как он работает. Откройте файл Emulating VLOOKUP.xlsx. В нем есть две таблицы:

Столбцы B:D таблицы данных содержат функции VLOOKUP() соответствующие заголовкам столбцов. Каждый столбец ищет значение, из столбца A для этой строки в таблице подстановки. Столбцы B и D возвращают значение из столбца 2 (G) таблицы подстановки, а столбец C – из столбца 3 (Н). Также обратите внимание, что столбцы B и C возвращают приблизительные совпадения, поскольку четвертый параметр функции VLOOKUP = True или опущен. Столбец D запрашивает точное совпадение (четвертый параметр = 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. Используйте формулу:

=pqVLOOKUP([Values],BandingLevels,2,true)

Рис. 22.9. Репликация VLOOKUP() с четвертым параметром равным true

Снова перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите столбец 3,default. Используйте формулу:

=pqVLOOKUP([Values],BandingLevels,3)

Рис. 22.10. Репликация VLOOKUP() с опущенным четвертым параметром (по умолчанию = true, приблизительное совпадение)

Теперь определите точное совпадение со вторым столбцом таблицы подстановки. Добавление столбца –> Настраиваемый столбец. Назовите его 2,false. Используйте формулу:

=pqVLOOKUP([Values],BandingLevels,2,false)

Рис. 22.11 Репликация VLOOKUP() с точным совпадением

Несмотря на то, что вы можете использовать эту функцию для эмуляции точного соответствия VLOOKUP(), лучше этого не делать, а воспользоваться объединением таблиц. Завершите запрос. Главная –> Закрыть и загрузить.

Вы должны знать об одном незначительном отличии между функцией VLOOKUP() Excel и pqVLOOKUP Power Query: значение #N/A, возвращаемое pqVLOOKUP, на самом деле является текстом, а не значением ошибки. Поскольку истинную ошибку #N/A в Power Query вернуть нельзя.

Понимание функции pqVLOOKUP

Взгляните на код:

Код довольно длинный и сложный, и он использует множество трюков, но основная методология следующая:

1. Втяните таблицу подстановки в Power Query.
2. Отсортируйте ее по убыванию по первому столбцу.
3. Удалите все записи, превышающие искомое значение.
4. Верните значение в запрошенном столбце таблицы данных для первой оставшейся записи, если не указано точное соответствие.
5. Если было указано точное соответствие, проверьте, соответствует ли возврат. Если это так, верните значение. Если это не так, верните #N/A.

Для каждого параметра функции в первой строке кода объявлен тип данных. Это делается для предотвращения проблем, если случайно в таблице подстановки заголовки будут числами.

Переменная approximate_match определена как необязательная (optional); пользователь может игнорировать ее.

Переменная matchtype проверяет, был ли указан тип соответствия. Если он был указан, именно он присваивается переменной matchtype, если не был указан (approximate_match равен null), то присваивается значение true.

Имя возвращаемого столбца извлекается путем просмотра заголовков столбцов таблицы, разбиения их на список записей и извлечения записи, индекс которой соответствует запрошенному столбцу (на 1 меньше, так как отсчет начинается с 0).

Данные сортируются в порядке убывания, в зависимости от столбца для поиска. Все записи, превышающие запрошенное значение, удаляются (путем выбора всех строк, где значение меньше или равно искомому значению).

Если строк не осталось, возвращается #N/А, если есть хотя бы одна строка, возвращается первая запись в столбце поиска. Этот результат может быть позже проверен, чтобы увидеть, соответствует ли он искомой записи (что важно для точного соответствия).

Затем вычисляется приблизительное значение соответствия, даже если было запрошено точное соответствие. Если в наборе данных нет строк, сохраняется результат #N/A; в противном случае из возвращаемого столбца извлекается ближайшее значение.

Последний тест проверяет тип запрошенного соответствия. Если это приблизительное совпадение, то возвращается самое близкое совпадение (которое может быть #N/A). Если тип соответствия был точным, код вернет #N/A вместо ближайшего соответствия, если значение столбца подстановки не соответствует точно искомому значению.

1 Power Query Ошибки №1. Измененный тип, Неверная фильтрация в UI Почему возникает ошибка Expression.Error Столбец таблицы не найден? Одна из причин — шаг Измененный тип.
Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе. 2 Power Query Ошибки №2. Фиксированная ширина столбца Excel В этом уроке вы узнаете как отключить автоматическое определение ширины столбцов таблицы Excel при обновлении запроса Power Query. 3 Power Query Ошибки №3. Formula Firewall, запрос ссылается на… Вы работаете в Power Query, но при попытке обновить все возникает ошибка Formula Firewall, запрос ссылается на… Что делать? Как исправить? 4 Power Query Ошибки №4. Доступ к веб-содержимому В этом уроке вы узнаете как избавиться от надоедливого окна доступ к веб-содержимому. 5 Power Query Ошибки №5. Количество столбцов CSV При импорте CSV получается неверное количество столбцов. 6 Power Query Ошибки №6. Не получается изменить тип данных (DataFormat.Error) При попытке изменить тип данных с текстового на дату возникает ошибка DataFormat.Error. 7 Power Query Ошибки №7. Разные имена листов В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. 8 Power Query Ошибки №8. Разные имена листов 2 В этом уроке мы другими способами решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. 9 Power Query Ошибки №9. Разные имена столбцов, Table.TransformColumnNames В этом уроке мы разберем проблему, когда нам присылают файлы, в которых всегда столбцы названы по-разному. 10 Power Query Ошибки №10. Как развернуть все столбцы В этой задаче мы научимся разворачивать все столбцы табличного столбца не перечисляя каждый из них хардкодом. 11 Power Query Ошибки №11. Подключиться к последнему файлу В этом уроке мы научимся подключаться к самому свежему файлу из нужной нам папки с нужным названием. 12 Power Query Ошибки №12. Консолидация и MissingField.Type Бывает вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден. 13 Power Query Ошибки №13. Удалить пустые столбцы Как без хардкода автоматически удалить пустые столбцы в Power Query, которые есть в импортируемой таблицы. Разберем 2 способа. Один из них прост до безобразия, а для второго понадобится функция Table.Profile. 14 Power Query Ошибки №14. Удалить лишние пробелы В этом уроке мы разберем еще 1 способ удалить лишние пробелы в текстовом столбце Power Query. Для этого мы повторим и изучим несколько новых функций.
Text.SplitAny
List.Select
Character.FromNumber
Text.Combine 15 Power Query Ошибки №15. Плохо структурированный TXT Как быть, если текстовый файл разбивается на столбцы неправильно? 16 Power Query Ошибки №16. При округлении не совпадает общая сумма Разберем ошибку, которая возникает при необходимости разбить число на определенные доли, а результаты округлить до двух знаков после запятой. В такой ситуации может получиться, что сумма слагаемых не будет равняться исходному числу. 17 Power Query Ошибки №17. Удаление дубликатов, Подводные камни Table. Distinct Что может пойти не так при использовании функции удаления дубликатов. 18 Power Query Ошибки №18. Удаление дубликатов 2, Подводные камни Table. Distinct Что может пойти не так при использовании функции удаления дубликатов. Другое решение данной проблемы. 19 Power Query Ошибки №19. Неверная разбивка на столбцы При импорте данных из CSV или TXT разбивка на столбцы происходит неправильно. 20 Power Query Ошибки №20. Пустая таблица при импорте Excel (XLSX, XLS) Вы пытаетесь подключиться к книге Excel, но импортируется пустая таблица. 21 Power Query Ошибки №21. Подключение к PostgreSQL, Установка драйвера Вы пытаетесь подключиться к PostgreSQL и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов»

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функция ERROR
• DAX функция IFERROR (если ошибка)
• DAX функция ISERROR

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы рассмотрим несколько функций в Power BI и Power Pivot, которые так или иначе обрабатывают возникающие во время вычислений в DAX, ошибки. И это функции ERROR, IFERROR и ISERROR.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

 

DAX функция ERROR в Power BI и Power Pivot

ERROR () — останавливает выполнение DAX кода и выводит заранее определенную пользователем ошибку (предупреждение).

Синтаксис: ERROR («Текст ошибки»)

Пример: в Power BI имеется исходная таблица с перечислением товаров и их количеством

Суть задачи: создать такую меру, чтобы она всегда вычисляла общее количество товара и пользователь не мог наложить никаких фильтров на это вычисление. Если пользователь накладывает фильтры, нужно остановить вычисление меры и в Power BI Desktop выдать пользователю ошибку (предупреждение).

Общее количество можно рассчитать при помощи DAX функции SUM:

Общее Количество Товара = SUM ('Товары'[Количество])

Данная формула действительно сможет посчитать общее количество товара, но она также легко подвержена пользовательским фильтрам, что по условию задачи нам не нужно:

Тогда мы можем изменить формулу выше и сумму рассчитать под следующим условием: если наложен какой-либо фильтр, то выдать пользователю предупреждение, если фильтра нет, то рассчитать количество.

Все это легко решается при помощи функций IF (условия «если») и ISFILTERED (проверяет на наличие фильтров):

Общее Количество Товара = 
IF(
    ISFILTERED('Товары'[Товар]);
    "ФИЛЬТРОВАТЬ ТОВАРЫ НЕЛЬЗЯ!";
    SUM('Товары'[Количество])
)

Получившаяся формула вполне рабочая, если мы выберем какой-либо товар, то нам действительно выйдет предупреждение:

В принципе, мы задачу практически решили. Но, можно пойти еще дальше и при пользовательском фильтре не то чтобы просто вывести предупреждение, а вообще остановить работу DAX формулы и тем самым действительно обратить внимание пользователя к ошибке.

И это как раз таки можно реализовать при помощи функции ERROR, прописав внутри нее наш текст предупреждения, и вставив ERROR заместо текста предупреждения в формуле выше:

Общее Количество Товара = 
IF(
    ISFILTERED('Товары'[Товар]);
    ERROR("ФИЛЬТРОВАТЬ ТОВАРЫ НЕЛЬЗЯ!");
    SUM('Товары'[Количество])
)

Тогда, если пользователь наложит фильтр, то DAX формула остановит свою работу:

И при нажатии на визуализации в Power BI на ссылку «См. подробности», выйдет текст самого предупреждения, который мы прописывали в ERROR:

Если убрать все фильтры, то, соответственно, формула рассчитает общее количество товаров и ни каких предупреждений от ERROR не будет.

DAX функция IFERROR (если ошибка) в Power BI и Power Pivot

IFERROR () — если ошибка. Производит вычисление выражения и если во время вычисления возникла ошибка, то функция выводит значение из второго параметра, если ошибок нет, то возвращается результат вычисления самого выражения.

Синтаксис: IFERROR (Выражение; Значение Если Ошибка)

Пример формулы 1: IFERROR (6 / 2; BLANK() ) 
Результат: 3

В итоге возвратился результат вычисления самого выражения, так как само выражение «6 / 2» вычисляется без ошибок и равно 3.

Пример формулы 2: IFERROR (6 / 0; BLANK() ) 
Результат: пусто

Так как на 0 делить нельзя, то результатом вычисления выражения будет ошибка и в этом случае IFERROR выведет значение из второго параметра, где в нашем случае стоит функция BLANK, которая, в свою очередь, выводит пустое значение.

То есть, функцией IFERROR можно обрабатывать ошибки в формулах, где возможно деление на 0. Но, кроме этого, можно при помощи нее застраховываться и от любых других ошибок, возникающих при выполнении формул в DAX.

DAX функция ISERROR в Power BI и Power Pivot

ISERROR () — относится к информационным функциям DAX. Она выводит значение TRUE (Истина), если значение, входящее в ее параметр вычисляется с ошибкой, а также, значение FALSE (Ложь), если ошибок нет.

Синтаксис: ISERROR (Значение)

Пример формулы 1: ISERROR (6 / 2)
Результат 1: FALSE (Ложь)

Пример формулы 2: ISERROR (6 / 0)
Результат 2: TRUE (Истина)

В первой формуле ISERROR выдала значение FALSE (Ложь), потому что выражение «6 / 2» вычисляется без ошибки. Тогда как, во втором случае выражение «6 / 0» вычисляется с ошибкой и поэтому ISERROR выдала значение TRUE (Истина).

Если ISERROR дополнить функцией условия «если» IF, то получится полный аналог DAX функции, которую мы рассматривали выше — IFERROR:

IFERROR = 
IF (
    ISERROR (Выражение);
    "Значение Если Ошибка"
    Выражение
)

 
На этом, с разбором функций обработок ошибок в Power BI и Power Pivot, все.

    Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

  Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D