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

9
votes

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.

1
excelpowerquery
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

1 Answers

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