Remove all errors with Power Query

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.”

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.

The solution can be found here…

https://stackoverflow.com/questions/39299331/replace-all-error-values-of-all-columns-after-importing-datas-while-keeping-the

and this is the 4 lines of code…

let
    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)  



in
      ReplaceErrors

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.