Replace Error Values in Multiple Columns

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