Добрый день. У меня есть 20 файлов. Сделал запрос, объединяющий их. Выгружаю результат объединения и преобразования на лист. Сижу, смотрю, как на панельке с запросами Excel перебирает файлы, показывает, сколько МБ загружено, сколько строк загружено… Долго сижу). Потом в какой-то момент выскакивает сообщение об ошибке, например, что «не найден столбец Х», или что «ошибка формата данных: нельзя преобразовать в число». И дальше на панельке с запросами просто указывается «! Загрузка не была завершена» без предложения перейти к ошибкам. Тот фрагмент данных, который отображается в редакторе PQ, никаких ошибок, соответственно, не содержит)
Одну ошибку мне удалось найти: я сидел и наблюдал, на загрузке какого файла эксель выдает сообщение об ошибке и прекращает загрузку. Но это же ТРЭШ! А если файлов 100, 1000…)
Вопрос: каким образом я могу выяснить, в каких файлах из 20 возникают эти ошибки?
| title | description | author | ms.date | ms.author |
|---|---|---|---|---|
|
Error handling |
An article on how to catch and handle errors in Power Query using the syntax try and otherwise. |
ptyx507x |
12/9/2022 |
miescobar |
Error handling
Similar to how Excel and the DAX language have an IFERROR function, Power Query has its own syntax to test and catch errors.
As mentioned in the article on dealing with errors in Power Query, errors can appear either at the step or cell level. This article will focus on how you can catch and manage errors based on your own specific logic.
[!Note]
To demonstrate this concept, this article will use an Excel Workbook as its data source. The concepts showcased here apply to all values in Power Query and not only the ones coming from an Excel Workbook.
The sample data source for this demonstration is an Excel Workbook with the following table.
This table from an Excel Workbook has Excel errors such as #NULL!, #REF!, and #DIV/0! in the Standard Rate column. When you import this table into the Power Query editor, the following image shows how it will look.
Notice how the errors from the Excel workbook are shown with the [Error] value in each of the cells.
In this article, you’ll learn how to replace an error with another value. In addition, you’ll also learn how to catch an error and use it for your own specific logic.
In this case, the goal is to create a new Final Rate column that will use the values from the Standard Rate column. If there are any errors, then it will use the value from the correspondent Special Rate column.
Provide an alternative value when finding errors
In this case, the goal is to create a new Final Rate column in the sample data source that will use the values from the Standard Rate column. If there are any errors, then it will use the value from the corresponding Special Rate column.
To create a new custom column, go to the Add column menu and select Custom column. In the Custom column window, enter the formula try [Standard Rate] otherwise [Special Rate]. Name this new column Final Rate.
The formula above will try to evaluate the Standard Rate column and will output its value if no errors are found. If errors are found in the Standard Rate column, then the output will be the value defined after the otherwise statement, which in this case is the Special Rate column.
After adding the correct data types to all columns in the table, the following image shows how the final table looks.
[!NOTE]
As an alternative approach, you can also enter the formulatry [Standard Rate] catch ()=> [Special Rate], which is equivalent to the previous formula, but using the catch keyword with a function that requires no parameters.The
catchkeyword was introduced to Power Query in May of 2022.
Provide your own conditional error logic
Using the same sample data source as the previous section, the new goal is to create a new column for the Final Rate. If the value from the Standard Rate exists, then that value will be used. Otherwise the value from the Special Rate column will be used, except for the rows with any #REF! error.
[!Note]
The sole purpose of excluding the#REF!error is for demonstration purposes. With the concepts showcased in this article, you can target any fields of your choice from the error record.
When you select any of the whitespace next to the error value, you get the details pane at the bottom of the screen. The details pane contains both the error reason, DataFormat.Error, and the error message, Invalid cell value '#REF!':
You can only select one cell at a time, so you can effectively only see the error components of one error value at a time. This is where you’ll create a new custom column and use the try expression.
Use try with custom logic
To create a new custom column, go to the Add column menu and select Custom column. In the Custom column window, enter the formula try [Standard Rate]. Name this new column All Errors.
The try expression converts values and errors into a record value that indicates whether the try expression handled an error or not, as well as the proper value or the error record.
You can expand this newly created column with record values and look at the available fields to be expanded by selecting the icon next to the column header.
This operation will expose three new fields:
- All Errors.HasError—displays whether the value from the Standard Rate column had an error or not.
- All Errors.Value—if the value from the Standard Rate column had no error, this column will display the value from the Standard Rate column. For values with errors this field won’t be available, and during the expand operation this column will have
nullvalues. - All Errors.Error—if the value from the Standard Rate column had an error, this column will display the error record for the value from the Standard Rate column. For values with no errors, this field won’t be available, and during the expand operation this column will have
nullvalues.
For further investigation, you can expand the All Errors.Error column to get the three components of the error record:
- Error reason
- Error message
- Error detail
After doing the expand operation, the All Errors.Error.Message field displays the specific error message that tells you exactly what Excel error each cell has. The error message is derived from the Error Message field of the error record.
Now with each error message in a new column, you can create a new conditional column with the name Final Rate and the following clauses:
- If the value in the All Errors.Errors.Message column equals
null, then the output will be the value from the Standard Rate column. - Else, if the value in the All Errors.Errors.Message column doesn’t equal
Invalid cell value '#REF!'., then the output will be the value from the Special Rate column. - Else, null.
After keeping only the Account, Standard Rate, Special Rate, and Final Rate columns, and adding the correct data type for each column, the following image demonstrates what the final table looks like.
Use try and catch with custom logic
Alternatively, you can also create a new custom column using the try and catch keywords.
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null
More resources
- Understanding and working with errors in Power Query
- Add a Custom column in Power Query
- Add a Conditional column in Power Query
Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе.
Text.SplitAny
List.Select
Character.FromNumber
Text.Combine
Содержание
- Работа с ошибками в Power Query
- Ошибка на уровне шага
- Распространенные ошибки на уровне шага
- Не удается найти источник источника данных. ошибка
- Столбец таблицы не найден
- Другие распространенные ошибки на уровне шага
- Ошибка на уровне ячейки
- Обработка ошибок на уровне ячейки
- Удалить ошибки
- Заменить ошибки
- Не учитывать ошибки
- Распространенные ошибки на уровне ячеек
- Ошибки преобразования типов данных
- Ошибки операций
- Агрегированные данные из столбца (Power Query)
- Столбцы pivot (Power Query)
Работа с ошибками в Power Query
В Power Query можно столкнуться с ошибками двух типов:
- Ошибки на уровне шага
- Ошибки на уровне ячеек
В этой статье приводятся рекомендации по исправлению наиболее распространенных ошибок, которые могут быть обнаружены на каждом уровне, и описание причины ошибки, сообщения об ошибке и сведений об ошибке для каждого из них.
Ошибка на уровне шага
Ошибка на уровне шага предотвращает загрузку запроса и отображение компонентов ошибок в желтой области.
- Причина ошибки: первый раздел перед двоеточием. В приведенном выше примере причина ошибки — expression. Error.
- Сообщение об ошибке: раздел непосредственно после причины. В приведенном выше примере сообщение об ошибке — столбец «Column» таблицы не найден.
- Сведения об ошибке: раздел сразу после строки Details: String. В приведенном выше примере сведения об ошибке — Column.
Распространенные ошибки на уровне шага
Во всех случаях рекомендуется внимательно рассмотреть причину ошибки, сообщение об ошибке и сведения об ошибке, чтобы понять, что вызывает ошибку. Можно нажать кнопку Перейти к ошибке (если доступно), чтобы просмотреть первый шаг, в котором произошла ошибка.
Не удается найти источник источника данных. ошибка
Эта ошибка обычно возникает, когда пользователь недоступен для доступа к источнику данных, но у него нет правильных учетных данных для обращения к нему или источник перемещен в другое место.
Пример. у вас есть запрос из текстовой плитки, расположенной на диске D и созданной пользователем а. пользователь a использует запрос совместно с пользователем б, у которого нет доступа к диску D. Когда этот пользователь пытается выполнить запрос, он получает источник данных. ошибка , так как в среде нет диска D.
Возможные решения: можно изменить путь к текстовому файлу на путь, к которому у обоих пользователей есть доступ. Пользователь б может изменить путь к файлу на локальную копию того же текстового файла. Если кнопка изменить параметры доступна в области ошибка, ее можно выбрать и изменить путь к файлу.
Столбец таблицы не найден
Эта ошибка обычно активируется, когда шаг создает прямую ссылку на имя столбца, которое не существует в запросе.
Пример. имеется запрос из текстового файла, в котором одно из имен столбцов имело значение Column. В запросе имеется шаг, который переименовывает столбец в значение Date. Но в исходном текстовом файле были внесены изменения, и он больше не содержит заголовок столбца с именем, так как он был вручную изменен на » Дата«. Power Query не удается найти заголовок столбца с именем Column, поэтому он не может переименовать столбцы. Отображается ошибка, показанная на следующем рисунке.
Возможные решения. в этом случае существует несколько решений, но все они зависят от того, что вы хотели бы сделать. В этом примере, так как заголовок столбца с датой правильно взят из текстового файла, можно просто удалить шаг, который переименовывает столбец. Это позволит выполнить запрос без этой ошибки.
Другие распространенные ошибки на уровне шага
При объединении или объединении данных между несколькими источниками данных может появиться Формула. ошибка брандмауэра , как показано на следующем рисунке.
Эта ошибка может быть вызвана несколькими причинами, такими как уровни конфиденциальности данных между источниками данных или способ объединения или слияния этих источников данных. Дополнительные сведения о диагностике этой проблемы см. в статье брандмауэр конфиденциальности данных.
Ошибка на уровне ячейки
Ошибка на уровне ячейки не помешает загрузить запрос, но отображает ошибочные значения в ячейке. При выборе пустого пространства в ячейке отображается область ошибок под предварительной версией данных.
Средства профилирования данных позволяют легко определить ошибки на уровне ячеек с помощью функции «качество столбца». Дополнительные сведения: средства профилирования данных
Обработка ошибок на уровне ячейки
При возникновении ошибок на уровне ячеек Power Query предоставляет набор функций для их обработки путем удаления, замены или сохранения ошибок.
В следующих разделах приведенные примеры будут использовать тот же образец запроса, что и начальная точка. В этом запросе имеется столбец Sales , содержащий одну ячейку с ошибкой, вызванной ошибкой преобразования. Значение в этой ячейке равно » НД«, но при преобразовании этого столбца в целое число Power Query не удалось преобразовать НД в число, поэтому отобразится следующая ошибка.
Удалить ошибки
Чтобы удалить строки с ошибками в Power Query, сначала выберите столбец, содержащий ошибки. На вкладке Главная в группе уменьшить строки выберите Удалить строки. В раскрывающемся меню выберите пункт удалить ошибки.
В результате этой операции вы получите таблицу, которую вы ищете.
Заменить ошибки
Если вместо удаления строк с ошибками необходимо заменить их фиксированным значением, это также можно сделать. Чтобы заменить строки с ошибками, сначала выберите столбец, содержащий ошибки. На вкладке Преобразование в группе все столбцы выберите заменить значения. В раскрывающемся меню выберите команду заменить ошибки.
В диалоговом окне Замена ошибок введите значение 10 , так как необходимо заменить все ошибки значением 10.
В результате этой операции вы получите таблицу, которую вы ищете.
Не учитывать ошибки
Power Query может служить хорошим средством аудита для обнаружения любых строк с ошибками, даже если эти ошибки не устранены. Именно здесь могут быть полезны ошибки сохранения . Чтобы избежать возникновения ошибок в строках, сначала выберите столбец, содержащий ошибки. На вкладке Главная в группе сокращение строк выберите параметр не учитывать строки. В раскрывающемся меню выберите пункт не учитывать ошибки.
В результате этой операции вы получите таблицу, которую вы ищете.
Распространенные ошибки на уровне ячеек
Как и в случае с любой ошибкой на уровне шага, мы рекомендуем ознакомиться со статьей причины ошибок, сообщения об ошибках и сведения об ошибках, предоставленные на уровне ячейки, чтобы понять, что вызывает ошибки. В следующих разделах рассматриваются некоторые из наиболее часто встречающихся ошибок на уровне ячеек в Power Query.
Ошибки преобразования типов данных
Часто запускается при изменении типа данных столбца в таблице. Некоторые значения, найденные в столбце, не могут быть преобразованы в нужный тип данных.
Пример. у вас есть запрос, который содержит столбец с именем Sales. Одна ячейка в этом столбце содержит значение « НД » в качестве значения ячейки, а в остальных — целые числа. Вы решили преобразовать тип данных столбца из текстового в целое число, но ячейка со значением НД вызывает ошибку.
Возможные решения. После определения строки с ошибкой можно изменить источник данных, чтобы он отражал правильное значение, а не НД, или применить операцию замены ошибки , чтобы предоставить значения для любых значений НД , вызвавших ошибку.
Ошибки операций
При попытке применить неподдерживаемую операцию, например умножить текстовое значение на числовое значение, возникает ошибка.
Пример. необходимо создать настраиваемый столбец для запроса, создав текстовую строку, содержащую фразу «общий объем продаж:», Объединенную со значением из столбца » продажи «. Ошибка возникает из-за того, что операция объединения поддерживает только текстовые столбцы, а не числовые.
Возможные решения. перед созданием этого пользовательского столбца измените тип данных столбца Sales на Text.
Источник
Агрегированные данные из столбца (Power Query)
С помощью Power Query можно агрегировать один или несколько столбцов связанной таблицы. Например, можно вычислить общую сумму позиций для каждого заказа.
Предварительный просмотр данных таблицы «Заказы» в веб-канале OData «Northwind»
В этом примере агрегируется столбец Order_Details из таблицы «Заказы Northwind». Данные импортируется из образца веб-канала OData Northwind.
В Excel выберите Главная> новый источник > другие источники > из канала OData.
В диалоговом окне Канал OData введите URL-адрес канала OData Northwind.
В области Навигатор выберите таблицу Orders (Заказы) в левой области, данные будут предварительно просмотреться справа, а затем выберите Transform Data (Преобразовать данные).
Появится редактор Power Query.
В режиме предварительного просмотра данных прокрутите список вправо и щелкните значок 
рядом со Order_Details данными.
Таблица — это пример структурированного столбца, который можно развернуть, чтобы увидеть
больше столбцов. Дополнительные сведения см. в статье Работа со столбцом со структурой списка,записи или таблицы.
В списке Столбец таблицы:
Выберите параметр Агрегировать.
Наведите курсор на агрегатный элемент функции, например Агрегаты UnitPrice.
В списке агрегатных функций выберите одну или несколько агрегатных функций. Например, Sum и Average.
Power Query расширяет столбец Order_Details, заменяя его значениями «Сумма UnitPrice» и «Среднее значение unitPrice».
При расширении структурированного столбца, например Таблица,может возникнуть вопрос, откуда он был. Чтобы узнать это, можно просмотреть связанную таблицу во внешнем источнике данных.
В области Параметры запроса в области Примененные шаги выберите Источник , а затем 
Параметры изменения. Появится диалоговое окно Источник данных, которое зависит от внешнего источника данных. В этом примере это диалоговое окно Канал OData. Заметьте этот источник данных.
Выберите Главная > последниеисточники , а затем выберите источник данных из списка, который соответствует тому, что вы заме добавили на шаге 1. Появится диалоговое окно Навигатор.
По умолчанию список всех таблиц из источника данных отображается в левой области. Выберите связанную таблицу, чтобы просмотреть данные в правой области. Имя структурированного столбца — это имя связанной таблицы. В этом примере выберитеOrder_Details .
По умолчанию вы можете просматривать и просматривать все таблицы. Но вы можете выбрать Параметры отображения, чтобы включить или отключить только выбранные элементы и Включить предварительный просмотр данных, чтобы настроить отображение по своему вкусу.
Источник
Столбцы pivot (Power Query)
Вы можете свести столбец в таблице, агрегируя совпадающие значения в столбце, чтобы создать новую ориентацию таблицы. Сначала таблица будет отсортироваться в порядке возрастания по значениям в первом столбце.
При своде вы берете два исходных столбца и создаете новую пару атрибута и значения, которая представляет точку пересечения новых столбцов:
Атрибуты Одинаковые значения из одного столбца исходных атрибутов (в зеленом цвете слева) будут совмещены в несколько новых столбцов (в зеленом цвете справа).
Значения из исходного столбца без свербов (синего цвета слева) распределяются по соответствующим новым столбцам в качестве значений (синим цветом справа).
Вот пример данных, используемых в этой процедуре.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Выберем столбец, который нужно степть. В примере выберите Дата. Уникальные значения этого столбца становятся новыми столбцами и их заглавями.
Выберите преобразовать > столбец .
В диалоговом окне Столбец pivot в списке Столбец значений выберите Amt.
Выберите Дополнительные параметры, а затем выберите функцию Агрегированного значения. В примере выберите Сумма.
Доступны такие параметры: Don’t aggregate, Count (all), Count (не пусто), Minimum, Maximum , Median, Sumи Average.
Совет Если вы не хотите подсчитывать ячейку без значения, выберите элемент Количество (не пустая). Например, если в столбце десять ячеек и две пустые, то количество будет 8.
Столбец Date из исходной таблицы сведется для получения суммы всех значений из исходного столбца Amt на пересечении country и каждого нового столбца Date.
Вот пример данных, используемых в этой процедуре.
Вы можете сводные столбцы без агрегирования при работе со столбцами, которые невозможно агрегировать или вы не хотите агрегировать значения.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Выберем столбец, который нужно степть. В примере выберите положение. Уникальные значения этого столбца становятся новыми столбцами и их заглавями.
Выберите преобразовать > столбец .
В диалоговом окне Столбец pivot в списке Столбец значений выберите Product (Товар).
Выберите Дополнительные параметры, а затем выберите функцию Агрегированного значения. В примере выберите не агрегировать.
Столбец «Положение» из исходной таблицы высвещен для получения значений из исходного столбца «Товар» на пересечении столбца «Страна» и каждого нового столбца «Положение».
В этом примере мы используем пример из раздела «Сводка и агрегировать столбец»:
Если вы сводные данные в столбце Date, выберите Amt в списке Столбец значений, а затем в дополнительных параметрах выберитеНе агрегировать для функции агрегированного значения ,вы получите следующее сообщение об ошибке:
«Expression.Error: В переименовке было слишком много элементов, чтобы завершить операцию».
Эта ошибка возникает,потому что параметр Не агрегировать предназначен для работы с одним значением, возвращенным для пересечения между country и Date, а не несколькими значениями.
Эта ошибка может появиться не сразу. При первоначальном определении данных в точке пересечения создавалось отдельное значение. Позднее после операции обновления условия данных изменились, и теперь в точке пересечения можно указать несколько значений. В этом случае можно использовать команду Pivot с агрегатом.
Источник
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
- 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
- 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
- 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)
- 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.