Power query как заменить error на null

Dealing with errors in Power Query

In Power Query, you can encounter two types of errors:

• Step-level errors
• Cell-level errors

This article provides suggestions for how to fix the most common errors you might find at each level, and describes the error reason, error message, and error detail for each.

Step-level error

A step-level error prevents the query from loading and displays the error components in a yellow pane.

• Error reason: The first section before the colon. In the example above, the error reason is Expression.Error.
• Error message: The section directly after the reason. In the example above, the error message is The column ‘Column’ of the table wasn’t found.
• Error detail: The section directly after the Details: string. In the example above, the error detail is Column.

Common step-level errors

In all cases, we recommend that you take a close look at the error reason, error message, and error detail to understand what’s causing the error. You can select the Go to error button, if available, to view the first step where the error occurred.

Can’t find the source — DataSource.Error

This error commonly occurs when the data source is inaccessible by the user, the user doesn’t have the correct credentials to access the data source, or the source has been moved to a different place.

Example: You have a query from a text tile that was located in drive D and created by user A. User A shares the query with user B, who doesn’t have access to drive D. When this person tries to execute the query, they get a DataSource.Error because there’s no drive D in their environment.

Possible solutions: You can change the file path of the text file to a path that both users have access to. As user B, you can change the file path to be a local copy of the same text file. If the Edit settings button is available in the error pane, you can select it and change the file path.

The column of the table wasn’t found

This error is commonly triggered when a step makes a direct reference to a column name that doesn’t exist in the query.

Example: You have a query from a text file where one of the column names was Column. In your query, you have a step that renames that column to Date. But there was a change in the original text file, and it no longer has a column heading with the name Column because it was manually changed to Date. Power Query is unable to find a column heading named Column, so it can’t rename any columns. It displays the error shown in the following image.

Possible solutions: There are multiple solutions for this case, but they all depend on what you’d like to do. For this example, because the correct Date column header already comes from your text file, you can just remove the step that renames the column. This will allow your query to run without this error.

Other common step-level errors

When combining or merging data between multiple data sources, you might get a Formula.Firewall error such as the one shown in the following image.

This error can be caused by a number of reasons, such as the data privacy levels between data sources or the way that these data sources are being combined or merged. For more information about how to diagnose this issue, go to Data privacy firewall.

Cell-level error

A cell-level error won’t prevent the query from loading, but displays error values as Error in the cell. Selecting the white space in the cell displays the error pane underneath the data preview.

The data profiling tools can help you more easily identify cell-level errors with the column quality feature. More information: Data profiling tools

Handling errors at the cell level

When encountering any cell-level errors, Power Query provides a set of functions to handle them either by removing, replacing, or keeping the errors.

For the next sections, the provided examples will be using the same sample query as the start point. In this query, you have a Sales column that has one cell with an error caused by a conversion error. The value inside that cell was NA, but when you transformed that column to a whole number Power Query couldn’t convert NA to a number, so it displays the following error.

Remove errors

To remove rows with errors in Power Query, first select the column that contains errors. On the Home tab, in the Reduce rows group, select Remove rows. From the drop-down menu, select Remove errors.

The result of that operation will give you the table that you’re looking for.

Replace errors

If instead of removing rows with errors, you want to replace the errors with a fixed value, you can do so as well. To replace rows that have errors, first select the column that contains errors. On the Transform tab, in the Any column group, select Replace values. From the drop-down menu, select Replace errors.

In the Replace errors dialog box, enter the value 10 because you want to replace all errors with the value 10.

The result of that operation will give you the table that you’re looking for.

Keep errors

Power Query can serve as a good auditing tool to identify any rows with errors even if you don’t fix the errors. This is where Keep errors can be helpful. To keep rows that have errors, first select the column that contains errors. On the Home tab, in the Reduce rows group, select Keep rows. From the drop-down menu, select Keep errors.

The result of that operation will give you the table that you’re looking for.

Common cell-level errors

As with any step-level error, we recommend that you take a close look at the error reasons, error messages, and error details provided at the cell level to understand what’s causing the errors. The following sections discuss some of the most frequent cell-level errors in Power Query.

Data type conversion errors

Commonly triggered when changing the data type of a column in a table. Some values found in the column could not be converted to the desired data type.

Example: You have a query that includes a column named Sales. One cell in that column has NA as a cell value, while the rest have whole numbers as values. You decide to convert the data type of the column from text to whole number, but the cell with the NA value causes an error.

Possible solutions: After identifying the row with the error, you can either modify the data source to reflect the correct value rather than NA, or you can apply a Replace error operation to provide a value for any NA values that cause an error.

Operation errors

When trying to apply an operation that isn’t supported, such as multiplying a text value by a numeric value, an error occurs.

Example: You want to create a custom column for your query by creating a text string that contains the phrase «Total Sales: » concatenated with the value from the Sales column. An error occurs because the concatenation operation only supports text columns and not numeric ones.

Possible solutions: Before creating this custom column, change the data type of the Sales column to be text.

Nested values shown as errors

When working with data that contains nested structured values (such as tables, lists, or records), you may sometimes encounter the following error:

These errors usually occur for two reasons:

• When the Data Privacy Firewall buffers a data source, nested non-scalar values are automatically converted to errors.
• When a column defined with the Any data type contains non-scalar values, such values will be reported as errors during load (such as in a Workbook in Excel or the data model in Power BI Desktop).

Possible solutions:

• Remove the column that contains the error, or set a non- Any data type for such a column.
• Change the privacy levels of the data sources involved to one that allows them to be combined without being buffered.
• Flatten the tables before doing a merge to eliminate columns that contain nested structured values (such as table, record, or list).

Источник

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

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

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

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

Пример источника данных для этой демонстрации — книга Excel со следующей таблицей.

Эта таблица из книги Excel содержит ошибки Excel, такие как #NULL!, #REF!, и #DIV/0! в столбце «Стандартный тариф «. При импорте этой таблицы в редактор Power Query показано, как она будет выглядеть.

Обратите внимание, что ошибки из книги Excel отображаются со значением [Error] в каждой ячейке.

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

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

Укажите альтернативное значение при поиске ошибок

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

Чтобы создать новый настраиваемый столбец, перейдите в меню «Добавить столбец » и выберите «Настраиваемый столбец«. В окне «Настраиваемый столбец» введите формулу try [Standard Rate] otherwise [Special Rate] . Присвойт этому новому столбцу окончательную ставку.

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

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

В качестве альтернативного подхода можно также ввести формулу try [Standard Rate] catch ()=> [Special Rate] , эквивалентную предыдущей формуле, но используя ключевое слово catch с функцией, для которой не требуются параметры.

Ключевое catch слово было введено в Power Query в мае 2022 года.

Укажите собственную логику условной ошибки

Используя тот же образец источника данных, что и в предыдущем разделе, новая цель — создать новый столбец для конечной ставки. Если значение из стандартной ставки существует, это значение будет использоваться. В противном случае будет использоваться значение из столбца «Специальная ставка «, за исключением строк с любой #REF! ошибкой.

Единственной целью исключения ошибки является демонстрация #REF! . В соответствии с основными понятиями, представленными в этой статье, вы можете выбрать любые поля из записи об ошибке.

При выборе любого из пробелов рядом со значением ошибки вы получите область сведений в нижней части экрана. Область сведений содержит как причину ошибки, DataFormat.Error так и сообщение об ошибке: Invalid cell value ‘#REF!’

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

Использование try с пользовательской логикой

Чтобы создать новый настраиваемый столбец, перейдите в меню «Добавить столбец » и выберите «Настраиваемый столбец«. В окне «Настраиваемый столбец» введите формулу try [Standard Rate] . Присвойт этому новому столбцу имя «Все ошибки«.

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

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

Эта операция предоставит три новых поля:

• Все ошибки.HasError — показывает, было ли значение из столбца «Стандартная ставка » ошибкой.
• Все ошибки.Значение— если значение из столбца «Стандартная ставка » не содержит ошибок, этот столбец будет отображать значение из столбца «Стандартная ставка «. Для значений с ошибками это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.
• Все ошибки.Error — если значение из столбца «Стандартная ставка » содержит ошибку, в этом столбце отображается запись об ошибке для значения из столбца «Стандартная ставка «. Для значений без ошибок это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.

Для дальнейшего изучения можно развернуть столбец All Errors.Error , чтобы получить три компонента записи об ошибке:

• Причина ошибки
• Сообщение об ошибке
• Сведения об ошибках

После выполнения операции развертывания в поле All Errors.Error.Message отображается определенное сообщение об ошибке, сообщающее о том, какая ошибка Excel имеет каждая ячейка. Сообщение об ошибке является производным от поля сообщения об ошибке записи об ошибке.

Теперь с каждым сообщением об ошибке в новом столбце можно создать новый условный столбец с именем Final Rate и следующими предложениями:

• Если значение в столбце All Errors.Errors.Message равно null , выходные данные будут иметь значение из столбца «Стандартная ставка «.
• В противном случае, если значение в столбце All Errors.Errors.Message равно Invalid cell value ‘#REF!’. , выходные данные будут значениями из столбца «Специальная ставка «.
• Else, null.

После сохранения только столбцов «Учетная запись«, «Стандартная ставка«, » Специальная ставка» и «Окончательная ставка » и добавления правильного типа данных для каждого столбца показано, как выглядит окончательная таблица.

Использование try и catch использование пользовательской логики

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

try [Standard Rate] catch (r)=> if r[Message] <> «Invalid cell value ‘#REF!’.» then [Special Rate] else null

Источник

You’d often run into data sets with errors especially when the source is excel. If you have been working with Power Query you know that it doesn’t like error values and truncates the entire row which has an error in any column.

Sad. But you can obviously do Replace Errors and fix them with null values, however the challenge is that, the Replace Errors should automatically work on even the new columns added in data.

Video First ?

Consider this 3 column data

Now obviously when this is loaded in Power Query it will show error wherever #N/A (and for any other type of excel errors)

You can easily fix this

• Select all columns
• Transform Tab >> Replace Values Drop Down >> Replace Errors >> type null
• Done

But what if the 4th column is added and has error values, the Replace Errors will not automatically extend to the 4th column. Let’s do that!

Replace Error Values in Multiple Columns

Step 1 –

• I extract all the columns names as a list.
• Click on fx and write the M code below
• I do this as a second step after Source (data loading)

=Table.ColumnNames(Source)

Step 2 – Since a Table offers more options to work as compared to a List. Covert the List to a Table. Right click on the List (header) >> To Table.

Step 3 – From the Add Columns Tab >> Custom Column >> type =null

• This will add a Custom Column will null values across all rows.
• I am doing this because I want to replace all error values with null.

The result looks like this

Step 4 – Next I will transpose this table. Transform Tab >> Transpose.

Understanding whats going on..

If you are still with me, so far you have blindly pursued the steps. Let me help you understand where are we headed.

• When you do Replace Errors the highlighted (in yellow) code appears.
• Notice that column names – One, Two and Three are hard coded. They wont change if the column names changes or source data adds a new column.
• Also note that the column names are in double curly brackets {{ }}. Which means it’s a list inside a list.
• We are trying to create the same list of list with 2 parts – Column Name and null value. But a dynamic one!
• Let’s proceed

Step 5 – Convert the table into a list of list.

• Use the fx button to write a short M Code
• This will convert each column into a list and then make a single list of all lists
• Renamed the step to ColList (it’s optional but nice to have good labels across)

= Table.ToColumns(#'Transposed Table')

Step 6 – It’s time now to feed the list in Replace Errors

• Using the fx I’ll create a new Step
• Write the following Code. In the code Source is the Step (which has the table with error values) and ColList is our List of List which replaces all errors dynamically with null.

= Table.ReplaceErrorValues(Source, ColList)

Bingo! 😎

Some more cool stuff on Power Query!

1. Make Remove Other Columns Dynamic in Power Query
2. Dynamic Column Renaming
3. Running Total in Power Query
4. Video – Combine Data from Multiple Excel File – With Dynamic Sheets and Columns

I have an awesome Course on Power Query, you must check it out..

Course Details are here   |   Course Outline (pdf) is here

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

В нашей таблице 2 столбца. В первом находится название категории и даты, а во втором суммы остатков.

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

Решение

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

Значения копии столбца мы преобразуем в дату. В исходном столбце мы заменим значения таким образом, чтобы они превратились в null в случае, когда в копии находится дата.

Далее останется лишь удалить лишние строки и переупорядочить столбцы.

Примененные функции

• Excel.Workbook
• File.Contents
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.PromoteHeaders
• Table.Skip
• Table.DuplicateColumn
• Table.TransformColumnTypes
• Table.ReplaceErrorValues
• Table.ReplaceValue
• Replacer.ReplaceValue
• Table.FillDown

Код

let
   source = Excel.Workbook(File.Contents(path), null, true),
   cols_select = Table.SelectColumns(source, {"Data"}),
   col_expand_table = Table.ExpandTableColumn(
      cols_select,
      "Data",
      {"Column1", "Column2"}
   ),
   table_promote_headers = Table.PromoteHeaders(
      col_expand_table,
      [PromoteAllScalars = true]
   ),
   table_skip_rows = Table.Skip(table_promote_headers, 8),
   table_promote_headers_2 = Table.PromoteHeaders(
      table_skip_rows,
      [PromoteAllScalars = true]
   ),
   col_duplicate = Table.DuplicateColumn(
      table_promote_headers_2,
      "Номенклатура",
      "Дата"
   ),
   types = Table.TransformColumnTypes(col_duplicate, {{"Дата", type date}}),
   col_replace_errors = Table.ReplaceErrorValues(types, {{"Дата", null}}),
   col_replace_values = Table.ReplaceValue(
      col_replace_errors,
      each [Номенклатура],
      each if [Дата] = null then [Номенклатура] else null,
      Replacer.ReplaceValue,
      {"Номенклатура"}
   ),
   table_fill_down = Table.FillDown(col_replace_values, {"Номенклатура"}),
   types_2 = Table.TransformColumnTypes(
      table_fill_down,
      {{"Кон. остаток", type number}},
      "en-US"
   ),
   col_replace_values_2 = Table.ReplaceValue(
      types_2,
      null,
      0,
      Replacer.ReplaceValue,
      {"Кон. остаток"}
   ),
   rows_select = Table.SelectRows(
      col_replace_values_2,
      each ([Дата] <> null)
   ),
   table_reorder_cols = Table.ReorderColumns(
      rows_select,
      {"Дата", "Номенклатура", "Кон. остаток"}
   )
in
   table_reorder_cols

Этот урок входит в Практический курс Power Query

Replace Error Values in Multiple Columns

You’d often run into data sets with errors especially when the source is excel. If you have been working with Power Query you know that it doesn’t like error values and truncates the entire row which has an error in any column.

Sad. But you can obviously do Replace Errors and fix them with null values, however the challenge is that, the Replace Errors should automatically work on even the new columns added in data.

Video First ?

Consider this 3 column data

Now obviously when this is loaded in Power Query it will show error wherever #N/A (and for any other type of excel errors)

You can easily fix this

• Select all columns
• Transform Tab >> Replace Values Drop Down >> Replace Errors >> type null
• Done

But what if the 4th column is added and has error values, the Replace Errors will not automatically extend to the 4th column. Let’s do that!

Replace Error Values in Multiple Columns

Step 1 –

• I extract all the columns names as a list.
• Click on fx and write the M code below
• I do this as a second step after Source (data loading)

Step 2 – Since a Table offers more options to work as compared to a List. Covert the List to a Table. Right click on the List (header) >> To Table.

Step 3 – From the Add Columns Tab >> Custom Column >> type =null

• This will add a Custom Column will null values across all rows.
• I am doing this because I want to replace all error values with null.

The result looks like this

Step 4 – Next I will transpose this table. Transform Tab >> Transpose.

Understanding whats going on..

If you are still with me, so far you have blindly pursued the steps. Let me help you understand where are we headed.

• When you do Replace Errors the highlighted (in yellow) code appears.
• Notice that column names – One, Two and Three are hard coded. They wont change if the column names changes or source data adds a new column.
• Also note that the column names are in double curly brackets << >>. Which means it’s a list inside a list.
• We are trying to create the same list of list with 2 parts – Column Name and null value. But a dynamic one!
• Let’s proceed

Step 5 – Convert the table into a list of list.

• Use the fx button to write a short M Code
• This will convert each column into a list and then make a single list of all lists
• Renamed the step to ColList (it’s optional but nice to have good labels across)

Step 6 – It’s time now to feed the list in Replace Errors

• Using the fx I’ll create a new Step
• Write the following Code. In the code Source is the Step (which has the table with error values) and ColList is our List of List which replaces all errors dynamically with null.

Источник

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

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

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

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

Пример источника данных для этой демонстрации — книга Excel со следующей таблицей.

Эта таблица из книги Excel содержит ошибки Excel, такие как #NULL!, #REF!, и #DIV/0! в столбце «Стандартный тариф «. При импорте этой таблицы в редактор Power Query показано, как она будет выглядеть.

Обратите внимание, что ошибки из книги Excel отображаются со значением [Error] в каждой ячейке.

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

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

Укажите альтернативное значение при поиске ошибок

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

Чтобы создать новый настраиваемый столбец, перейдите в меню «Добавить столбец » и выберите «Настраиваемый столбец«. В окне «Настраиваемый столбец» введите формулу try [Standard Rate] otherwise [Special Rate] . Присвойт этому новому столбцу окончательную ставку.

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

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

В качестве альтернативного подхода можно также ввести формулу try [Standard Rate] catch ()=> [Special Rate] , эквивалентную предыдущей формуле, но используя ключевое слово catch с функцией, для которой не требуются параметры.

Ключевое catch слово было введено в Power Query в мае 2022 года.

Укажите собственную логику условной ошибки

Используя тот же образец источника данных, что и в предыдущем разделе, новая цель — создать новый столбец для конечной ставки. Если значение из стандартной ставки существует, это значение будет использоваться. В противном случае будет использоваться значение из столбца «Специальная ставка «, за исключением строк с любой #REF! ошибкой.

Единственной целью исключения ошибки является демонстрация #REF! . В соответствии с основными понятиями, представленными в этой статье, вы можете выбрать любые поля из записи об ошибке.

При выборе любого из пробелов рядом со значением ошибки вы получите область сведений в нижней части экрана. Область сведений содержит как причину ошибки, DataFormat.Error так и сообщение об ошибке: Invalid cell value ‘#REF!’

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

Использование try с пользовательской логикой

Чтобы создать новый настраиваемый столбец, перейдите в меню «Добавить столбец » и выберите «Настраиваемый столбец«. В окне «Настраиваемый столбец» введите формулу try [Standard Rate] . Присвойт этому новому столбцу имя «Все ошибки«.

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

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

Эта операция предоставит три новых поля:

• Все ошибки.HasError — показывает, было ли значение из столбца «Стандартная ставка » ошибкой.
• Все ошибки.Значение— если значение из столбца «Стандартная ставка » не содержит ошибок, этот столбец будет отображать значение из столбца «Стандартная ставка «. Для значений с ошибками это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.
• Все ошибки.Error — если значение из столбца «Стандартная ставка » содержит ошибку, в этом столбце отображается запись об ошибке для значения из столбца «Стандартная ставка «. Для значений без ошибок это поле будет недоступно, и во время операции развертывания этот столбец будет иметь null значения.

Для дальнейшего изучения можно развернуть столбец All Errors.Error , чтобы получить три компонента записи об ошибке:

• Причина ошибки
• Сообщение об ошибке
• Сведения об ошибках

После выполнения операции развертывания в поле All Errors.Error.Message отображается определенное сообщение об ошибке, сообщающее о том, какая ошибка Excel имеет каждая ячейка. Сообщение об ошибке является производным от поля сообщения об ошибке записи об ошибке.

Теперь с каждым сообщением об ошибке в новом столбце можно создать новый условный столбец с именем Final Rate и следующими предложениями:

• Если значение в столбце All Errors.Errors.Message равно null , выходные данные будут иметь значение из столбца «Стандартная ставка «.
• В противном случае, если значение в столбце All Errors.Errors.Message равно Invalid cell value ‘#REF!’. , выходные данные будут значениями из столбца «Специальная ставка «.
• Else, null.

После сохранения только столбцов «Учетная запись«, «Стандартная ставка«, » Специальная ставка» и «Окончательная ставка » и добавления правильного типа данных для каждого столбца показано, как выглядит окончательная таблица.

Использование try и catch использование пользовательской логики

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

try [Standard Rate] catch (r)=> if r[Message] <> «Invalid cell value ‘#REF!’.» then [Special Rate] else null

Источник

Работа с ошибками в 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 на текст.

Источник