Power query заменить ошибки - Не ошибается лишь тот, кто ничего не делает!

vitajlka

Пользователь

Сообщений: 15
Регистрация: 17.01.2018

Как заменить все ошибки в таблице сразу?
В источнике данных есть #ссылка или могут быть другие типы ошибок, соответственно при загрузке в модель данных query не даёт этого сделать из-за наличия ошибок. Хотя на последнем этапе в каждом столбце сделал замену ошибок, но это не помогает. В принципе, если я сделаю в самом начале в каждом столбце замену ошибок, то это сработает. Но столбцов и строк много, хочется найти быстрый способ
Текст запроса:
let
Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name=»filepath»]}[Content]{0}[Путь]&»Пример1.xlsx»), null, true),
данные_Sheet = Источник{[Item=»данные»,Kind=»Sheet»]}[Data],
#»Удаленные столбцы» = Table.RemoveColumns(данные_Sheet,{«Column1», «Column2», «Column3»}),
#»Удаленные верхние строки» = Table.Skip(#»Удаленные столбцы»,2),
#»Транспонированная таблица» = Table.Transpose(#»Удаленные верхние строки»),
#»Объединенные столбцы» = Table.CombineColumns(Table.TransformColumnTypes(#»Транспонированная таблица», {{«Column2», type text}}, «ru-RU»),{«Column1», «Column2»},Combiner.CombineTextByDelimiter(«:», QuoteStyle.None),»Сведено»),
#»Транспонированная таблица1″ = Table.Transpose(#»Объединенные столбцы»),
#»Повышенные заголовки» = Table.PromoteHeaders(#»Транспонированная таблица1″, [PromoteAllScalars=true]),
#»Другие столбцы с отмененным свертыванием» = Table.UnpivotOtherColumns(#»Повышенные заголовки», {«:Фин счет»}, «Атрибут», «Значение»),
#»Разделить столбец по разделителю» = Table.SplitColumn(#»Другие столбцы с отмененным свертыванием», «Атрибут», Splitter.SplitTextByDelimiter(«:», QuoteStyle.Csv), {«Атрибут.1», «Атрибут.2»}),
#»Строки с примененным фильтром» = Table.SelectRows(#»Разделить столбец по разделителю», each ([Атрибут.1] = «бюджет» or [Атрибут.1] = «Прогноз» or [Атрибут.1] = «Факт»)),
#»Замененные ошибки» = Table.ReplaceErrorValues(#»Строки с примененным фильтром», {{«Значение», 0}}),
#»Замененные ошибки1″ = Table.ReplaceErrorValues(#»Замененные ошибки», {{«Атрибут.1», «0»}}),
#»Замененные ошибки2″ = Table.ReplaceErrorValues(#»Замененные ошибки1″, {{«:Фин счет», 0}}),
#»Замененные ошибки3″ = Table.ReplaceErrorValues(#»Замененные ошибки2″, {{«Атрибут.2», «0»}})
in
#»Замененные ошибки3″

Источник

Замените все значения ошибок для всех столбцов после импорта данных (при сохранении строк)

Вопрос:

Таблица Excel в качестве источника данных может содержать значения ошибок (#NA, # DIV/0), которые могут нарушить некоторые последующие этапы процесса преобразования в Power Query.
В зависимости от следующих шагов мы можем получить не вывод, а ошибку. Так как же справиться с этим делом?

Я нашел два стандартных шага в Power Query, чтобы поймать их:

Удалить ошибки (интерфейс: Главная/Удалить строки/Удалить ошибки) → все строки с ошибками будут удалены
Заменить значения ошибок (UI: Transform/Replace Errors) → столбцы должны быть сначала выбраны для выполнения этих операций.

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

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

Поэтому я попытался получить динамический способ очистки всех столбцов, независимо от имен столбцов (и количества столбцов). Он заменяет ошибки на нулевое значение.

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

//Remove errors of all columns of the data source. ColumnName doesn't play any role
Cols = Table.ColumnNames(Source),
ColumnListWithParameter = Table.FromColumns({Cols, List.Repeat({""}, List.Count(Cols))}, {"ColName" as text, "ErrorHandling" as text}),
ParameterList = Table.ToRows(ColumnListWithParameter ),
ReplaceErrorSource = Table.ReplaceErrorValues(Source, ParameterList)
in
ReplaceErrorSource

Вот разные три сообщения с запросами, после того как я добавил два новых столбца (с ошибками) к источнику:

Если у кого-то есть другое решение для такой очистки данных, напишите здесь.

Лучший ответ:

let
    src = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    cols = Table.ColumnNames(src),
    replace = Table.ReplaceErrorValues(src, List.Transform(cols, each {_, "!"}))
in
    replace

Источник

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!

Make Remove Other Columns Dynamic in Power Query
Dynamic Column Renaming
Running Total in Power Query
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

Chandeep

Welcome to Goodly! My name is Chandeep.
On this blog I actively share my learning on practical use of Excel and Power BI. There is a ton of stuff that I have written in the last few years. I am sure you’ll like browsing around.
Please drop me a comment, in case you are interested in my training / consulting services. Thanks for being around
Chandeep

Источник

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

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

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

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

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

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

Листинг 1[2]

= error [

Reason = «Business Rule Violated»,

Message = «Item codes must start with a letter»,

Detail = «Non-conforming Item Code: 456»

]

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

Листинг 2

= error Error.Record(

«Business Rule Violated»,

«Item codes must start with a letter»,

«Non-conforming Item Code: 456»

)

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

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

Листинг 3

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

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

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

Листинг 4

let

a = 6,

Result = if a = 5 then true else ...

Result

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

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

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

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

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

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

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

Листинг 5

let

GetValue = () => error «Something bad happened!»,

DoSomething = (input) => input + 1,

Result = [

FieldA = 25,

FieldB = DoSomething(GetValue),

FieldC = FieldA + FieldB

]

Result

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

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

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

let

Data = GetDataFromWebService() // повышенная ошибка

{ List.Sum(Data[Amount]), List.Max(Data[TransactionDate]) }

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

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

Листинг 6

let

GetValue= () => error «Something bad happened!»,

SomeFunction = (input) => input + 1

SomeFunction(GetValue())

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

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

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

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

Листинг 7

let

Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }})

Table.RowCount(Data) // возвращает 2

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

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

Листинг 8

let

Data = #table({«Col1»}, {{«SomeValue»}, { error «bad» }})

[

RowsWithErrors = Table.RowCount(Table.SelectRowsWithErrors(Data)),

RowsWithoutErrors = Table.RowCount(Table.RemoveRowsWithErrors(Data))

]

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

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

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

try ExpressionToTry otherwise FallbackExpression

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

try Number.FromText(input) otherwise 0

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

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

try GetFromPrimary()

otherwise try GetFromSecondary()

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

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

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

[

HasError = false,

Value = (значение выражения ExpressionToTry)

]

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

[

HasError = true,

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

]

Например:

Листинг 9

let

DoSomething = () => 45,

Result = try DoSomething()

Result // [HasError = false, Value = 45]

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

Листинг 10

let

DoSomething = () => error «bad»,

Result = try DoSomething()

Result // [HasError = true, Error = [Reason = «Expression.Error», Message = «bad», Details = null]

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

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

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

let

Primary = try GetDataFromPrimary(),

Source =

if Primary[HasError] = false then Primary[Value]

/* если Primary возвращает значение, используй его */

else if Primary[Error][Reason] = «External Source Error»

and Primary[Error][Message] = «Server is unreachable»

then GetDataFromSecondary()

/* если ошибка Primary вызвана тем, что его источник недоступен,

запроси данные с сервера Secondary */

else error Primary[Error]

/* если Primary вернул иную ошибку, верни её

в качестве результата запроса */

Source

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

try GetDataFromPrimary()

otherwise GetDataFromSecondary()

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

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

let

Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}})

try Data otherwise 0

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

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

Листинг 11

let

Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}})

try List.Sum(Data[Amount]) otherwise 0

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

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

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

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

Листинг 12

let

Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}),

ErrorsReplacedWithZero = Table.AddColumn(

Data,

«NewAmount»,

(row) => try row[Amount] otherwise 0

RemoveOldAmount = Table.RemoveColumns(ErrorsReplacedWithZero, {«Amount»}),

RenameNewAmount = Table.RenameColumns(RemoveOldAmount, {«NewAmount», «Amount»})

List.Sum(RenameNewAmount[Amount]) // возвращает 10

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

Листинг 13

let

Data = #table({«Amount»}, {{10}, {error «help!»}, {error «save me!»}}),

ErrorsReplacedWithZero = Table.ReplaceErrorValues(

Data,

) // заменяет ошибки в столбце Amount нулями

List.Sum(ErrorsReplacedWithZero[Amount]) // возвращает 10

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

Листинг 14

let

Data = {10, error «help!», error «save me!»},

#»Преобразовано в таблицу» = Table.FromValue(Data),

#»Замененные ошибки» = Table.ReplaceErrorValues(#»Преобразовано в таблицу», {{«Value», 0}}),

Value = #»Замененные ошибки»[Value],

#»Вычисленная сумма» = List.Sum(Value)

#»Вычисленная сумма»

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

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

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

let

Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}})

Validated = Table.TransformColumns(

Data,

{

«ItemCode»,

each if Text.StartsWith(_, «A») then _ else error Error.Record(

«Invalid Data»,

«ItemCode does not start with expected letter»,

)

}

)

Validated

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

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

let

Data = GetData(), // for testing use: #table({«ItemCode»}, {{«1»}, {«A2»}})

Validated = Table.AddColumn(

Data,

«ValidItemCode»,

each Text.StartsWith(_[ItemCode], «A»),

type logical

)

Validated

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

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

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

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

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

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

Источник

title	description	author	ms.date	ms.author	ms.custom
Replace values and errors	In Power Query, you can replace values and errors found in one or multiple columns with a value of your choice. This article demonstrates how to do this transformation in Power Query.	ptyx507	12/12/2022	miescobar	edited

Replace values and errors

With Power Query, you can replace one value with another value wherever that value is found in a column. The Replace values command can be found:

On the cell shortcut menu. Right-click the cell to replace the selected value in the column with another value.
On the column shortcut menu.
On the Home tab, in the Transform group.
On the Transform tab, in the Any column group.

The replace values operation has two modes:

Replace entire cell contents: This is the default behavior for non-text columns, where Power Query searches for and replaces the full contents of a cell. You can enable this mode for text columns by selecting Advanced options, and then selecting the Match entire cell contents check box.
Replace instances of a text string: This is the default behavior for text columns, where Power Query will search for a specific text string in all rows of a column and replace as many instances of the text string that it finds.

Advanced options are only available in columns of the Text data type. Within that set of options is the Replace using special characters option.

Replace entire cell contents

Imagine a table like the following, where you have columns for Account ID, Category Name, and Sales Goal.

The value of -1 in the Sales Goal column is an error in the source and needs to be replaced with the standard sales goal defined by the business for these instances, which is 250,000. To do that, right-click the -1 value, and then select Replace values. This action will bring up the Replace values dialog box with Value to find set to -1. Now all you need to do is enter 250000 in the Replace with box.

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

Replace instances of a text string

Continuing with the previous table, let’s say you want to remove the text string «Category Name: » from the Category Name column. To do that, go to the Transform group on the Home tab, and select Replace values. In the Replace values dialog box, enter the text string Category Name: (followed by a space) in the Value to find box, leave the Replace with box empty, and then select OK.

The result of that operation gives you the table in the following image.

Источник