Обработка ошибок
Аналогично тому, как в 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 мае 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.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
