Прочитать на английском

Поделиться через


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

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

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

Примечание

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

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

Снимок экрана: пример данных из книги Excel, содержащей три ошибки.

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

Снимок экрана: пример таблицы, загруженной в Power Query.

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

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

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

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

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

Снимок экрана: диалоговое окно

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

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

Снимок экрана: таблица со стандартными ошибками скорости, замененной специальной ставкой в столбце

Примечание

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

В catch мае 2022 года в Power Query появилась ключевое слово.

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

Используя тот же образец источника данных, что и предыдущий раздел, новая цель — создать новый столбец для конечной ставки. Если значение из стандартной ставки существует, используется это значение. В противном случае используется значение из столбца "Специальный тариф ", за исключением строк с любой #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 , чтобы получить три компонента записи об ошибке:

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

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

Снимок экрана: отображаемые сообщения об ошибках.

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

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

Снимок экрана: диалоговое окно

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

Снимок экрана: окончательная таблица с оставшимися столбцами с соответствующими типами данных.

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

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

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

Диалоговое окно настраиваемого столбца с новой формулой, показывающей подход к синтаксису try и catch.

Дополнительные ресурсы