Глава 12. Обработка ошибок и отладка

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Обработка ошибок в языке M включает в себя методы управления и устранения ошибок. Используя выражения и функции, такие как try, otherwise и catch, можно корректно обрабатывать потенциальные ошибки и эффективно управлять потоком выполнения запроса. Редактор Power Query содержит область Примененные шаги, которая позволяет выполнять код пошагово, облегчая выявление проблем в запросе.

Мои комментарии набраны с отступом.

Предыдущая глава       Содержание    Следующая глава

Скачать заметку в формате Word или pdf

В этой главе представлен обзор возможностей обработки ошибок и стратегий отладки в Power Query. Глава даст вам знания для эффективного устранения ошибок и повышения устойчивости запросов к возможным изменениям источника данных. Понимая типичные ошибки, возникающие в языке M, и применяя описанные здесь методы, вы создадите прочную основу для обработки ошибок и написания более надежных запросов.

В этой главе рассматриваются темы:

• Что такое ошибка?
• Сдерживание ошибок
• Обнаружение ошибок
• Вызов ошибок
• Обработка ошибок
• Стратегии отладки
• Распространенные ошибки

Чтобы извлечь максимум пользы, мы рекомендуем открыть редактор Power Query и опробовать примеры. Выполняя и изучая код, вы получите более глубокое понимание того, как обрабатывать ошибки и выполнять отладку. Скачайте PBIX-файл с репозитория GitHub.

Что такое ошибка?

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

При оценке выражения M возможны два исхода:

Возврат значения. Указывает на то, что вычисление прошло успешно, и выражение смогло сгенерировать результат.

Рис. 12.0. Деление на ноль не приводит к ошибке, а возвращает бесконечность

Сообщение об ошибке. Когда возникает ошибка, это означает, что при вычислении выражения не удалось получить значение. Сама ошибка не считается значением в языке M. Запись об ошибке содержит сведения о причине проблемы во время оценки. Это дает возможность определить операцию, ответственную за ошибку, и помогает в устранении неполадок.

Рис. 12.0а. Деление на операнд текстового типа возвращает Expression.Error

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

Сдерживание ошибок

Когда возникает ошибка, это приводит к тому, что оценка текущего члена выражения останавливается; фактически происходит «разворачивание» или «обратное движение» через ранее оцененные части выражения. Тем не менее, если ошибка может быть устранена, она не распространяется на выражение верхнего уровня, и значение все еще может быть получено.

Представьте, что у вас есть таблица, содержащая ячейку с ошибкой; эта ошибка может быть локализована в поле записи. Запись будет помечена как содержащая ошибку, и запись об ошибке будет сохранена, чтобы ее можно было распространить по мере необходимости. Это гарантирует, что при каждой последующей попытке доступа к этой ячейке будет возникать точно такая же ошибка. Рассмотрим следующий фрагмент кода с ошибкой:

Рис. 12.0б. Ошибка в поле записи A будет распространяться только при доступе к нему

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

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

Обнаружение ошибок

При работе в Power Query возникновение ошибок является естественной частью процесса разработки, независимо от того, создаете ли вы запросы через интерфейс, изменяете существующий код или пишете код M с нуля. Когда вы добавляете к коду данные реального мира, ошибки становятся частью жизни, с которой вам приходится иметь дело.

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

Наиболее распространенные ошибки на уровне шагов:

• Ошибки источника данных
• Неизвестные или отсутствующие идентификаторы
• Синтаксические ошибки
• Циклические ошибки

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

Рис. 12.1. Вкладка Просмотр; предварительный просмотр данных и профиль столбцов

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

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

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

Измените диапазон сканирования для профилирования столбцов в строке состояния с первых 1000 строк на сканирование всего набора данных:

Рис. 12.1а. Изменение диапазона сканирования для профилирования столбцов

Это изменение повлияет на обнаружение ошибок и производительность. В нашем примере количество ошибок увеличилось с 14 до 36. Это подчеркивает важность бдительности при проверке данных. На первый взгляд данные кажутся безошибочными, но во время выполнения запроса, вы можете столкнуться с непредвиденными сбоями.

Обнаружив ошибки на уровне ячеек, вы можете сохранить их, пройдя Главная –> Сократить строки –> Сохранить ошибки. При этом в интерфейсе вызывается функция Table.SelectRowsWithErrors, которая генерирует новую таблицу, содержащую только строки, содержащие хотя бы одну ошибку в любой из ячеек. Если вы предоставите список с названиями столбцов, функция ограничится проверкой на наличие ошибок в указанных столбцах. Используя этот подход, можно вывести проблемные строки для дальнейшего изучения.

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

Рис. 12.2. Предварительный просмотр деталей ошибки

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

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

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

Вызов ошибок

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

Выражение ошибки

Чтобы вызвать ошибку, напишите error и сообщение об ошибке в кавычках:

Ошибку также можно представить как запись, включающую дополнительные сведения. Запись об ошибке может содержать поля Reason, Message и Detail:

Вы также можете создать запись об ошибке из текстовых значений, используя библиотечную функцию Error.Record:

При этом возвращается та же запись об ошибке, что и в предыдущем примере.

Определение пользовательских ошибок в языке M было улучшено с введением структурированных сообщений об ошибках в середине 2022 г. Это обновление включало добавление двух новых полей в запись об ошибке: Message.Format и Message.Parameters.

Message.Format предоставляет структуру для создания объединенного сообщения об ошибке, которое отображается пользователю. В строке можно использовать пару одинарных кавычек для доступа к элементам из списка Message.Parameters в следующей форме: #{x}, где x – это отсчитываемый от нуля индекс для вставляемого значения параметра. Например, #{0} представляет собой первый элемент из списка Message.Parameters.

Message.Parameters – список, содержащий значения, из которых элементы могут быть запрошены и вставлены в строку сообщения об ошибке в указанных позициях. Не волнуйтесь, если в настоящее время вы не полностью понимаете следующий код. Вставьте код в новый запрос. Назовите запрос fxDivision:

Передача нуля в качестве второго аргумента приведет к ошибке:

Рис. 12.3. Пользовательское сообщение об ошибке

Структурированные поля сообщений об ошибках также предоставляют средства для быстрого и простого получения всей информации об ошибке без необходимости синтаксического анализа пользовательского текста. Входные значения могут быть получены из поля ошибки Detail и любой строки ввода в сообщении об ошибке из списка Message.Parameters, что может быть полезно для создания отчетов об ошибках и их анализа. Вот пример:

Вы можете вернуть любой элемент с позицией индекса, отсчитываемой от нуля. Например, при создании более сложных отчетов по обработке ошибок можно извлечь один или несколько элементов. При отображении входных значений в сообщениях об ошибках все примитивные значения будут отображаться в виде текста. Любые структурированные значения отображаются с использованием их литерального типа (table, record или list).

Если указаны оба поля ошибки (Message.Parameters и Message.Format), Message.Format имеет приоритет и используется для создания строки, которая будет перезаписывать Message.Parameters в распространяемой ошибке.

Указание Message.Format без Message.Parameters допустимо до тех пор, пока в предоставленной строке формата не ожидается ввода параметров.

Указание Message.Parameters без Message.Format вернет null вместо списка параметров в распространяемой ошибке.

Наконец, Message.Format и Message.Parameters не влияют на поля Reason и Detail.

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

Оператор … (многоточие, ellipsis)

Оператор многоточия (три точки) вызывает ошибку и предоставляет заранее определенное сообщение об ошибке. Текст сообщение зависит от версии движка M. Например, Expression.Error: Not Implemented или Expression.Error: Value was not specified. Вот пример того, как можно использовать оператор многоточия:

Моя версия движка вернула Expression.Error: Значение не задано.

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

Создание пользовательских ошибок предоставляет ряд возможностей: более информативные сообщения об ошибках, обработка исключительных случаев, применение правил проверки данных компании, улучшение общего взаимодействия с пользователем.

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

Стратегия обработки ошибок выходит за рамки лишь устранения возникших ошибок. Речь идет не только о выборе и использовании методов, доступных в языке M для эффективного устранения ошибок и обеспечения надежности процессов преобразования данных. При работе с данными часто возникают такие ошибки, как отсутствующие значения, несовместимые типы данных, а также неожиданные или недопустимые вычисления. Чтобы решить эти проблемы, следует рассмотреть все методы. Цель состоит в том, чтобы внедрить стратегию обработки ошибок, которая поможет предотвращать, управлять и устранять ошибки в месте их возникновения, обеспечивая предсказуемый результат. Вот что есть в наборе инструментов:

Оператор объединения с null (coalesce)

Язык M поддерживает распространение null – если значение равно null, результат будет null. Ниже поле a в myRecord иллюстрирует это. Такое поведение предотвращает ошибки, которые могут возникнуть при выполнении операций с null.

Рис. 12.3а. Объединение с null

Но когда null передается как аргумент функции, которая ожидает другой тип, null вызывает ошибку, как в поле c. Условная логика могла бы стать лекарством, но в главе 4 Общие сведения о значениях и выражениях вы узнали, что в языке M также есть оператор объединения, который представляет собой двойной вопросительный знак (??).

Оператор объединения работает с двумя значениями, возвращая левое, если оно не равно null, в противном случае возвращает правое. Можно формировать цепочки значений или выражений, разделяя их оператором объединения. Если все значения в цепочке равны null, возвращается значение null. Оператор объединения делает код более кратким и удобочитаемым:

Здесь цепочку составляют три значения: x, y и пустой список. Поскольку пустой список является первым значением, отличным от null, это значение возвращается в качестве аргумента List.Count.

Необязательный доступ к элементу или полю

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

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

То же самое верно и для доступа к полям:

Запись, к которой мы обращаемся, содержит 2 поля, ни одно из которых не имеет имени Three, и доступ к этому полю вызовет ошибку. Поскольку мы указали на необязательный доступ, выражение не вызовет ошибку, а вернет значение null.

Перечисление MissingField.Type

Когда вы видите необязательный missingField в списке параметров библиотечной функции, это означает, что функция позволяет контролировать свое поведение при обнаружении отсутствующих полей записи или столбцов таблицы. По умолчанию все указанные поля или столбцы являются обязательными, а любые отсутствующие вызовут ошибку. Однако функции с параметром MissingField.Type предлагают гибкое решение для обработки таких ситуаций:

• Error (0 или null) – параметр по умолчанию, указывающий, что если какие-либо поля или столбцы отсутствуют, будет вызвана ошибка.
• Ignore (1). Когда выбран этот параметр, отсутствующие поля или столбцы игнорируются. Они не вызывают ошибку и исключаются из вывода.
• UseNull (2). При выборе этого параметра в выходные данные будут переданы значение null для отсутствующих полей или столбцов. Это позволяет сохранить полный набор данных с одновременным указанием на отсутствие конкретной информации.

Различные функции Record и Table включают перечисление MissingField.Type: Record.RemoveFields, Table.RemoveColumns, Record.RenameFields, Table.RenameColumns, Record.ReorderFields, Table.ReorderColumns, Record.SelectFields, Table.SelectColumns, Record.TransformFields и Table.FromRecords.

MissingField.Type позволяет эффективно справляться с потенциальными проблемами, переопределяя поведение по умолчанию при возникновении ошибок: игнорировать отсутствующие поля или использовать вместо них значения null.

Выражение if, условная логика

Вы можете использовать условный оператор if-then-else для проверки условий, прежде чем выполнять какие-либо вычисление, предотвращая возможные ошибки:

Условная логика позволяет создать ветвь в коде.

Выражение try

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

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

try и otherwise

Конструкция try-otherwise пытается выполнить операцию и предоставит выражение по умолчанию в случае возникновения ошибки:

Только когда выражение try возвращает ошибку, вычисляется выражение по умолчанию. Это помогает изящно обрабатывать ошибки в лаконичной и удобочитаемой форме.

try и catch

Такой подход упрощает обработку ошибок, потому что в отличие от других вариантов, функция catch имеет доступ к записи ошибки, возвращаемой try. Это устраняет потребность в синтаксическом анализе сообщения об ошибке и написании пользовательских функций.

Рис. 12.3б. Как работает try и catch

Когда ошибка возникает в myInput, она распространяется на newValue и, впоследствии, на catchMyError. Если функция catch принимает параметр, значение ошибки будет передано в качестве значения:

Рис. 12.3в. Функция catch с параметром е возвращает запись ошибки

Если функция catch вызывается с нулевым параметром, то не получает сведения об ошибке от try и в этом смысле эквивалентна конструкции try-otherwise. Этот подход применим лишь в том случае, когда все ошибки должны обрабатываться и устраняться одинаковым образом.

try и пользовательская функция

Этот метод устарел с введением ключевого слова catch в язык M в июне 2022 года. Пользовательская функция все еще может использоваться, но функция catch с одним параметром стал основным методом обработки ошибок.

Стандартные библиотечные функции

Язык M также предоставляет две стандартные библиотечные функции, которые помогают в обработке ошибок. Table.ReplaceErrorValues заменяет значения ошибок в указанных столбцах таблицы на новое значение из списка списков errorReplacement. Формат этого списка списков: { {column1, value1}, … }. Допускается только одно значение замены для каждого столбца. Указание столбца более одного раза в списке errorReplacement приведет к ошибке.

Table.RemoveRowsWithErrors возвращает таблицу путем удаления строк, в которых хотя бы одна ячейка содержит значение ошибки. Функции можно передать необязательный второй параметр – список с именами столбцов. Тогда она проверит наличие ошибок только в ячейках в указанных столбцах.

Стратегии отладки

Отладка является неотъемлемой частью процесса разработки. Эффективные стратегии отладки играют важную роль в выявлении и исправлении ошибок, а также в улучшении понимания кода. Общие рекомендации по отладке кода M включают использование комментариев, переименование шагов, разбивку сложных выражений и тестирование.

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

По умолчанию Power Query назначает универсальные имена каждому шагу. Эти названия не дают большого представления о том, что делает шаг. Переименовывая шаги во что-то более описательное, вы делаете код удобным для чтения и понимания. Описательные имена шагов упрощают поддержку и обновление кода. Если в будущем вам потребуется изменить запрос, гораздо проще найти шаги, если они хорошо названы.

Разбивайте сложные выражения на более мелкие, удобные для управления части. Язык M, благодаря функциональной природе, позволяет создавать сложные вложенные выражения. Хотя эти выражения могут быть мощными, их также может быть сложно отладить, если что-то идет не так. Разбивая сложные выражения, можно изолировать и исследовать каждую часть отдельно, что упрощает выявление проблем. Такой подход упрощает отладку, и повышает удобочитаемость и удобство обслуживания кода при привязке к переменным с правильными именами.

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

Наряду с этими общими рекомендациями существует мощная стратегия отладки, которая заслуживает внимания. Вы знакомы с концепцией выражений let и record. Эти конструкции используются для создания сложных выражений в языке M. Однако, когда дело доходит до решения проблем и отладки, запись гораздо удобнее.

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

Вы можете вернуть одно или нескольких полей с помощью проекции:

… или вернуть одно значение, применив доступ к полю:

Выражение записи особенно полезна при устранении неполадок со сложными или вложенными выражениями:

Рис. 12.4. Просмотр всех полей записи для легкой отладки

Одним взглядом можно увидеть все поля в записи. Мы видим три ошибки, а поля RoundUp и Times Two распространяют ошибку, вызванную One при делении текста на число. Такое представление облегчает отслеживание и исправление ошибки.

Распространенные ошибки

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

Синтаксические ошибки

… указывают на проблемы с шаблоном или структурой предоставленного выражения.

Рис. 12.4а. Отсутствует запятая в конце строки 3

Дополнительная запятая внутри списка в строке 2:

Сообщения о синтаксических ошибках иногда могут вводить в заблуждение. Отсутствующая закрывающая скобка возвращает Expression.SyntaxError: Ожидался токен ‘,’

Однако при добавлении запятой закрывающая скобка все еще отсутствует:

… а выражение завершится ошибкой Expression.SyntaxError: Ожидался токен Literal, а не более очевидной Expression.SyntaxError: «,» не может предшествовать ‘in’.

Дополнительная закрывающая фигурная скобка также возвращает ошибку Expression.SyntaxError: Ожидался токен ‘,’

Наиболее распространенными причинами ошибки Expression.SyntaxError являются дополнительные или отсутствующие запятые, круглые и фигурные скобки.

Работа с ошибками – первоочередная задача

Если какой-либо шаг приводит к ошибке в запросе, мы рекомендуем сначала устранить ее, прежде чем выполнять дальнейшие действия. Если оставить ошибку без внимания, она может повлиять на запрос. То, как реализован движок М может привести к тому, что ошибка будет игнорироваться такими функциями, как Table.RemoveRowsWithErrors. Например, в запросе ниже, где ошибки возникают из-за преобразования типов столбцов смешанного типа.

Здесь Table.SelectRows удаляет из таблицы пустые строки. Однако эта операция не устраняет ошибки на уровне ячеек, возникшие из-за преобразования несовместимых типов. Когда позже для удаления строк с ошибками применяется функция Table.RemoveRowsWithErrors, ошибки сохраняются, и результирующая таблица будет включать строку с ошибками:

Рис. 12.4б. Несмотря на то, что мы дали указание Power Query удалить все ошибки, ошибки остаются

Ключ к решению проблемы лежит в порядке операций; лучше сразу устранять ошибки. В редакторе Power Query перейдите в разделе Примененные шаги и кликните правой кнопкой мыши шаг RemoveErrors. Выберите Разместить перед. Вы измените порядок операций. Теперь Table.RemoveRowsWithErrors отработает до Table.SelectRows. В результате ошибки, вызванные приведением типов, устраняются корректно, и запрос возвращает одну допустимую строку данных без строки с ошибками.

DataSource.Error, не удалось найти источник

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

Неизвестный или отсутствующий идентификатор

Идентификатор – это имя, используемое для ссылки на значение: запрос, переменную (шаг) или имя поля записи. Идентификаторы можно разделить на обычные и идентификаторы в кавычках. Идентификаторы в кавычках позволяют использовать любую последовательность символов, включая ключевые слова, пробелы, операторы и знаки препинания, например, #»error logic». Обычные или обобщенные идентификаторы используются для именования полей и доступа к ним. Они не требуют обозначения в кавычках.

Когда вы видите сообщение об ошибке, что-то типа Имя ‘xxxxx’ не было распознано, убедитесь, что имя написано верно. Вы обратились к идентификатору, который не был найден. Выясните, где используется неизвестный идентификатор, и обновите его имя или имя в ссылке. Возможно потребуется создать переменную с этим именем, если она не существует, и присвоить ей значение. Когда речь идет о нижнем подчеркивании (_), пропущено ключевое слово each.

Неизвестная функция

Следующий код…

… возвращает Expression.Error: Имя "list.Max" не распознано. Убедитесь в том, что оно написано верно.

Частыми причинами являются орфографические ошибки и неправильный регистр. Имена функций M подчиняются последовательному шаблону, состоящему из категории функций, за которой следуют точка и имя. Когда имя состоит из составных слов, принято писать первую букву каждого добавленного слова с заглавной буквы. Например, функция для выбора строк в таблице, которая входит в категорию табличных функций и имеет имя SelectRows, обозначается Table.SelectRows. Знание этого соглашения помогает быстрее выявлять ошибки в именах функций.

Внедрение Intellisense в Power Query ознаменовало значительное улучшение процесса разработки для пользователей версий Power BI Desktop, Excel 365 и Excel 2019 (выпуск в сентябре 2018 г.). С Intellisense разработчики могут начать вводить имя функции или переменной, и им будет предоставлен раскрывающийся список потенциальных завершений.

С Intellisense связана одна проблема – дублирование. Например, при наборе list. для поиска List.Max Intellisense может автоматически завершить выбор пользователя – listList.Max. Есть два способа избежать этого:

1. Опустите точку при вводе функции, например: TableSelectR
2. Вводите только буквы, которые в названии функции являются прописными. Можно дополнительно ввести больше букв, например, tsrow для Table.SelectRows

Оба метода позволят получить желаемую функцию без дублирования.

Ссылка на неизвестный столбец

Когда вы сталкиваетесь с сообщением об ошибке, например Expression.Error: столбец "xxxxx" таблицы не найден., это указывает на то, что у вас есть жестко закодированная ссылка на столбец, которая не соответствует ни одному из имен столбцов, присутствующих в таблице.

Существует несколько причин возникновения этой ошибки:

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

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

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

Вы можете использовать иную стратегию для устранения этой ошибки. Рассмотрите возможность использования функций M, которые допускают необязательный параметр missingField. Он управляет поведением функции при обнаружении отсутствующего поля. Изучите возможность использования динамических ссылок на столбцы вместо жестко заданных имен. Используя динамические ссылки на столбцы, запросы адаптируются к изменениям в источнике данных.

Ссылка на неизвестное поле

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

Вы можете переключиться на необязательный доступ к полям, добавив один вопросительный знак после оператора доступа к полю или поиска, например: [Name2]? Выражение вернет значение null вместо возникновения ошибки, если поле не найдено в записи:

Чтобы устранить ошибку, следует внимательно просмотреть код и сверить ссылки на поля с фактическими именами полей в записи. Убедитесь, что имена полей присутствуют, написаны правильно и имеют правильный регистр.

Рассмотрите возможность использования функций M, которые допускают необязательный параметр missingField. Он управляет поведением функции при обнаружении отсутствующего поля, обеспечивая большую гибкость при обработке таких ситуаций. Изучите возможность использования динамических ссылок на поля вместо жестко закодированных имен. Используя динамические ссылки, можно адаптироваться к изменениям имен полей.

Недостаточно элементов в перечислении

Язык M использует индексацию с нуля для таблиц и списков. Узнайте больше о списках и доступе к элементам на основе позиции в главе 6 Структурированные значения. Как и доступ к полю или его поиск, доступ к элементам также выполняется как обязательный по умолчанию, то есть ожидается, что указанная позиция должна присутствовать.

Рассмотрим следующий запрос:

Функция List.Numbers генерирует список с 3 элементами {1, 2, 3}; применение доступа к элементам и передача значения 3 в качестве номера индекса пытается извлечь четвертый элемент из списка значений, вызвав ошибку Expression.Error: Элементов в перечислении было недостаточно для выполнения операции.

Вы можете переключиться на доступ к необязательным элементам, добавив один вопросительный знак после оператора доступа к элементам:

Выражение вернет значение null вместо того, чтобы вызывать ошибку, когда запрошенная позиция индекса отсутствует в списке. В качестве альтернативы вы можете использовать библиотечную функцию List.Range:

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

Ошибка Formula.Firewall

Эту ошибку возвращает брандмауэр конфиденциальности данных Power Query. Он предотвращает непреднамеренную утечку данных из Power Query между источниками, и выдает ошибку типа:

Formula.Firewall: запрос "Query1" (шаг "Источник") ссылается на другие запросы или шаги, поэтому он может не иметь прямого доступа к источнику данных. Пожалуйста, восстановите эту комбинацию данных.

Или:

Formula.Firewall: запрос "Query1" (шаг "Источник") – это доступ к источникам данных, которые имеют уровни конфиденциальности, которые нельзя использовать вместе. Пожалуйста, восстановите эту комбинацию данных.

Часто вы найдете два способа устранить эту ошибку без отключения брандмауэра. Вы можете объединить Query1 и Query2 в один запрос или превратить эти запросы в функцию с нулевым параметром, чтобы избежать ошибки Formula.Firewall. Последняя возможность доступна в Power BI Desktop и в Excel, но не в службе Power BI. Узнайте больше об ошибке Formula.Firewall в главе 15 Оптимизация производительности.

Expression.Error: Ключу не соответствует ни одна строка в таблице

Эта ошибка возникает, когда Power Query не может найти имя, которое он ищет. К распространенным причинам относятся неправильно написанное или измененное имя, недостаточные права учетной записи, или несколько учетных данных для одного источника данных, который не поддерживается в службе Power BI. Ошибка Expression.Error в следующем коде вызвана несовпадающими значениями ключей таблицы в выражении поиска:

Чтобы устранить ошибку в этом примере, необходимо изменить myTable на YourTable на шаге Navigation.

Эта ошибка часто возникает при использовании соединителя папок для объединения файлов. При использовании коннектора Folder все файлы обрабатываются на основе выбранного примера структуры файла. Например, если вы выберете файл Excel с таблицей с именем myData, все остальные файлы в папке будут рассматриваться как файлы Excel с таблицей myData. Если в структуре файлов есть вариации, и таблицы названы иначе, во время объединения данных может возникнуть ошибка Expression.Error. Чтобы избежать возникновения ошибок или потери данных, важно тщательно проверить структуру файлов и обеспечить согласованность, прежде чем объединять их с помощью этого метода.

Expression.Error: Ключ соответствовал более чем одной строке в таблице

Эта ошибка указывает, что операция поиска возвращает более одной строки. Ошибка возникает при использовании оператора доступа к элементу { } для поиска строки в таблице с ключевыми полями и значениями, указанными в квадратных скобках [ ]. Движок M (mashup) использует такой синтаксис только для таблиц с первичными ключами при навигации или детализации. Все ключевые поля таблицы и соответствующие значения полей перечислены для однозначной идентификации нужной строки. В следующем запросе ошибка Expression.Error вызвана тем, что не указаны значения для всех ключей в выражении поиска:

Чтобы устранить ошибку, необходимо включить поле Item и присвоить ему соответствующее значение на шаге Navigation:

Ошибка возникает при ручной правке кода, сгенерированного интерфейсом, или когда метод поиска вручную применяется к таблицам, в которых отсутствует достаточное количество значений для сопоставления ключей, или когда таблица может содержать повторяющиеся строки. Чтобы предотвратить эту ошибку, используйте функцию Table.Keys, чтобы проверить, есть ли у таблицы первичные ключи. Она вернет список с записью, содержащей два поля: Columns – список имен столбцов, формирующих первичные ключи для таблицы, если таковые имеются, и Primary – логическое значение. Убедитесь, что для каждого ключевого поля указано совпадающее значение, тогда ошибка не возникнет.

Expression.Error: При вычислении возникло переполнение стека. Продолжение невозможно

Ошибка переполнения стека возникает, когда при вычислении кода исчерпывается доступная память в стеке вызовов. Это может произойти из-за ошибки в коде, например из-за вычисления рекурсивной функции без какого-либо конечного условия:

Рекурсивные функции могут привести к переполнению стека, когда они многократно обращаются к самим себе без какого-либо надлежащего условия завершения. Чтобы устранить переполнение стека добавьте управляющую структуру. Например, проверьте, условие x < 100. Если условие истинно, функция вызывает себя рекурсивно с операцией x + 1 в качестве новых входных данных. В противном случае, если (x ≥ 100), функция возвращает значение x, завершая рекурсивные вызовы:

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

Кроме того, вы можете рассмотреть возможность замены рекурсии функциями List.Transform, List.Generate или List.Accumulate. Эти итеративные функции могут выполнять те же задачи, что и рекурсивные функции, но более эффективно использовать память, снижая риск ошибок переполнения стека. Не забудьте проверить условия завершения и оптимизировать код M для повышения производительности и надежности.

Собираем все вместе

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

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

Выбор столбцов

Типичная задача – выбор столбцов из таблицы. Его легко сделать в интерфейсе. Однако можете ли вы придумать метод, чтобы облегчить выбор столбцов с целью сделать этот процесс более удобным для пользователя, надежным (робастным) и стабильным?

Создание индивидуального решения

Мы проведем вас через процесс разработки кода и объясним каждую часть шаг за шагом. Затем мы приведем код полностью. Сначала сосредоточимся на удобстве для пользователя. Поскольку язык M чувствителен к регистру и в нем легко допустить орфографическую ошибку, рассмотрим, как мы можем предоставить список с номерами столбцов вместо списка с именами столбцов. Мы не хотим начинать нумерацию столбцов с нуля. Интуитивно мы привыкли начинать счет с единицы.

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

С учетом этих соображений мы определили три параметра пользовательской функции: таблица, над которой будет выполняться операция; список с позициями столбцов, с которыми будут выполняться действия (отсчитываемых от единицы); булево значение – переключатель для определения, следует ли сохранять или удалять указанные столбцы. Рассмотрим пример:

Явные параметры определяют входные данные функции и их тип данных. Использование явных параметров в пользовательских функциях является лучшей практикой, которая служит первой линией защиты, гарантируя, что функция принимает только аргументы указанного типа. Для параметра colPositions явное указание типа намеренно было опущено.

Для информирования пользователей о типах ожидаемых аргументов принято снабжать функцию документацией. Мы реализовали пользовательскую логику ошибок, чтобы предоставить конкретную информацию, когда передается аргумент, не соответствующий ожидаемому типу, а именно списку. Настраивая сообщение об ошибке, мы можем предложить содержательную обратную связь, направляя пользователя на то, как соответствовать типу аргумента:

Обработка необязательных параметров в пользовательских функциях имеет решающее значение для предотвращения того, чтобы значения null стали основной причиной ошибок. Если необязательный параметр опущен, по умолчанию он равен null. Для защиты от потенциальных проблем в строке 8 используется оператор объединения, чтобы гарантировать, что когда параметр colPositions равен null, он будет заменен пустым списком {}. Таким образом, переменная cols всегда имеет допустимое значение при передаче в List.Transform: либо список, предоставленный пользователем, либо пустой список.

Оператор объединения также используется для значения по умолчанию необязательного параметра keepOrDelete. Если пользователь не передаст аргумент для keepOrDelete, по умолчанию будет использован Keep. Функция должна быть более удобной и предсказуемой, избавляя пользователей от необходимости явно указывать значение, если они намерены сохранить указанные столбцы:

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

Прежде чем приступить к вычитанию, нам нужно убедиться, что тип данных элемента в списке colPositions будет числом. Для этого мы включим обработку ошибок для случаев, когда значение не может быть преобразовано в число. Используя конструкцию try-otherwise, мы попытаемся преобразовать каждый элемент списка в число. В случае успеха мы выполним вычитание единицы, чтобы приспособиться к индексу, отсчитываемому от нуля. Если какое-либо значение в списке не может быть преобразовано в число, мы перейдем к части otherwise, в которой мы указали значение null. Для простоты ошибочные входные данные игнорируются:

Чтобы сгенерировать список фактических имен столбцов на основе вводимых пользователем данных и в то же время гарантировать, что доступ к необязательным элементам не приведет к ошибкам, мы добавим три функции. Строим логику изнутри наружу: фильтруем список baseZero, оставляя только те значения, которые больше или равны нулю. Эта мера предосторожности гарантирует, что доступ к необязательным элементам никогда не вызовет ошибку, вызванную отрицательным индексом. Затем в List.Transform применяем доступ к необязательному элементу, чтобы получить соответствующее имя столбца из списка allCols. Наконец, удаляя все значения null из списка, мы можем определить количество оставшихся допустимых имен столбцов:

Выполнив эти шаги, мы можем с уверенностью генерировать выходное значение. Для этого мы:

• Используем два оператора if-then-else для управления потоком запроса. Внешнее условие if проверяет, остались ли в списке colNames допустимые имена столбцов.
• Если допустимых имен столбцов нет, функция возвращает входную таблицу как есть, без каких-либо изменений. Если есть допустимые имена столбцов, второй оператор if-then-else, основанный на значении inclOrExclCols, определяет, следует ли сохранить указанные столбцы или удалить их из входной таблицы.

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

Вот полный код M, включающий документацию:

В этом примере показан ряд методов предотвращения и обработки ошибок, иллюстрирующих их роль в задачах преобразования данных. Включив в него логику разработчика, пользовательские ошибки, объединение с null, необязательный доступ к элементам, операторы try-otherwise и if-then-else, мы создали мощный и удобный для пользователя метод выбора столбцов в таблице. Каждый из этих методов служит определенной цели, способствуя надежности функции.

Сообщение об ошибках на уровне ячеек

Обеспечение качества и достоверности данных является важнейшим этапом в процессе подготовки данных. Устранение ошибок на уровне ячеек важно для поддержания целостности наборов данных. Хотя Table.RemoveRowsWithErrors может показаться удобным вариантом для удаления строк с ошибками, она имеет и недостатки, особенно если вы не уверены, какие строки были удалены и каковы причины их удаления. Чтобы сохранить прозрачность данных, дать возможность принимать обоснованные решения и действовать при возникновении проблем, давайте рассмотрим создание отчета об ошибках для вашего запроса.

Создание индивидуального решения

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

Учитывая эти соображения, мы определили три входных параметра пользовательской функции: таблицу, с которой будет выполняться операция; необязательный список, содержащий имена ключевых столбцов, необязательный список, указывающий имена столбцов, которые должны быть проверены на наличие ошибок.

Чтобы гарантировать, что принимаются только допустимые аргументы, были объявлены явные параметры. Явное определение ожидаемых типов данных для каждого параметра добавляет уровень защиты, предотвращая прием несовместимых типов данных:

Если аргумент keyCols является необязательным, это обеспечивает пользователям гибкость, но также требует соответствующей обработки, когда значение не указано, так как при пропуске keyCols его значение равно null. Чтобы избежать ошибки несоответствия типов при передаче в качестве аргумента другой функции, ожидающей тип списка, используется объединения для замены null пустым списком.

Учитывая возможные орфографические ошибки в предоставленных пользователем keyCol, мы можем проверить их на соответствие именам столбцов во входной таблице (colNames). Таким образом, мы можем определить допустимые имена ключевых столбцов и сохранить их в новой переменной под названием validKeys. Этот этап проверки не только помогает предотвратить потенциальные ошибки, но и предоставляет пользователю список с неправильно написанными или отсутствующими именами ключевых столбцов в выходных данных. Переменная hasKeys возвращает true или false в зависимости от наличия допустимого ключевого столбца:

Тот же метод объединения и проверки используется для необязательного аргумента scanCols. Если scanCols опущен, по умолчанию он имеет значение null, а объединение с null вернет пустой список, гарантируя, что тип списка доступен для дальнейшей обработки.

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

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

Далее мы определяем список с зарезервированными именами столбцов. Другими словами, имена в списке reservedNames не должны присутствовать во входной таблице. Все обнаруженные нарушения будут сохранены в списке nameViolations. Это позволяет нам предоставлять обратную связь пользователю:

Теперь углубимся в суть нашего решения и создадим ключевой компонент при построении таблицы отчетов об ошибках, используя несколько операторов if-then-else:

• Во-первых, мы проверяем, существуют ли нарушения в именах столбцов. Если нет, мы продолжаем использовать вложенное выражение let для обработки двух различных сценариев на основе наличия допустимых ключей.
• Если допустимые ключи существуют, мы сохраняем все ошибки, содержащие строки из измененной входной таблицы.
• Если допустимые ключи отсутствуют, мы добавим столбец индекса строк, начинающийся с нуля, с именем rowIndex, в измененную входную таблицу перед вызовом функции SelectRowsWithErrors.

Выглядит это следующим образом:

Переменная t теперь содержит таблицу из строк с одной или более ошибками в ячейках. Однако наше внимание сосредоточено исключительно на самих ошибках. Мы добавим новый столбец с именем newCol с помощью Record.ToTable(_) для создания таблицы из двух столбцов в каждой строке. Первый столбец, Name, состоит из имен полей, а второй, Value, содержит значения полей. Теперь мы можем снова вызвать функцию Table.SelectRowsWithErrors, на этот раз нацеливаясь исключительно на столбец Value для извлечения ошибок.

Наша конечная цель – отчет об ошибке с указанием причины ошибки, сообщения и подробностей. Для этого мы создаем дополнительный столбец во вложенной таблице, используя конструкцию try-catch для извлечения соответствующей информации из значения ошибки, try [Value] catch (e) => e и сохраняем эту информацию в новом столбце с именем Errors.

Теперь, когда мы успешно извлекли необходимые данные, нет необходимости сохранять фактическое значение ошибки, хранящееся в столбце Value вложенной таблицы. Можно использовать проекцию для создания таблицы с меньшим числом столбцов: [[Name], [Errors]].

Наконец, мы оптимизируем внешнюю таблицу, опустив все столбцы, кроме newCol, и ключевого столбца rowIndex:

Оптимизировав столбцы в таблице, мы переходим к развертыванию вложенной таблицы в newCol, которая содержит столбцы Name и Errors. Эта операция извлечения приведет к расширению таблицы, что позволит нам предоставить значение записи во вновь добавленном столбце Errors.

Извлечение полей Reason, Message и Detail из значения записи в столбце Errors еще больше расширяет таблицу, предоставляя пользователям подробные сведения о каждой возникшей ошибке:

Нам нужно завершить последнее ветвление из первого оператора if-then-else. Если мы обнаружим, что существуют какие-либо нарушения имени столбца, мы вызовем пользовательскую ошибку. В сообщении об ошибке предложим пользователю переименовать перечисленные столбцы и повторить попытку:

В итоге функция выводит значение записи, содержащее четыре поля: число для счетчика ошибок, таблицу отчетов об ошибках Date, список с недопустимыми ключевыми столбцами и список со столбцами Invalid scan columns:

Полный код M, включая документацию:

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

Резюме

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

В следующей главе мы узнаем об основных функциях и операторах, которые позволяют выполнять итерации и рекурсию в языке М Power Query.