Power query как найти ошибку

Tags:
Power BI, Power Query

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

Пример набора данных

Мы будем использовать пример файла Excel в качестве источника данных, который содержит 18 484 строки клиентов. В образце Dataset у нас есть поле BirthDate рядом со всеми другими полями, которые должны иметь в нем значение даты. Вот как выглядят данные, когда мы вводим их в Power Query:

 

Происходит ошибка

Когда мы получаем этот набор данных в окне редактора Power Query Editor (как показано на приведенном выше снимке экрана), Power Query автоматически преобразует тип данных столбца BirthDate в Date. Вы можете увидеть это автоматическое преобразование типа данных в списке шагов;
 
Конечно, вы можете отключить автоматическое определение типа данных Power Query, но наша точка зрения отличается. Мы хотим, чтобы набор данных не показывал вам, как с этим бороться. Ошибки происходят в Power Query в реальном мире, и мы хотим показать вам, как их найти.

Как вы можете видеть в редакторе Power Query Editor, мы не видим ошибок для этого типа данных, и все выглядит великолепно;

 

Теперь мы загружаем этот набор данных в Power BI, используя Close и Apply в окне редактора запросов, и мы ждем, что все загрузится успешно, однако это выходит из-под контроля!

 

Звучит знакомо? Да, если вы некоторое время работали с Power BI, возможно, вы это испытали. В редакторе Power Query Editor нет ошибок, но когда мы загружаем данные в Power BI, они появляются ! Как это возможно? Давайте сначала узнаем, почему это происходит.

Почему Power Query Editor не поймал ошибку?

Редактор Power Query Editor всегда работает с предварительным просмотром набора данных, размер предварительного просмотра зависит от того, сколько столбцов у вас есть, иногда это 1000 строк, а иногда и 200 строк. Если вы нажмете на Query в окне редактора Power Query, вы можете увидеть это, как показано ниже в строке состояния;

 

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

Как тогда преобразование будет применено ко всему набору данных? Когда вы загружаете данные в Power BI, а именно — когда вы нажимаете «Close» и «APPLY» в окне Power Query Editor. Этот APPLY означает применить эти преобразования теперь во всем наборе данных. Именно по этой причине процесс загрузки может занять больше времени, особенно если набор данных большой.

Power Query Editor всегда работает с предварительным просмотром данных, чтобы ускорить процесс разработки. Когда вы загружаете данные в Power BI, преобразования будут применяться ко всему набору данных.

Теперь, когда вы знаете, как Power Query Editor имеет дело с предварительным просмотром данных, вы можете догадаться, почему произошла ошибка выше? Причина в том, что предварительный просмотр данных (около 1000 строк) не имел проблем с применяемыми преобразованиями (в этом случае автоматический тип данных изменяется на Date для столбца BirthDate). Однако весь набор данных (около 18 тыс. строк) имеет проблемы с этим преобразованием! Когда вы увидите вышеприведенную ошибку в Power BI Desktop, вы можете нажать View errors и перейти в  Power Query editor, посмотреть их, разобраться с ними и исправить. Однако этого недостаточно.

Что делать, если ошибка не возникает в Power BI Desktop, но происходит в запланированном обновлении в службе Power BI?

Это хороший вопрос! Исправить ошибки в Power BI Desktop легко, но учтите, что ошибка также не произошла в Desktop, и вы опубликовали отчет Power BI на веб-сайт и запланировали его обновление. Затем на следующий день вы увидите, что отчет не обновился с ошибкой! Вы должны научиться правильно обращаться к строкам ошибок до того, как это приведет к сбою запланированного обновления. Давайте посмотрим, как с этим справиться.

Работа с ошибками: поиск строк ошибок

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

 

На скриншоте выше, мы переименовали таблицу DimCustomer в DimCustomer — Original, а затем создали ссылку из нее. Если вы хотите узнать, что такое Reference, прочитайте статью о Reference и Duplicate здесь. Новый запрошенный запрос можно назвать DimCustomer. Это будет чистый запрос без ошибок (мы удалим ошибки из него на следующем шаге);

 

Новая таблица — это таблица, которая будет чистой, без ошибок, и мы можем использовать ее в отчете. Давайте очистим это от любых ошибок

Удаление ошибок из загрузки таблицы в Power BI

Поскольку DimCustomer станет для нас окончательным запросом, я хочу удалить из него ошибки. Удаление ошибок — это простой вариант на вкладке «Главная» в разделе  Reduce Rows -> Remove Rows -> Remove Errors.. Перед этим выберите столбец BirthDate.

 

Вы также можете сделать это для всех столбцов, если хотите; выбрав все столбцы, а затем выбрав «Remove Errors». Это сообщение — всего лишь образец одного столбца и может быть продлен до конца.

Remove Errors — это шаг на этапе преобразования данных, а это означает, что при нажатии APPLY он будет применяться ко всему набору данных, поэтому в результате, когда изменение типа данных приведет к ошибке, следующий шаг после этого — Remove Errors, уничтожит строки, вызвавшие ошибку. Но DimCustomer — Original все еще может вызвать ошибку, поэтому мы должны снять галочку Enable Load с этого запроса.

 

Теперь мы успешно удалили ошибки и загрузили данные в Power BI. Ошибок не будет.

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

Храните ошибки в таблице исключений

Аналогично опции «Remove Errors» есть опция «Keep Errors». Если вы уже видели этот вариант, возможно, вам интересно, как его использовать? Вот точный сценарий использования. Keep Errors поможет уловить строки ошибок в таблице исключений.

Создайте еще одну ссылку из DimCustomer — Original.

 

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

 

Теперь эта таблица будет содержать строки, которые вызывают ошибку. Вот пример набора;

 

Это еще не конец истории. Если вы загрузите эту новую таблицу DimCustomer — строки ошибок в Power BI, вы снова получите ту же ошибку. Зачем? ну, потому что этот запрос, безусловно, собирается возвращать строки ошибок! Вам необходимо удалить ошибку из этого набора данных.

Получение информации об ошибке

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

В таблице  Error Rows добавьте Custom Column.

 

В редакторе Custom Column напишите «try», а затем пробел, имя поля, вызвавшего ошибку. В нашем примере: BirthDate;

try [BirthDate]

 

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

 

Выход записи «try» будет иметь два поля; HasError (мы уже знаем, что это будет правда) и Error. Ошибка — это еще одна запись с более подробной информацией. Нажмите «Expand » в столбце «Custom column» и выберите «Error».

 

В столбце вывода с именем «Error» снова нажмите на «Expand» и на этот раз выберите все столбцы;

 

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

Теперь вы получите полную информацию об ошибке, как показано ниже;

 

Вышеупомянутая информация является вашим самым ценным активом для отчетности об исключениях.

Удалите столбец ошибок

Теперь последний шаг перед загрузкой данных в Power BI — удалить столбец, который вызывает ошибку. В нашем примере; Столбец BirthDate должен быть удален (в противном случае обновление снова завершится неудачей);

 

Отчет об исключении

Теперь вы можете загрузить данные в Power BI. У вас будет две таблицы; DimCustomer и DimCustomer – Error Rows. DimCustomer — это таблица, которую вы можете использовать для обычной отчетности. DimCustomer – Error Rows — это таблица, которую вы можете использовать для отчетов об исключениях. Отчет об исключении — это отчет, который можно использовать для устранения неполадок, и перечисляет все ошибки для дальнейшего расследования. Убедитесь, что между этими двумя таблицами нет никакой связи.

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

 

Подведем итоги

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

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

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

Предыдущая заметка     Следующая заметка

Рис. 1. Три поля записи error; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

Сообщение об ошибке

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

Листинг 1[2]

= error [ Reason = «Business Rule Violated», Message = «Item codes must start with a letter», Detail = «Non-conforming Item Code: 456» ]

Все три поля являются необязательными. Если поле Reason отсутствует, причина ошибки будет иметь значение по умолчанию – Expression.Error. Запись ошибки можно также создать с помощью функции Error.Record. В отличие от описанного выше подхода, в Error.Record атрибут Reason является обязательным.

Листинг 2

= error Error.Record( «Business Rule Violated», «Item codes must start with a letter», «Non-conforming Item Code: 456» )

Оба приведенных выше примера приводят к эквивалентной ошибке, изображенной на рис. 1 Глядя на рисунок, видно, как три поля/параметра соотносятся с отображаемым сообщением.

Вместо записи error также может принимать строку. Результирующее сообщение об ошибке будет иметь значение предоставленной строки, а его причина – значение Expression.Error.

Листинг 3

Рис. 2. Строка в error

Ярлык с многоточием

Существует также оператор быстрого доступа для создания ошибок, который пригодится во время разработки. Допустим, вы хотите протестировать запрос, часть кода которого еще не написана. Поскольку каждое выражение должно возвращать значение, или вызывать ошибку, вы не можете протестировать свой запрос, не поместив что-то в качестве заполнителя в нереализованные участки кода. Используйте оператор многоточия (…). При вызове … выдает ошибку Expression.Error: Значение не задано. Вот фрагмент кода, в котором не реализована ветвь else:

Листинг 4

let a = 6, Result = if a = 5 then true else ... in Result

Когда условие (а = 5) принимает значение false, вызывается «…», что приводит к ошибке. Обратите внимание, ключевое слово error не используется. Оператор многоточия как определяет, так и вызывает ошибку.

Особое поведение

Что именно происходит, когда возникает ошибка? Какое поведение возвращает ошибку, а не значение? Рассмотрим выражение:

В обычных условиях сначала выполняется функция GetValue(). Затем полученное значение передается в someFunction(), которая возвращает финальный результат. Предположим, GetValue() выдает ошибку. Дальнейшее выполнение выражения прекращается. someFunction() не вызывается. Ошибка GetValue() становится итогом выражения. Такое поведение также известно, как повышение. Ошибка передается тому шагу, с которого была вызвана someFunction().

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

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

Если ошибка возникает в выражении, которое что-то определяет (поле записи, ячейку таблицы, переменную в выражении let, …), ошибка содержится в этом чём-то. Последствия ошибки ограничены этим чем-то и логикой, которая пытается получить доступ к значению этого чего-то. Ниже последствия ошибки GetValue содержатся в той части запроса, на которую она повлияла. Ошибка не остановила выполнение запроса. Запрос завершился успешно и вернул запись. Два поля – FieldB и FieldC – вернули ошибку, потому что они являются чем-то, затронутым ошибкой.

Листинг 5

let GetValue = () => error «Something bad happened!», DoSomething = (input) => input + 1, Result = [ FieldA = 25, FieldB = DoSomething(GetValue), FieldC = FieldA + FieldB ] in Result

Рис. 3. Результат запроса

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

Ниже функция GetDataFromWebService() вычисляется один раз, даже если к самим данным обращаются дважды. Если первое обращение вернуло ошибку, второе обращение тоже вернет  ошибку, сохраненную ранее.

let Data = GetDataFromWebService() // повышенная ошибка in { List.Sum(Data[Amount]), List.Max(Data[TransactionDate]) }

Ошибки верхнего уровня

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

Листинг 6

let GetValue= () => error «Something bad happened!», SomeFunction = (input) => input + 1 in SomeFunction(GetValue())

Сдерживание против исключения

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

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

На самом деле, из-за лени M, если к ячейке с ошибкой не будет обращений, то ошибка и не возникнет.

Листинг 7

let Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }}) in Table.RowCount(Data) // возвращает 2

Хотя одна ячейка и содержит ошибку, запрошенные данные (количество строк), не требуют вычисления значения ошибочной ячейки, поэтому выражение вернет значение 2.

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

Листинг 8

let Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }}) in [ RowsWithErrors = Table.RowCount(Table.SelectRowsWithErrors(Data)), RowsWithoutErrors = Table.RowCount(Table.RemoveRowsWithErrors(Data)) ]

Рис. 4. Список, содержащий количество строк с ошибками и без

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

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

try ExpressionToTry otherwise FallbackExpression

… первый, try with otherwise, пытается выполнить ExpressionToTry. Если это выражение возвращает значение, всё Ok, переходим к следующему шагу запроса. Когда выражение выдает ошибку, вычисляется выражение otherwise и возвращается его значение.

try Number.FromText(input) otherwise 0

Если Number.FromText возвращает значение, оно и будет результатом выражения. Когда Number.FromText выдает ошибку, try обращается к части otherwise, и возвращает 0. Другими словами, если входные данные могут быть преобразованы в число, возвращается это число; в противном случае возвращается значение по умолчанию – 0.

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

try GetFromPrimary() otherwise try GetFromSecondary() otherwise «Возникли проблемы с обоими серверами. Возьми отгул на остаток дня :)»

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

Эта форма всегда возвращает запись. Если выражение завершилось успешно, эта запись имеет вид:

[ HasError = false, Value = (значение выражения ExpressionToTry) ]

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

[ HasError = true, Error = (запись, описывающая возникшую ошибку) ]

Например:

Листинг 9

let DoSomething = () => 45, Result = try DoSomething() in Result // [HasError = false, Value = 45]

Рис. 5. Запись, возвращаемая try, если нет ошибки

Листинг 10

let DoSomething = () => error «bad», Result = try DoSomething() in Result // [HasError = true, Error = [Reason = «Expression.Error», Message = «bad», Details = null]

Рис. 6. Запись, возвращаемая try, если есть ошибка

Запись, помещенная в поле Error, содержит ровно три поля: Reason, Message и Details.[3] Это верно, даже если в записи, первоначально использовавшейся для определения ошибки, отсутствовало одно или несколько из этих полей (помните, они необязательны при определении ошибки), или если она включала дополнительные поля.

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

let Primary = try GetDataFromPrimary(), Source = if Primary[HasError] = false then Primary[Value]     /* если Primary возвращает значение, используй его */ else if Primary[Error][Reason] = «External Source Error» and Primary[Error][Message] = «Server is unreachable»     then GetDataFromSecondary()     /* если ошибка Primary вызвана тем, что его источник недоступен,             запроси данные с сервера Secondary */ else error Primary[Error]     /* если Primary вернул иную ошибку, верни её             в качестве результата запроса */ in Source

Используя try with otherwise, мы бы запросили Secondary, если Primary выдаст любую ошибку, а не только когда основной сервер недоступен:

try GetDataFromPrimary() otherwise GetDataFromSecondary()

Масштаб (область действия)

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

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}) in try Data otherwise 0

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

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

Листинг 11

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}) in try List.Sum(Data[Amount]) otherwise 0

List.Sum суммирует значения в столбце [Amount] таблицы Data. Если выражение, определяющее значение элемента, вызывает ошибку, она повышается. List.Sum прекращает суммирование и возвращает ошибку. try обрабатывает эту ошибку, возвращая 0 вместо суммы элементов списка. Скорее всего, цель у разработчика была иная. Он хотел заменить элементы с ошибками на 0, и суммировать числовые значения. Необходимо применить try так, чтобы обработка ошибок велась на уровне ячеек таблицы. Кажется, что можно сделать так:

Table.TransformColumns(Data, (input) => try input otherwise 0)

Однако, эта логика не улавливает ошибки, вызванные выражениями значений ячеек. Дело в том, что аргументы вычисляются до того, как их значения будут переданы в функцию. Если оценка приводит к ошибке, функция не вызывается. Вместо этого ошибка передается шагу, который вызвал функцию. В нашем случае, если выражение значения столбца выдает ошибку, функция преобразования (input) => … не вызывается, поэтому try не может обработать ошибку. Вместо этого ошибка передается обратно в Table.TransformColumns.

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

Листинг 12

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}), ErrorsReplacedWithZero = Table.AddColumn( Data, «NewAmount», (row) => try row[Amount] otherwise 0 ), RemoveOldAmount = Table.RemoveColumns(ErrorsReplacedWithZero, {«Amount»}), RenameNewAmount = Table.RenameColumns(RemoveOldAmount, {«NewAmount», «Amount»}) in List.Sum(RenameNewAmount[Amount]) // возвращает 10

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

Листинг 13

let Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}), ErrorsReplacedWithZero = Table.ReplaceErrorValues( Data, {{«Amount», 0}} ) // заменяет ошибки в столбце Amount нулями in List.Sum(ErrorsReplacedWithZero[Amount]) // возвращает 10

Применить try к элементам списка сложнее. Для списков нет функции List.ReplaceErrorValues. Самым простым решением может быть преобразование списка в таблицу, обработка ошибки, а затем обратное преобразование таблицы в список.

Листинг 14

let Data = {10, error «help!», error «save me!»}, #»Преобразовано в таблицу» = Table.FromValue(Data), #»Замененные ошибки» = Table.ReplaceErrorValues(#»Преобразовано в таблицу», {{«Value», 0}}), Value = #»Замененные ошибки»[Value], #»Вычисленная сумма» = List.Sum(Value) in #»Вычисленная сумма»

Рис. 7. Сумма элементов списка, содержащего ошибки

Нарушения правил

Вы можете использовать ошибки, как сигнал о несоответствиях. Допустим, вы обрабатываете CSV-файл, где значения в столбце ItemCode должны начинаться с буквы A. В начале запроса вы проверяете это, заменяя несоответствующие значения ошибками. Последующие этапы обработки, которые обращаются к столбцу, будут предупреждены, если они попытаются работать со значениями, нарушающими правила.

let Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}}) Validated = Table.TransformColumns( Data, { «ItemCode», each if Text.StartsWith(_, «A») then _ else error Error.Record(       «Invalid Data», «ItemCode does not start with expected letter», _ ) } ) in Validated

Такую проверку полезно применить в базовом запросе, на который будут ссылаться несколько других запросов. Это позволит вам выполнить проверку один раз (вспомните принцип Не повторяйся, Don’t repeat yourself), гарантируя, что пользователи, пытающиеся использовать ошибочные данные, будут предупреждены о наличии аномалий.

Другой вариант – добавить столбец со значениями true и false, в зависимости от того, соблюдается ли правило:

let Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}}) Validated = Table.AddColumn( Data, «ValidItemCode», each Text.StartsWith(_[ItemCode], «A»), type logical ) in Validated

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

В следующей заметке

Я планирую рассказать о том, что остается за кулисами: организации разделов кода и о том, как M предоставляет возможность аннотировать значения дополнительной информацией (метаданными). Однако перед этим обсудим, как работает система типов в Power Query.

[1] Заметка написана на основе статьи Ben Gribaudo. Power Query M Primer (Part 15): Error Handling. Если вы впервые сталкиваетесь с Power Query, рекомендую начать с Марк Мур. Power Query.

[2] Номер листинга соответствует номеру запроса в приложенном Excel файле.

[3] Судя по рис. 6 современная реализация M создает пять полей.

SPBDEV Blog

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

Пример набора данных

Мы будем использовать пример файла Excel в качестве источника данных, который содержит 18 484 строки клиентов. В образце Dataset у нас есть поле BirthDate рядом со всеми другими полями, которые должны иметь в нем значение даты. Вот как выглядят данные, когда мы вводим их в Power Query:

Происходит ошибка

Когда мы получаем этот набор данных в окне редактора Power Query Editor (как показано на приведенном выше снимке экрана), Power Query автоматически преобразует тип данных столбца BirthDate в Date. Вы можете увидеть это автоматическое преобразование типа данных в списке шагов;

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

Как вы можете видеть в редакторе Power Query Editor, мы не видим ошибок для этого типа данных, и все выглядит великолепно;

Теперь мы загружаем этот набор данных в Power BI, используя Close и Apply в окне редактора запросов, и мы ждем, что все загрузится успешно, однако это выходит из-под контроля!

Звучит знакомо? Да, если вы некоторое время работали с Power BI, возможно, вы это испытали. В редакторе Power Query Editor нет ошибок, но когда мы загружаем данные в Power BI, они появляются ! Как это возможно? Давайте сначала узнаем, почему это происходит.

Почему Power Query Editor не поймал ошибку?

Редактор Power Query Editor всегда работает с предварительным просмотром набора данных, размер предварительного просмотра зависит от того, сколько столбцов у вас есть, иногда это 1000 строк, а иногда и 200 строк. Если вы нажмете на Query в окне редактора Power Query, вы можете увидеть это, как показано ниже в строке состояния;

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

Как тогда преобразование будет применено ко всему набору данных? Когда вы загружаете данные в Power BI, а именно — когда вы нажимаете «Close» и «APPLY» в окне Power Query Editor. Этот APPLY означает применить эти преобразования теперь во всем наборе данных. Именно по этой причине процесс загрузки может занять больше времени, особенно если набор данных большой.

Power Query Editor всегда работает с предварительным просмотром данных, чтобы ускорить процесс разработки. Когда вы загружаете данные в Power BI, преобразования будут применяться ко всему набору данных.

Теперь, когда вы знаете, как Power Query Editor имеет дело с предварительным просмотром данных, вы можете догадаться, почему произошла ошибка выше? Причина в том, что предварительный просмотр данных (около 1000 строк) не имел проблем с применяемыми преобразованиями (в этом случае автоматический тип данных изменяется на Date для столбца BirthDate). Однако весь набор данных (около 18 тыс. строк) имеет проблемы с этим преобразованием! Когда вы увидите вышеприведенную ошибку в Power BI Desktop, вы можете нажать View errors и перейти в Power Query editor, посмотреть их, разобраться с ними и исправить. Однако этого недостаточно.

Что делать, если ошибка не возникает в Power BI Desktop, но происходит в запланированном обновлении в службе Power BI?

Это хороший вопрос! Исправить ошибки в Power BI Desktop легко, но учтите, что ошибка также не произошла в Desktop, и вы опубликовали отчет Power BI на веб-сайт и запланировали его обновление. Затем на следующий день вы увидите, что отчет не обновился с ошибкой! Вы должны научиться правильно обращаться к строкам ошибок до того, как это приведет к сбою запланированного обновления. Давайте посмотрим, как с этим справиться.

Работа с ошибками: поиск строк ошибок

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

На скриншоте выше, мы переименовали таблицу DimCustomer в DimCustomer — Original, а затем создали ссылку из нее. Если вы хотите узнать, что такое Reference, прочитайте статью о Reference и Duplicate здесь . Новый запрошенный запрос можно назвать DimCustomer. Это будет чистый запрос без ошибок (мы удалим ошибки из него на следующем шаге);

Новая таблица — это таблица, которая будет чистой, без ошибок, и мы можем использовать ее в отчете. Давайте очистим это от любых ошибок

Удаление ошибок из загрузки таблицы в Power BI

Поскольку DimCustomer станет для нас окончательным запросом, я хочу удалить из него ошибки. Удаление ошибок — это простой вариант на вкладке «Главная» в разделе Reduce Rows -> Remove Rows -> Remove Errors.. Перед этим выберите столбец BirthDate.

Вы также можете сделать это для всех столбцов, если хотите; выбрав все столбцы, а затем выбрав «Remove Errors». Это сообщение — всего лишь образец одного столбца и может быть продлен до конца.

Remove Errors — это шаг на этапе преобразования данных, а это означает, что при нажатии APPLY он будет применяться ко всему набору данных, поэтому в результате, когда изменение типа данных приведет к ошибке, следующий шаг после этого — Remove Errors, уничтожит строки, вызвавшие ошибку. Но DimCustomer — Original все еще может вызвать ошибку, поэтому мы должны снять галочку Enable Load с этого запроса.

Теперь мы успешно удалили ошибки и загрузили данные в Power BI. Ошибок не будет.

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

Храните ошибки в таблице исключений

Аналогично опции «Remove Errors» есть опция «Keep Errors». Если вы уже видели этот вариант, возможно, вам интересно, как его использовать? Вот точный сценарий использования. Keep Errors поможет уловить строки ошибок в таблице исключений.

Создайте еще одну ссылку из DimCustomer — Original.

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

Теперь эта таблица будет содержать строки, которые вызывают ошибку. Вот пример набора;

Это еще не конец истории. Если вы загрузите эту новую таблицу DimCustomer — строки ошибок в Power BI, вы снова получите ту же ошибку. Зачем? ну, потому что этот запрос, безусловно, собирается возвращать строки ошибок! Вам необходимо удалить ошибку из этого набора данных.

Получение информации об ошибке

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

В таблице Error Rows добавьте Custom Column.

В редакторе Custom Column напишите «try», а затем пробел, имя поля, вызвавшего ошибку. В нашем примере: BirthDate;

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

Выход записи «try» будет иметь два поля; HasError (мы уже знаем, что это будет правда) и Error. Ошибка — это еще одна запись с более подробной информацией. Нажмите «Expand » в столбце «Custom column» и выберите «Error».

В столбце вывода с именем «Error» снова нажмите на «Expand» и на этот раз выберите все столбцы;

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

Теперь вы получите полную информацию об ошибке, как показано ниже;

Вышеупомянутая информация является вашим самым ценным активом для отчетности об исключениях.

Удалите столбец ошибок

Теперь последний шаг перед загрузкой данных в Power BI — удалить столбец, который вызывает ошибку. В нашем примере; Столбец BirthDate должен быть удален (в противном случае обновление снова завершится неудачей);

Отчет об исключении

Теперь вы можете загрузить данные в Power BI. У вас будет две таблицы; DimCustomer и DimCustomer – Error Rows. DimCustomer — это таблица, которую вы можете использовать для обычной отчетности. DimCustomer – Error Rows — это таблица, которую вы можете использовать для отчетов об исключениях. Отчет об исключении — это отчет, который можно использовать для устранения неполадок, и перечисляет все ошибки для дальнейшего расследования. Убедитесь, что между этими двумя таблицами нет никакой связи.

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

Подведем итоги

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

Источник

Работа с ошибками в Power Query

В Power Query можно столкнуться с двумя типами ошибок:

• Ошибки на уровне шага
• Ошибки на уровне ячеек

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

Ошибка на уровне шага

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

• Причина ошибки: первый раздел перед двоеточием. В приведенном выше примере причина ошибки — Expression.Error.
• Сообщение об ошибке: раздел непосредственно после причины. В приведенном выше примере сообщение об ошибке — столбец «Столбец» таблицы не найден.
• Сведения об ошибке: раздел непосредственно после строки Details: В приведенном выше примере сведения об ошибке — «Столбец«.

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

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

Не удается найти источник — DataSource.Error

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

Пример. У вас есть запрос из текстовой плитки, которая была расположена на диске D и создана пользователем A. Пользователь A предоставляет общий доступ к запросу пользователю B, у которого нет доступа к диску D. Когда этот пользователь пытается выполнить запрос, он получает dataSource.Error , так как в своей среде нет диска D.

Возможные решения. Вы можете изменить путь к файлу текстового файла на путь, к которому у обоих пользователей есть доступ. Как пользователь Б, вы можете изменить путь к файлу, чтобы он был локальной копией того же текстового файла. Если кнопка «Изменить параметры» доступна в области ошибок, ее можно выбрать и изменить путь к файлу.

Столбец таблицы не найден

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

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

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

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

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

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

Ошибка уровня ячейки

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

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

Обработка ошибок на уровне ячейки

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

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

Удаление ошибок

Чтобы удалить строки с ошибками в Power Query, сначала выберите столбец, содержащий ошибки. На вкладке «Главная» в группе «Уменьшить строки» выберите «Удалить строки«. В раскрывающемся меню выберите «Удалить ошибки«.

Результат этой операции даст вам таблицу, которую вы ищете.

Замена ошибок

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

В диалоговом окне «Замена ошибок » введите значение 10 , так как вы хотите заменить все ошибки значением 10.

Результат этой операции даст вам таблицу, которую вы ищете.

Сохранение ошибок

Power Query может служить хорошим средством аудита для выявления строк с ошибками, даже если вы не исправите ошибки. Здесь могут быть полезны ошибки keep . Чтобы сохранить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке «Главная» в группе «Уменьшить строки» выберите «Сохранить строки«. В раскрывающемся меню выберите «Сохранить ошибки«.

Результат этой операции даст вам таблицу, которую вы ищете.

Распространенные ошибки на уровне ячеек

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

Ошибки преобразования типов данных

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

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

Возможные решения. После идентификации строки с ошибкой можно либо изменить источник данных, чтобы отразить правильное значение, а не NA, либо применить операцию «Заменить» , чтобы указать значение для любых значений NA , вызывающих ошибку.

Ошибки операций

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

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

Возможные решения. Перед созданием этого настраиваемого столбца измените тип данных столбца Sales на текст.

Источник

As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.

Types of Errors

Power Query primarily handles 2 types or categories of errors:

• Step level Errors – it’s main symptom is that you query simply will not load giving you a message similar to the one below

• Value Level Errors – query will load, but will have a warning saying that it loaded with errors and give you a hyperlink to see what errors it had when loading. For example, you could click on the “1 error” hyperlink shown in the image below and Power Query will create a new query with only the rows that have errors so you can audit those rows specifically.

Ways to Audit Errors

The best way to audit the errors is to go into the Power Query editor window (fka Query Editor window) and go through the steps and values to read the error messages.

For both categories or types of errors, it is extremely important to understand the Error Message which provides a path (exactly where it happened, usually the first sentence in the error message) and checking the Error Reason (exactly why it happened, usually in the details section of the error message).

An example of a Step level error with its Error message is shown in the next image:

In some cases Power Query even gives us this “Go To Error” button so we can go to the first step where we hit the error so we can fix the issue. If Power Query doesn’t give you that button, or it doesn’t get you to the first step where you error was raised then it is helpful to navigate through the steps to find out where the error first appeared.

For Value Level errors the hyperlink to the errors is an invaluable help. Clicking that hyperlink will make Power Query automatically create another query, usually with the Format “Errors in [Name of Original Query]”, with only the rows that had errors in your original query and it’ll also add an Index Column (called Row Number) to tell you exactly in what row the error happened. You can click in the whitespace next to the value errors to see the Error Message in the Cell Preview pane as shown below.

One thing to mention is that you could’ve manually gone through your original query, select the fields that you want to audit and select the option called “Keep Errors” so you can only see the errors found in those specific fields/columns:

The Most Common Errors in Power Query

From my experience over the years, there are only a handful of errors that you’ll encounter in Power Query. There are so few that I created the next list of the most common errors that might come across when working with Power Query.

Most Common Step Level Errors

1. A Data Source function Error – usually caused by Power Query not having the right credentials or unable to connect to the data source (wrong file path or server name in most cases). Example: A query is being pointed to a filepath that no longer exists and, since Power Query can’t find it or connect to it, it displays a DataSource Error as shown below

1. Missing Columns Errors – usually caused when a step is referencing a column that no longer exists. Example: The report was doing a fill down operation over a column that was named “Employee”, but suddenly the files had a change so that column would have the name “Full Name” instead. This discrepancy gives us the errors below

Most Common Value Level Errors

1. Conversion Errors – converting a text that isn’t a date to a date data type can bring an error. When a value can not be converted to the desired data type, its output will be an error as shown below (Power Query can’t convert the text string ‘——‘ to a date)

1. Operation Errors – when a operation or a function requires a specific data type for a value, but we pass a completely different data type, then its output will be an error value. Example:  in the next image you’ll see that I try multiplying a column that has a text value “1” against a column that has a numeric value 1. Since the Column1 is set to text, that is not the number 1, but just a text “1”, so that operation yields an error and the Details tells us that the operator (*) can’t be applied to it. Similar to this situation, you can find others with functions that only accept certain data types and we try to pass a completely different data type that causes errors.

To learn more cool techniques and more advanced scenarios with the M language, go ahead and enroll to the FREE trial of the Power Query Academy where I talk more in detail about these type of techniques.

Power Query MЯзык, использованиеerrorУказывает на ненормальную ошибку при обработке выражения вычисления.

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

1. Распространенные ошибки Power Query

(1) Функция ввела неверный параметр: Expression.Error: невозможно преобразовать значение XXX в тип XXX.

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

let
         result = Number.ToText ("Текст")
in
    result

(2) Ошибка типа выражения: Expression.Error: невозможно применить оператор XXX к типам XXX и XXX.

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

let
    результат = 1+ "текст"
in
    result

(3) Имя не может быть распознано (регистр и орфографические ошибки): Expression.Error: Имя XXX не может быть распознано

По сравнению с именем функции в Common Errors (1) имя функции number.totext введено неправильно в зависимости от регистра, что приводит к ошибкам.

let
         result = number.totext ("текст")
in
    result

(4) Ошибка преобразования данных: DataFormat.Error: невозможно преобразовать в XXX

Столбец суммы продаж преобразован в числовой тип, но он содержит текстовые данные, что вызывает ошибку.

let
         Source = Excel.CurrentWorkbook () {[Name = "  2"]} [Содержание],
         Тип изменения = Table.TransformColumnTypes (источник, {{"сумма продаж", номер типа}})
in
         Тип изменения

2. Обработка исключений на языке M (Ошибка)

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

（1）TryСтруктура выражения:

tryResult = try Заявления, которые могут быть ненормальными otherwise Значение результата, когда произошла ошибка

（2）tryРезультат, возвращаемый выражением

Когда ошибка не возникла

TryВернуть записьrecordС полямиHasError、Value。

из ихHasErrorЦенностьFALSE，ValueДа»Заявления, которые могут быть ненормальными«Результат расчета

Когда возникает ошибка

потому как“считать1”Столбец не существует, ошибка вычисления выражения.

TryВернуть записьrecordС полямиHasError、Error。

из ихHasErrorЦенностьTRUE，ErrorЭто рекордrecord

РазвернутьError, Вы увидите подробную запись сообщения об ошибке, включая поляReason、Message、Detail。

из ихMessageВ поле указана неверная информация (она уже запрошена на китайском, как правило, более понятная)

（3) СловосочетаниеotherwiseВремя,tryРезультат, возвращаемый выражением

Когда ошибка не возникла

Возвращается результат вычисления «утверждения, которое может быть ненормальным».

Без совпаденияotherwiseизtryПо сравнению с оператором, нет необходимости передавать возвращаемый результатValueПрочтите поле еще раз. Выражение предложения проще и компактнее.

Когда возникает ошибка

потому как“считать1”Столбец не существует, ошибка вычисления выражения.

Результат расчета возвращенotherwiseПосле значения.

три,использоватьtryИсключение обработки выражения

(1) Повышение отказоустойчивости кода

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

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

(2) Пользовательское исключение выброса

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

let
         результат = (если (попробуйте 1+ "текст") [HasError]
                                         затем ошибка [Reason = "Ошибка вычисления",
                                                                 Message = "Ошибка операции сложения",
                                                                 Detail = "Можно только складывать числа"]
                    else 1+1)
in
    result

I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they arose. Now you are seeing Power Query errors, which probably appear strange and unfamiliar. It can be daunting at first, but over time you will understand what the errors are and what causes them.

While we can’t cover every error, the purpose of this post is to help demystify some of the more common errors you are likely to encounter.

Types of Power Query errors

Error messages can appear in various places, such as in the Queries & Connections pane, within the Power Query Editor, or maybe just as a value in a field.

I have grouped the common errors into three types:

• Process creation errors
• Data processing errors
• Software bugs

We look at each of these and find out how to fix the most common issues.

Process creation errors

Process creation errors occur as we build a query. These are driven by either errors in the M code or our lack of understanding of how Power Query works.

M code errors

M code errors can be challenging to find, especially if we are new to the language. A comma, a mistyped word, or even a capital letter is enough to cause the process to fail. The three main places where we can edit M code are:

• Custom Columns
• Advanced Editor
• Formula Bar

Let’s start by looking at Custom Columns, then move on to look at the Advanced Editor and Formula Bar.

Custom Columns

Of the M coding options, the Custom Column feature is the most accessible and the one we are most likely to use

Custom Columns contain a syntax check at the bottom of the screen to help guide us with formulas. Unfortunately, unless we’ve been working with Power Query for a while, we won’t understand what many of these messages mean.

The screenshot above shows the Token RightParen expected error message (we can also see a red squiggly underline below the comma). This is just one of many potential messages. As we type into the formula box, the message will change. Therefore, it is not worth looking at this message until we think the formula is finished. If the Show error link is visible, we can click it to take us to where the problem is.

Once you know what the messages mean, they are not as confusing as might initially seem. The most common warnings you’ll come across are:

• Token Literal expected means the next thing in the formula is expected to be a value, column name, or function.
• Token Then expected, or Token Else expected means the words then or else are expected to be entered. These will appear when writing an if statement.
• Token RightParen expected means that a closing bracket (or parentheses depending on your local vernacular), is expected to close a formula.
• A Comma cannot precede a RightParen means what it says; a comma cannot be directly in front of a closing bracket. There are no circumstances in M where this should be necessary.
• Invalid literal indicates an issue with the value entered as an argument (this often occurs when a text string has not been closed using the double quotation character).
• Token EoF expected usually occurs when an invalid function name is used, or it uses the wrong case (for example, if is a valid command, while If with an upper case I is not).
• Token internal expected means the logical test, true value, or false value of an if statement is missing, or a formula contained within these arguments is incomplete.
• The formula is incomplete usually indications no formula has been entered (only the equals symbol in the formula box).

Once we get the message that No syntax errors have been detected, we can click the OK button to close the window. Of course, this doesn’t mean the formula or data types are correct, but the syntax has been entered correctly.

Advanced Editor & Formula Bar

The Advanced Editor and Formula bar accept changes even if it causes an error. Unfortunately, this means the variety of error messages increases when using these features:

• The Advanced Editor has the same warning message at the bottom as a Custom Column but allows us to click Done even if there is an error in the code.
• The Formula Bar has no error checks. We can make any changes to the code and press the Enter key to accept those changes without any checks.

Given the multitude of possible errors we could create, we can’t go through all of them. However, it is much easier to troubleshoot once you know how to read the error message.

Advanced Editor syntax errors

Where there are syntax errors in the Advanced Editor, it highlights them with a red squiggly underline and describes the error at the bottom.

In the example above, the comma is missing at the end of the Source step. Therefore, this creates an error at the start of the #”Changed Type” step.

The underline may not show us exactly where the issue is; however it highlights at what point Power Query identifies the error. So we know it should be in the code prior to the error.

Expression syntax errors

As noted above, nothing stops us from entering errors into the Advanced Editor or Formula Bar.

The screenshot below shows an Expression.SyntaxError… hmmm… what does that mean?

If we look below the error message, Power Query has kindly shown us where the error is. If you notice, there is an arrow —->; this indicates the line that contains the error. By looking along that line, we find a group of ^^^; these pinpoint where the error resides.

In our example above, the error is that we have used a data type of tet, which is invalid.

Where there are multiple errors in the code, we may need to go through several rounds of error fixing as the error message will only show one error at a time.

Formula.Firewall error

There is a very frustrating error, which will rear its head from time to time – the dreaded Formula.Firewall error.

This error can take two forms:

Error message #1

Formula.Firewall: Query ‘____’ (step ‘____’) is accessing the data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

Error message #2

Formula.Firewall: Query ‘____’ (step ‘____’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

What do these mean? And how can we fix it?

Power Query does not like to use two data sources with different privacy settings. This usually occurs when there are:

• External and internal data sources combined in a single query
• Dynamic data sources used to define the source of another query

The following steps should fix the Formula.Firewall error.

Apply correct privacy settings

Let’s start by applying the privacy settings. We can do this by ignoring privacy or using the correct setting for each data source.

Ignore privacy

This first option is not ideal, as it ignores the data privacy settings entirely. However, it’s a useful little fix if you are the only person accessing the data.

Click File > Option Settings > Query Options.

The Query Options window dialog box. Select Privacy > Always ignore Privacy Level settings, then click OK.

Apply privacy for each data source

Alternatively, rather than ignoring the privacy settings, we could set them correctly.

To set the data source for inputs, click File > Options > Data source settings.

In the data source settings dialog box, select the source and click edit permissions. This allows us to set the privacy setting for each source.

There are four privacy settings:

• None: There are no privacy settings applied. Microsoft recommends only using this in a controlled environment.
• Private: The data is confidential or sensitive and should not be shared. This data cannot be shared with another data source.
• Organizational: The data can be shared within the organization. This data can only be shared with other organization data sources.
• Public: The data can be shared with any other data source, including public or organizational sources.

We should set the correct privacy level for our data sources.

Flattening queries

If there is still a Formula.Firewall error, we can combine the queries into a single query. The most straightforward approach to achieve this is shown in this post: https://exceloffthegrid.com/power-query-source-cell-value/

Data processing errors

Data processing errors occur when the data is fed through the transformation process. There may be nothing specifically wrong with the data or the process, yet the two don’t work well together. It could be something as simple as the transformation steps expecting to find a column called “Product”, but a “Product” column does not exist in the data set. Neither the data nor the process is incorrect, but they just don’t fit together.

The most common errors in this area are:

• Wrong source location
• Column name changes
• Incorrect data types

Let’s look at each of them in a bit more detail

Wrong source location

The wrong source location error occurs when a file or database changes location, or a server has crashed, and therefore the source cannot be accessed. Either way, Power Query can’t find the source data.

After refreshing, an error message like the following will appear, detailing the file location it cannot find.

We also see an error in the Queries & Connections window. If we double-click the query, we find out more detail about the error.

The Power Query editor opens and shows the following message. Click Go To Error to go to the exact step.

Finally, we can click Edit Settings to change the source location in the window.

There are other, and maybe better, options for changing the source data location; I have written about this in a previous post, so check out that for more details.

Missing column names

Generally, Column header names are hardcoded somewhere within the M code. Therefore, any changes in source data structure can trigger the following error.

The Queries & Connections pane will show the same Download did not complete error we saw earlier. Opening the Query reveals further details about the error.

Ideally, we should aim to build queries that can be flexible when column names change, though that isn’t always possible.

As a quick fix, we can either:

• Change the header name in the source data
• Correct the hard-coded value in the M code through the Advanced Editor or Formula Bar
• Delete the old step and insert a new one that correctly picks up the new column name.

But you must be careful; poorly implemented changes can cause other problems further down in the query.

Incorrect data types

Data type errors will not prevent the data from loading into the query; instead, those cells are loaded as blank. Queries and Connections pane shows the error and indicates the number of lines with errors.

The screenshot above shows 50 errors, but it could easily be just 1 or 2, depending on the structure of the data.

Data type errors occur when:

• Data is converted from one type to another – for example, trying to change a text string into a decimal data type
• Incorrect data types used within functions – for example, trying to use a number function on a text data type, or trying to multiply text values

Excel is very forgiving and will happily switch between data types where it can. However, power Query is not as forgiving; therefore, getting the correct data type is essential.

After opening the query, Power Query shows the errors. The pink color below the column header displays the % of errors found in the first 1000 records.

If the error is not found within the first 1000 records:

• Change the setting in the status bar to column profiling based on the entire data set.
• Filter to include only errors by clicking Home > Keep Rows > Keep Errors

After clicking the word “Error” within the Preview Window, it provides details about the specific issue.

In the screenshot above, we can see that Power Query was trying to convert a text value into a date, which caused the error.

While there may be multiple lines with errors, it does not mean you must fix each row individually. Changing one step may be enough to fix all the errors at the same time.

Software bugs

Finally, there is another unfortunate type of error that is outside of our control; software bugs.

When I started using Power Query, I came across two issues (though I didn’t know they were bugs at the time). In both cases, I concluded it was my fault for not understanding the tool correctly. However, it wasn’t me, but the software which was not working correctly.

As Power Query is continually updated, bugs can come and go quickly as newer versions are released. However, I would say that over the past few years, Power Querty has become robust and rarely suffers from issues.

Hopefully, you will not encounter any of the problems I had; they have already been resolved. Therefore, if you meet an issue where the software is not behaving as documented, then updating to the newest version should resolve the issue. Also, ensure you report any issues to Microsoft; they can only fix issues if they know they exist.

Conclusion

Power Query error messages can seem confusing as they use terms that we are unfamiliar with. However, I hope this post has helped you to identify your error and provides suggestions on how to fix it.

Read more posts in this Introduction to Power Query series

1. Introduction to Power Query
2. Get data into Power Query – 5 common data sources
3. Data Refresh Power Query in Excel: 4 ways & advanced options
4. Use the Power Query editor to update queries
5. Get to know Power Query Close & Load options
6. Power Query Parameters: 3 methods
7. Common Power Query transformations (50+ powerful transformations explained)
8. Power Query Append: Quickly combine many queries into 1
9. Get data from folder in Power Query: combine files quickly
10. List files in a folder & subfolders with Power Query
11. How to get data from the Current Workbook with Power Query
12. How to unpivot in Excel using Power Query (3 ways)
13. Power Query: Lookup value in another table with merge
14. How to change source data location in Power Query (7 ways)
15. Power Query formulas (how to use them and pitfalls to avoid)
16. Power Query If statement: nested ifs & multiple conditions
17. How to use Power Query Group By to summarize data
18. How to use Power Query Custom Functions
19. Power Query – Common Errors & How to Fix Them
20. Power Query – Tips and Tricks