Power query excel если ошибка то

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.

В 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 — не заканчивается на

В 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

s_bag Пользователь Сообщений: 34 Регистрация: 29.11.2013

Загружаю текстовый файл с помощью Power Query. Если сначала удаляю строки, содержащие ошибки в столбце Tran Date, а потом снимаю в фильтре галочку с null, всё Ok. Если сначала снимаю в фильтре галочку с null, то внизу показывает одну строку с Error, которую удалить нельзя. И, похоже, часть строк пропадает. Почему в этом месте важна последовательность действий? Собственно, обрабатываемый файл:GL Jan-Mar.TXT (17.08 КБ)Спасибо.

s_bag, потому что у вас перед этим идет шаг определения типа. на этом шаге у вас возникает ошибка из-за того, что данные (слова «Tran Amount») не распознались как дата. Если вы не избавились от этой ошибки, то при включении фильтра по null у вас происходит явное вычисление значений столбца для сравнения с nul. Оно длится до первой ошибки, далее операция прерывается, поэтому всё, что после первой ошибки, не показывается. Удалить ее не получается, потому что это такая «вложенная» ошибка. В общем, обрабатывать ошибки желательно прямо сразу после их возможного возникновения, во избежание

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365	#4 31.07.2020 20:04:17 Доброе время суток Может лучше так? Скрытый текст

s_bag Пользователь Сообщений: 34 Регистрация: 29.11.2013	Максим, спасибо за разъяснения. Андрей, это высший пилотаж! Пошел изучать код.

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365	#6 01.08.2020 11:12:57 Цитата s_bag написал: это высший пилотаж Ну, я бы так не сказал. Только либо имея чёткий стандарт формирования таких файлов, либо достаточную базу фактических файлов, можно сказать достаточно ли для идентификации строки с данными в файле по существованию не пустой строки между двумя символами /.

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020	Народ, а есть ли в PQ на M возможность прописать обработчик ошибок , который будет например при возникновении ошибки на каком -то шаге , заменять ошибку на null или на что-то другое?

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365	#8 30.08.2020 06:42:34 Цитата t330 написал: Народ И вам — здравствуйте, человечище! Что — настолько лень прочитать в спецификации языка Error Handling ? Microsoft считает, что на это достаточно трёх минут.

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020

#9 31.08.2020 16:23:10 Андрей, спасибо за ссылку, только я не смог применить … Например, хочу сделать так, чтобы ошибка для преобразовании типа данных обрабатывалась как в коде ниже, но не срабатывает Код let     Источник = Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content],     #"Измененный тип" = try Table.TransformColumnTypes(Источник,{{"Num", Int64.Type}}) otherwise Table.TransformColumnTypes(Источник,{{"Num", type text}}) in     #"Измененный тип" Прикрепленные файлы ОбработчикОшибок.xlsx (16.58 КБ) Изменено: t330 — 31.08.2020 16:23:53 (добавлен файл примера)

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365	#10 31.08.2020 16:53:29 Цитата t330 написал: ошибка для преобразовании типа данных где? По вашему примеру, если поменять имя таблицы на Таблица3_2, то всё прекрасно загружается. Правда, вы сделали sefl reference таблицу, ну, это уже не та проблема. Или вы хотели разобрать обработку ошибки, когда неверный источник?

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020	Все равно не срабатывает Вот видео на 1 минуту   https://radikal.ru/video/WUyGMi7CDdE Изменено: t330 — 31.08.2020 17:58:53

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020

#12 31.08.2020 22:03:43 Цитата Андрей VG написал: где? По вашему примеру, если поменять имя таблицы на Таблица3_2, то всё прекрасно загружается. Правда, вы сделали sefl reference таблицу, ну, это уже не та проблема.Или вы хотели разобрать обработку ошибки, когда неверный источник? Да , если в источнике заменить Таблица3 на Таблица3_2 , то якобы мой неправильный код сработает. На самом деле нет, так как в этом случае в самом источнике (Таблица3_2) в столбце Num просто не возникает никаких ошибок при изменении типа переменной с Text на int.64 , потому что в этом столбце только цифры и пустые ячейки. В общем, мой «обработчик» просто ничего не делает.

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365

#13 31.08.2020 22:07:56 Цитата t330 написал: не возникает никаких ошибок при изменении типа переменной с Text на int.64 Суть не в этом. ОБработка ошибок начинается только тогда, когда есть ошибка. Table.TransformColumnTypes разве возвращает ошибку? Функция возвращает таблицу. Но вот ошибки содержаться в некоторых или всех ячейках столбца, для которого был указан не верный тип данных. Следовательно, нужно отдельно проверять, содержит ли столбец какую-нибудь ошибку и на основании этого менять указание типа данных. Иначе никак.

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020

#14 01.09.2020 01:11:21 Цитата Андрей VG написал: Следовательно, нужно отдельно проверять, содержит ли столбец какую-нибудь ошибку и на основании этого менять указание типа данных. Иначе никак. Научите пожалуйста как это сделать? Вот такой код не работает И еще вы сказали, что можно сделать обработку ошибок , если к примеру у меня ошибка появляется на уровне слияния запросов из-за того, что например в одном запросе поменялись какие-то источники (поменялся состав столбцов в файлу одного запроса) … Не сочтите за наглость , но как это прописать? Код let     Источник = Excel.CurrentWorkbook(){[Name="Таблица3_2"]}[Content],     #"Измененный тип" = Table.TransformColumnTypes(Источник, {each try {"Num", Int64.Type} otherwise  {"Num", type text} }) in     #"Измененный тип"   Прикрепленные файлы ОбработчикОшибок.xlsx (13.81 КБ)

Андрей VG Пользователь Сообщений: 11878 Регистрация: 22.12.2012 Excel 2016, 365	#15 01.09.2020 06:51:47 Цитата t330 написал: Вот такой код не работает И не должен. Тут два пути, либо попытаться указать тип числовой и проверить столбец на ошибки, или проверить, что столбец содержит не целые числа (в примере просто числа). Прикрепленные файлы ОбработчикОшибок.xlsx (20.36 КБ)

t330 Пользователь Сообщений: 29 Регистрация: 30.01.2020	#16 02.09.2020 14:25:18 Цитата Андрей VG написал: И не должен. Тут два пути, либо попытаться указать тип числовой и проверить столбец на ошибки, или проверить, что столбец содержит не целые числа (в примере просто числа). Спасибо!