Удаление и замена ошибок в Power Query

Возникновение ошибок в таблицах Excel явление не редкое. Например #Н/Д в функции ВПР(VLOOKUP), #ЗНАЧ! при неверных аргументах в вычислениях и #ДЕЛ0! при делении на 0. И чаще всего их подменяют при помощи функции ЕСЛИОШИБКА(IFERROR) – подробнее про ЕСЛИОШИБКА можно почитать в этой статье. Так же можно воспользоваться заменой значений(Главная -Найти и заменить), но замены не будут работать с результатом вычислений функций.
В Power Query тоже можно убирать ошибки как "внутри" формул, так и отдельно в "ячейках".

Скачать файл пример:

  Замена ошибок в таблице в Power Query.xlsx (46,6 КиБ, 68 скачиваний)

В файле приведены исходные данные и все варианты решений, которые разбираются в статье.

Для начала создадим очень простую таблицу продаж, в которой заранее создадим условия для возникновения ошибок как в стандартных формулах Excel, так и в вычисляемых столбцах Power Query. Это позволит нам рассмотреть не только обработку явных ошибок, но и некоторые нюансы работы с числами в Power Query.
Итак, в столбце План созданной таблицы я намеренно записал нулевые значения и плюс к этому одно значение суммы планируемых продаж записал явным текстом "126 673р", в то время как все остальные значения записаны как числа и рубли там отображаются форматом ячеек:

Так же я создал столбец "Средний чек"(столбец F) с формулой, которая в некоторых строках будет возвращать значение ошибки. В нашем случае это ошибка #ДЕЛ0!, т.к. средний чек у нас вычисляется путем деления суммы продаж на кол-во продаж(кол-во чеков) =[@[Продажи, руб]]/[@[Кол-во продаж]], а как мы помним, в некоторых строках в столбце План у нас нулевые значения. В итоге у нас подготовлена база для разбора обработки этих ошибок внутри Power Query.

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

Обработка ошибок в вычислениях
Начнем с обработки ошибок в вычисляемых столбцах.
Для того, чтобы в вычисляемом столбце Power Query пропустить ошибку вычислений(нечто вроде ЕСЛИОШИБКА в функциях Excel), применяется конструкция try [проверяемое выражение] otherwise [значение если ошибка].
Для этого создадим новый вычисляемый столбец "Выполнение плана": внутри редактора PowerQuery переходим на вкладку Добавить столбец -Пользовательский столбец и в появившемся окне вписываем формулу = [#"Продажи, руб"]/[План]:

Но т.к. мы заранее ожидаем появление ошибки, формулу мы записываем сразу с обработкой ошибок:
= try [#"Продажи, руб"]/[План] otherwise null
Как это работает: при возникновении ошибки в условии try мы эту ошибку не возвращаем, а записываем в качестве результата то, что идет после otherwise. Т.е. блок try пробует вычислить выражение и если в нем возникает ошибка, то само вычисление пропускается и в качестве результата записывается то, что записано после otherwise.
В большинстве случаев такой подход сработает отлично и будет возвращать ожидаемое нами значение. Но есть и такие ошибки, обработка которых в Power Query для привыкших к работе в Excel пользователей, будет казаться глюком или багом. И к одной из таких ошибок как раз относится ошибка деления на 0. Чтобы было более наглядно предлагаю сначала изучить результат работы наших вычислений:

Как видим, в строке 6 у нас все как ожидалось: деление на текст вызвало ошибку и она была заменена значением null. А деление на 0 почему-то ни ошибки, ни null не вернуло – вместо него загадочное NaN.
Дело в том, что для Power Query деление на 0 вообще не является ошибкой и в итоге для таких операций мы как раз можем получить значение NaN(Not a Number). Это вроде и не число, но и не ошибка. Притом в Power Query есть и другие подобные вещи, вроде Infinity(бесконечность, которая при этом может быть как положительной, так и отрицательной).
Да, выглядит странно и не логично, но такова реальность.
Поэтому, для избежания ошибки деления на 0, придется применять обычные условия проверки if [условие] then [если_условие_выполняется] else [если_условие_невыполняется].
Для изменения функции созданного вычисляемого столбца не надо его удалять – достаточно в примененных шагах справа от названия шага нажать левой кнопкой мыши на знак шестеренки:

и в появившемся окне изменить функцию.
Но т.к. мы все же допускаем возможность возникновения и других ошибок(в нашем случае математические операции с текстом), мы не заменяем функцию полностью, а создаем на её основе более сложную(т.е. try … otherwise мы продолжаем использовать):
= if [План] = 0 then // если в столбце План значение 0
null // записываем null
else // если в столбце План не 0
// пробуем вычислить и если будет ошибка вычислений
// запишем null
try [#"Продажи, руб"]/[План]
otherwise null
Чтобы начинающие не запутались в функции я разбил её на блоки и проставил комментарии.
Выглядит как-то несколько сложно и запутано, да еще и не надежно – поэтому далее в статье мы разберем и другие варианты.

Помимо ошибок в вычисляемых столбцах бывают и другие ситуации – когда ошибки уже есть в загружаемой таблице и тогда они отображаются в ячейках как Error:

Ошибки в Power Query это отдельный объект "Error" и если выделить такую ячейку внутри таблицы, то внизу увидим вполне понятное описание для этой ошибки:

Можно, конечно, создать еще вычисляемый столбец и проверять на ошибки, но….В данном случае в Power Query есть несколько более удобных инструментов для борьбы с ошибками: удаление строк с ошибками полностью и замена ошибок. Что очень удобно – эти инструменты можно так же использовать и для замены ошибок в созданных уже в Power Query столбцах, в том числе вычисляемых. Т.е. можно просто создавать вычисления без try … otherwise, а после просто заменить/удалить все ошибки. А можно вычисления сделать в исходной таблице и после уже удалить их через редактор Power Query. Плюс такого подхода в том, что та же ошибка деления на 0 будет воспринята именно ошибкой, что в ряде случаев более удобно.

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

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

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

Замена ошибок в столбцах и таблицах
Т.к. удаление строк полностью из-за ошибки только в одном из столбцов не всегда правильно с точки зрения целостности и полноты данных(ведь далеко не всегда нужно удаление полностью строк – в них могут содержаться нужные и важные данные), в Power Query есть так же инструмент для замены ошибок на заданное значение. Т.е. по сути никакого удаления нет – мы заменяем значение любой ошибки на некое другое значение. Для замены ошибок в конкретном столбце необходимо щелкнуть правой кнопкой мыши по этому столбцу и выбрать пункт Заменить ошибки:

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

В данном случае никакие строки не удаляются, но любые ошибки в столбце будут заменены на указанное значение(в данном случае это null).

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

Как уже упоминал выше, для замены ошибок во всех столбцах(иначе говоря во всей таблице), необходимо сначала выделить все столбцы этой таблицы. И вот здесь есть нюанс: Power Query всегда в таких случаях создает текстовое перечисление имен столбцов следующего вида:
= Table.ReplaceErrorValues(replace_err, {{"Средний чек", null}, {"Выполнение плана", null}})
Где "Средний чек" и "Выполнение плана" это имена наших столбцов, в которых мы решили заменить ошибки. Для примера их всего два, но в реальности здесь перечисляются имена всех столбцов таблицы, которых может быть и 20 и 50.

Если наша исходная таблица всегда содержит одинаковый набор столбцов, то проблем нет – создали запрос и забыли. Но если это всегда разные таблицы и столбцы имеют разные имена или могут дополняться, удаляться и т.п. - мы в лучшем случае заменим ошибки не во всех столбцах, а в худшем получим ошибку запроса(хотя здесь спорный вопрос, что из этого лучше 😊).
К сожалению, встроенной возможности заменить все ошибки во всей таблице нет. Но это можно сделать путем написания не самого хитрого кода на языке М. Я приведу код, который обращается к исходной таблице с именем "Таблица1" и заменяет во всех столбцах ошибки на null. При этом совершенно неважно как называются столбцы таблицы – код сам их считывает и использует далее для замены:

let
    //подключаемся к умной таблице с именем "Таблица1"
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], 
    //создаем список имен всех столбцов этой таблицы в виде списка ({"Column1", "Column2" и т.д.})
    col_names = Table.ColumnNames(Source),
    //конкретное значение, на которое надо заменить все ошибки - в нашем случае это значение null(будет пустой ячейкой)
    errval = null,
    //т.к. в исходном варианте ReplaceErrorValues выглядит как ReplaceErrorValues(Source, {{Column1, null}, {Column2, null}, ...}
    //т.е. для каждого столбца нужно указать значение для замены ошибок - null
    //Значит нам надо создать сначала список значений null, который будет по кол-ву элементов равен кол-ву столбцов
    //а затем преобразовать его в список списков для того, чтобы корректно передать его функции ReplaceErrorValues
 
    //создаем список значений(list) для замены ошибок, равный по кол-ву элементов кол-ву столбцов исходной таблицы
    to_rep_val = List.Repeat({errval}, List.Count(col_names)),
    //преобразуем список имен столбцов и значений для замены в единый список сопоставлений нужного вида: 
    to_replace = List.Zip({col_names,to_rep_val}),
    //заменяем ошибки
    replace_err = Table.ReplaceErrorValues(Source, to_replace)
in
    replace_err

Данный код вставляется через расширенный редактор Power Query: внутри редактора переходим на вкладку Главная -Расширенный редактор:

Вставляем туда приведенный выше код.

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