Remove all errors with Power Query

/, Power Query/Remove all errors with Power Query

Remove all errors

Are you experiencing an issue when unpivotting Excel data?

Unfortunately as soon as you try to Unpivot a table of data containing a #DIV/0 or #/NA you get a very strange warning….

“The operation failed because the source databases does not exist, the source table does not exist, or because you do not have access to the data source.”

Error Message

This is totally misleading, however the “More Details:” part of the message does tell you it’s related to a #DIV/0!.

Now, the best approach is to investigate the source file and fix the #DIV/0. However, for a number of reasons this is 1) highly time consuming, 2) the errors are known and the data isn’t required. You just need to convert the errors to 0.

So the simple approach would be to highlight all of the columns in Power Query and do a replace all errors. However, life is never that simple. If you don’t want your code to refer to any of the column names, if the column names are not consistent in the files you are consolidating – which is usually the case.

Error 1

Error 2

The solution can be found here…

and this is the 4 lines of code…

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

 // Generate a list of all the column headings

    AllColHeadings = Table.ColumnNames(Source), 

 // Use List.Transform to create a list of the the column headings with a value of 0 against each one
   ColsAndReplacementVals = List.Transform( AllColHeadings, each {_, 0}),

 // Then use Table.ReplaceErrorValues using the original table (Source)  and  and the new list of columns and replacement values

    ReplaceErrors = Table.ReplaceErrorValues(Source, ColsAndReplacementVals)  


Error 3

You can now happily unpivot your data without issue, and more importantly for, in this scenario, you don’t have to reference any of the columns in the code.

Error 4


Want to learn more? Follow these useful links

Power Query articles | Power Query books |Bad credit cash paid back in installments



2018-07-18T17:18:08+00:00Excel 2016, Power Query|