Power query поиск ошибок

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

Skip to content

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
                                      Source,
                                     {{"Sales", Int64.Type}, 
                                      {"Product", type text}})
in
    #"Changed Type"

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.

Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise

In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.

Download Sample Excel Workbook

First up, let’s load data from this table.

I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.

But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?

Let’s load the data into Power Query and call it Errors from Sheet

Straight away you can see the errors in the column.

Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.

Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.

I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]

This creates a new column with a Record in each row

In this record are two fields. HasError states whether or not there’s an error in the [End] column

If there is an Error then the 2nd field is another record containing information about that error

If there isn’t an error, then the 2nd field is the value from the [End] column

If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value

Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.

If I expand this Error column I can see all of these fields.

I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way

The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.

What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.

If there isn’t an error I want the value from the [End] column.

I can do all of this in a new column using an if then else

Add a new Custom Column called Error or Value and enter this code

What this is saying is:

• If the boolean value [HasError] in the [Try_End] column is true then
• return the [Message] in the [Error] record of the [Try_End] column
• else return the [Value] from the [Try_End] column

With that written I can remove both the End and Try_End columns so the final table looks like this

Checking for Errors and Replacing Them With Default Values

In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.

I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step

I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]

this gives me an error as I know it will in rows 1 and 3

so to avoid this, edit the step and use try .. otherwise

now the errors are replaced with 0.

Errors Loading Data from A Data Source

I’ll create a new query and load from an Excel workbook

Navigating to the file I want I load it

and loading this table

I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.

I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.

I don’t have an X: drive so I know this will cause the workbook loading to fail.

So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.

Open the Advanced Editor again and then use try otherwise to specify the backup file’s location

close the editor and now my backup file is loaded.