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. Наконец, вы должны загрузить эти данные в новый рабочий лист, щелкнув Главная > Закрыть и загрузить > Закрыть и загрузить.

Александр L Пользователь Сообщений: 414 Регистрация: 16.02.2015 Александр	Коллеги Всем привет , подскажите пжл возможно ли с помощь Try  в Power Query при добавлении доп столбца формулой прописать аналог EСЛИОШИБКА ? Мне просто необходимо чтобы при расчёте выводился ноль если происходит ошибка. Спасибо

PooHkrd Пользователь Сообщений: 6602 Регистрация: 22.02.2017 Excel x64 О365 / 2016 / Online / Power BI

#2 14.02.2019 13:37:57 try чего-то там otherwise что-то вместо ошибки так. В вашем случае нужно понимать что PQ деление на ноль не воспринимает как именно ошибку, для него это вполне существующее значение. Посему настоятельно керемендую перед делением осуществлять проверку знаменателя на равенство нулю: Код =if [#"Количество (в базовых единицах), короба"] * [Вложения] = 0 then 0 else [База]/([#"Количество (в базовых единицах), короба"] * [Вложения]) Изменено: PooHkrd — 14.02.2019 13:44:59 Вот горшок пустой, он предмет простой…

Александр L Пользователь Сообщений: 414 Регистрация: 16.02.2015 Александр	#3 14.02.2019 13:39:34 Код =try ([База]/([#"Количество (в базовых единицах), короба"]*[Вложения])) otherwise 0 Так у меня вроде так и прописано когда создаю  доп столбец но не работает Изменено: Александр L — 14.02.2019 13:40:20 (вставил формулу)

Максим Зеленский Пользователь Сообщений: 4646 Регистрация: 11.06.2014 Microsoft MVP	#4 14.02.2019 13:42:15 Проще всего так: Код =if ([#"Количество (в базовых единицах), короба"]*[Вложения]) = 0 then 0 else ([База]/([#"Количество (в базовых единицах), короба"]*[Вложения])) Еще можно — создать столбец =[#»Количество (в базовых единицах), короба»]*[Вложения] и использовать в формуле его, чтобы не считать два раза, а потом удалить. F1 творит чудеса

Максим Зеленский Пользователь Сообщений: 4646 Регистрация: 11.06.2014 Microsoft MVP	#5 14.02.2019 13:44:00 Цитата Александр L написал: у меня вроде так и прописано когда создаю  доп столбец но не работает Потому что деление на 0 это не совсем ошибка, которая стопорит запрос: 0/0 = NaN, 20/0 = Infinity -20/0 = -Infinity F1 творит чудеса

Александр L Пользователь Сообщений: 414 Регистрация: 16.02.2015 Александр	А вы вот как обошли я тоже пробовал через If но применял три условия и вот не получалось(((. Спасибо сейчас попробую на массиве этот метод.

	А ещё есть null, деление на который даёт null, а не ошибку и не упомянутое выше

Александр L Пользователь Сообщений: 414 Регистрация: 16.02.2015 Александр	#8 14.02.2019 13:48:21 да с null  я всегда пресекаю на начальном этапе)))))

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

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 и получаете ошибку «Перед использование этого соединителя необходимо установить один или несколько дополнительных компонентов»

Аналог функции 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»

=if List.AnyTrue    (       {[Inventory Item]=«Talkative Parrot»,[Sold By]=«Fred»}    )    then «Meets Criteria!»    else «No Match»

Не забудьте разделить критерии запятыми, а список критериев окружить фигурными скобками, потому что функция 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. Главная –> Расширенный редактор. Введите код:

(input) => let    values = {       {result_1, return_value_1},       {input, «Undefined»}    },    Result = List.First(List.Select(values, each _{0}=input)){1} in    Result

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

• 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

(input) => let    values = {       {«E», «Employee»},       {«S», «SCYC»},       {«N», «Non-Taxable»},       {«R», «Restricted»},       {«I», «Inactive»},       {«L», «Social»},       {«M», «Medical»},       {«U», «Regular»},       {input, «Undefined»}    },    Result = List.First(List.Select(values, each _{0}=input)){1} in    Result

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

Когда вы закончите вносить изменения в Расширенном редакторе, нажмите кнопку Готово. Главная –> Закрыть и загрузить. Теперь вы можете использовать функцию 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

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

(lookup_value as any, table_array as table, col_index_number as number, optional approximate_match as logical ) as any => let    /*Provide optional match if user didn’t */    matchtype =    if approximate_match = null    then true    else approximate_match,    /*Get name of return column */    Cols = Table.ColumnNames(table_array),    ColTable = Table.FromList(Cols, Splitter.SplitByNothing(), null,       null, ExtraValues.Error),    ColName_match = Record.Field(ColTable{0},«Column1»),    ColName_return = Record.Field(ColTable{col_index_number — 1},       «Column1»),    /*Find closest match */    SortData = Table.Sort(table_array,       {{ColName_match, Order.Descending}}),    RenameLookupCol =       Table.RenameColumns(SortData,{{ColName_match, «Lookup»}}),    RemoveExcess = Table.SelectRows(       RenameLookupCol, each [Lookup] <= lookup_value),    ClosestMatch=       if Table.IsEmpty(RemoveExcess)=true       then «#N/A»       else Record.Field(RemoveExcess{0},«Lookup»),    /*What should be returned in case of approximate match? */    ClosestReturn=       if Table.IsEmpty(RemoveExcess)=true       then «#N/A»       else Record.Field(RemoveExcess{0},ColName_return),    /*Modify result if we need an exact match */    Return =       if matchtype=true       then ClosestReturn       else          if lookup_value = ClosestMatch          then ClosestReturn          else «#N/A» in    Return

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

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 вместо ближайшего соответствия, если значение столбца подстановки не соответствует точно искомому значению.

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

If you haven’t read the first two posts (Part 1 | Part 2) in this series yet, I welcome you to do so before reading this one.

I also recommend that you check out this post on Query Error Auditing so you get a better understanding of what types of errors you can find in Power BI / Power Query.

This is a post on how to use error handling, similar to an IFERROR in DAX and Excel, but for Power Query (using its M language).

How does Error handling works in Excel & DAX?

In Excel and in DAX we have the IFERROR function which works like this:

=IFERROR( value, value_if_error)

Taken directly from the official DAX documentation:

Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself.

It’s a pretty simple and straightforward function in DAX and Excel, where you can enter your formula in the “value” parameter and then, if you get an error from it, you can define what should be the output value in the “value_if_error” parameter.

The whole idea is that you can “catch” an error and use a different value when it finds an error.

How does Error handling works in Power BI / Power Query?

In Power Query the code is a bit different. Let’s see it in action and then talk more about it.

Imagine that we have an Excel workbook with a table like this

:

What we would like to create is a new column that should multiply the values from the [Price] and [Amount] columns to create a new Subtotal column.

One caveat, as you can probably see, is that this spreadsheet has some cells with errors on the [Price] column. In the event that we find an error on the Price column, we need to use the value from the [List Price] instead of the [Price] value.

The first thing that we need to do is import that table from Excel. If you’d like to follow along, you can download the workbook by clicking the button below:

Importing data from the Excel Workbook

I’ll be using Power BI Desktop for this, but you can use Excel as well.

The first thing that we need to do is select the Excel connector and connect to our file:

and once you get the “Navigator” window, you can select the table that reads “Sample”:

Notice how there’s a bunch of errors in that [Price] column just in the preview. Let’s hit the “Edit” button so we can go to the Power Query Editor Window.

Using error handling in Power BI /Power Query

Now that we have our data in the Power Query Editor window:

what we want to do is create a Custom Column, so we simply go to the “Add Column” menu and hit on “Custom Column”.

In there, we try do create a simple column that will multiply the [Price] column by the [Amount] column:

and as you can see, our [Subtotal] column has some errors.

We know that in Excel and DAX you can use IFERROR, but what can you use in Power Query ?

For Power Query, we need to hit modify that Custom Column code (just click the gear icon next to the Added Custom step) and add the following pieces to it:

try [Price]*[Amount] otherwise [Amount]*[List Price]

We need to use the keywords “try” and “otherwise”. It’s pretty easy to read, but it just says to try and evaluate the expression ([Price] * [Amount]) and if that gives an error, use the expression defined after the otherwise statement.

The result of that will look like this:

pretty simple! almost as simple as the IFERROR function in DAX and Excel where intellisense does explain you a bit how to use that function, but in Power Query you need to understand how this works in order to use it. Is nowhere in the User Interface of Power Query, so you need to write this code manually.

Understanding Errors

The workbook sample that I’m using is fairly simple. I’ve had experiences where some users / customers absolutely need to know when a specific error is found from an Excel Workbook.

What happens with Power Query is that it just flags any errors found as “Error” but, what if you needed to know WHY it shows as an error?

Let’s go back to our initial load of the file. Remember that in most cases Power Query will automatically try to add a “Changed Type” step, so what if we remove that step?

Well, I removed the step and I’m still seeing the errors and that’s because the error wasn’t triggered by a data type conversion, but rather it’s a source error, meaning that the error comes directly from the Excel Workbook.

In Workbook with vast amounts of rows, it’s hard to tell if there are any errors at all and doing a “Replace Errors” will not tell us why those errors occurred. We NEED to know what is the error from the source because we want to handle each type of error differently.

Error Message and Error Reason

To figure out what’s the reason why there’s an error, we need to use the “try” statement again.

Note how I only use “try” and not the “otherwise” statement. This will give me a new column with record values. We can expand those records like this:

the most important field from those records it’s the “Error” field which can be either a null or a record value:

and after expanding that column and deleting some others that we don’t need, I end up with this:

I’ve highlighted the most important field after this whole process which is the “Message” which tells me exactly the reason why this is an error.

I can later use this to my advantage and target specific errors differently or get a report of ALL the errors found on a series of files that my department / group uses. This is extremely helpful if you’re trying to validate everything and make sure that we don’t have any errors at the source.

Don’t forget that these same principles work for both Step and cell Value level errors.