Excel with #Num! in data source

[IwanVB]

I have a Excel data source with some data errors. In some colums a calculation failure occurs resulting in #Num! in Excel.

The data source is being unpivoted in PowerQuery. All records containing #Num! are being ingnored in the unpivoting proces resulting in dataloss. 

- Skipping the rows is not an option. I need the rest of the data in the records

- Replace #Num! replacing by eg. "0" seems not to work

Not loading the columns containing the #Num! is an option, but I could not find any way to do this.

Any help?

[Anonymous]

Change it to Text before you unpivot.

[IwanVB]

Unfortunately changing the data type to text, any or whatever does not work

[Anonymous]

I suppose the column is of a numeric type. Try to change it to Text befor you unpivot the table, and afterward turn the column back to numeric.

[IwanVB]

Unfortunately not: Data type is "any"

[Anonymous]

Have you tried to replace #NUM! by null ?

[IwanVB]

Tried the following solutions without any luck

#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column70", 0}}),

#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column70", null}}),