Remove errors with Power Query

02-12-2018  0 Comment(s)

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

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.

    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)  


Comment Here


No Comments to Show