Как заменить ошибки во всех столбцах power query

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.

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