Обработка ошибок

Аналогично тому, как в Excel и языке DAX есть IFERROR функция, Power Query имеет собственный синтаксис для тестирования и перехвата ошибок.

Как упоминалось в статье об ошибках в Power Query, ошибки могут отображаться на шаге или на уровне ячейки. В этой статье рассматриваются способы перехвата ошибок и управления ими на основе собственной логики.

Замечание

Чтобы продемонстрировать эту концепцию, в этой статье в качестве источника данных используется книга Excel. Основные понятия, показанные здесь, применяются ко всем значениям в Power Query, а не только к тем, которые приходят из книги Excel.

Пример источника данных для этой демонстрации — книга Excel со следующей таблицей.

Эта таблица из книги Excel содержит ошибки Excel, такие как #NULL!, #REF!, и #DIV/0! в столбце "Стандартный тариф ". При импорте этой таблицы в редактор Power Query на следующем рисунке показано, как это выглядит.

Обратите внимание, что ошибки из книги Excel отображаются со значением [Error] в каждой из ячеек.

Из этой статьи вы узнаете, как заменить ошибку другим значением. Кроме всего прочего, вы узнаете, как поймать ошибку и использовать ее для вашей специфической логики.

Укажите альтернативное значение при поиске ошибок

В этом случае цель состоит в создании нового столбца "Окончательная ставка " в примере источника данных, который использует значения из столбца "Стандартная ставка ". При возникновении ошибок используется значение из соответствующего столбца "Специальная ставка ".

Чтобы создать новый настраиваемый столбец, перейдите в меню "Добавить столбец" и выберите "Настраиваемый столбец". В окне "Настраиваемый столбец " введите формулу try [Standard Rate] otherwise [Special Rate]. Присвойте этому новому столбцу окончательную ставку.

Эта формула пытается оценить столбец "Стандартная ставка " и выводит его значение, если ошибки не найдены. Если в столбце «Стандартная ставка» найдены ошибки, то выходные данные определяются значением, указанным после инструкции otherwise, которое в данном случае соответствует столбцу «Специальная ставка».

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

Замечание

В качестве альтернативного подхода можно также ввести формулу try [Standard Rate] catch ()=> [Special Rate], которая эквивалентна предыдущей, но с использованием ключевого слова catch в функции, не требующей параметров.

Ключевое catch слово было введено в Power Query в мае 2022 года.

Укажите собственную логику условной ошибки

Используя тот же образец источника данных, что и предыдущий раздел, новая цель — создать новый столбец для конечной ставки. Если значение из стандартной ставки существует, используется это значение. В противном случае используется значение из столбца "Специальный тариф ", за исключением строк с любой #REF! ошибкой.

Замечание

Единственной целью исключения #REF! ошибки является демонстрация. С помощью концепций, показанных в этой статье, вы можете выбрать любые поля из записи об ошибке.

При выборе любого пробела рядом со значением ошибки вы получите область сведений в нижней части экрана. Область сведений содержит как причину ошибки, DataFormat.Errorтак и сообщение об ошибке: Invalid cell value '#REF!'

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

Используйте try с пользовательской логикой

Чтобы создать новый настраиваемый столбец, перейдите в меню "Добавить столбец" и выберите "Настраиваемый столбец". В окне "Настраиваемый столбец " введите формулу try [Standard Rate]. Присвойте этому новому столбцу все ошибки.

Выражение try преобразует значения и ошибки в значение записи, указывающее, обрабатывает ли try выражение ошибку или нет, а также правильное значение или запись ошибки.

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

Эта операция предоставляет три новых поля:

• Все ошибки.HasError — показывает, было ли значение из столбца "Стандартный тариф " ошибкой или нет.
• Все ошибки.Значение—если значение из столбца Стандартная ставка не содержит ошибки, этот столбец отображает значение из столбца Стандартная ставка. Для значений с ошибками это поле недоступно, а во время операции развертывания этот столбец имеет null значения.
• Все ошибки.Error — если значение из столбца "Стандартная ставка " было ошибкой, в этом столбце отображается запись об ошибке для значения из столбца "Стандартная ставка ". Для значений без ошибок это поле недоступно, а во время операции развертывания этот столбец имеет null значения.

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

• Причина ошибки
• Сообщение об ошибке
• Сведения об ошибке

После выполнения операции развертывания в поле All Errors.Error.Message отображается определенное сообщение об ошибке, которое сообщает о том, какая ошибка Excel имеет каждую ячейку. Сообщение об ошибке формируется на основе поля "Сообщение об ошибке" в записи об ошибке.

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

• Если значение в столбце All Errors.Error.Message равно null, выходные данные — это значение из столбца "Стандартный тариф ".
• В противном случае, если значение в столбце All Errors.Error.Message не равно Invalid cell value '#REF!'., выходные данные — это значение из столбца "Специальный тариф ".
• В противном случае, null.

После сохранения только столбцов "Учетная запись", "Стандартная ставка", " Специальная ставка" и " Окончательная ставка" и добавления правильного типа данных для каждого столбца на следующем рисунке показано, как выглядит окончательная таблица.

Используйте try и catch с пользовательской логикой

Кроме того, можно создать новый настраиваемый столбец с помощью try ключевых слов и catch ключевых слов.

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

Связанный контент

• Общие сведения об ошибках в Power Query и работа с ними
• Добавление настраиваемого столбца в Power Query
• Добавление условного столбца в Power Query