Replace all error values of all columns after importing datas (while keeping the rows)

Question

An Excel table as data source may contain error values (#NA, #DIV/0), which could disturbe later some steps during the transformation process in Power Query.
Depending of the following steps, we may get no output but an error. So how to handle this cases?

I found two standard steps in Power Query to catch them:

Remove errors (UI: Home/Remove Rows/Remove Errors) -> all rows with an error will be removed
Replace error values (UI: Transform/Replace Errors) -> the columns have first to be selected for performing this operations.

The first possibility is not a solution for me, since I want to keep the rows and just replace the error values.

In my case, my data table will change over the time, means the column name may change (e.g. years), or new columns appear. So the second possibility is too static, since I do not want to change the script each time.

So I've tried to get a dynamic way to clean all columns, indepent from the column names (and number of columns). It replaces the errors by a null value.

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

Here the different three queries messages, after I've added two new column (with errors) to the source:

If anybody has another solution to make this kind of data cleaning, please write your post here.

Can you replace the original columns that generate the errors with IfError functions that trap the errors and generate other default values instead? — Eileen R
– Eileen R, Commented Sep 2, 2016 at 19:22

Sergey Lossev · Accepted Answer · 2016-09-02 20:01:15Z

23

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

answered Sep 2, 2016 at 20:01

Sergey Lossev

1,54011 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

visu-l Over a year ago

Thanks! Your code is much more cleaner/simpler as mine.

Eduardo Suela · Accepted Answer · 2023-01-02 10:04:18Z

2

Just for novices like me in Power Query "!" could be any string as substitute for error values. I initially thought it was a wild card.

List.Transform(cols, each {_, "!"}) generates the list of error handling by column for the main funcion:

Table.ReplaceErrorValues(table_with errors, {{col1,error_str1},{col2,error_str2},{},{}, ...,{coln,error_strn}})

Nice elegant solution, Sergei

edited Jan 2, 2023 at 10:04

answered Jan 2, 2023 at 10:03

Eduardo Suela

112 bronze badges

Collectives™ on Stack Overflow

Replace all error values of all columns after importing datas (while keeping the rows)

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related