How to catch an error in power query when the issue is with excel

Question

I am importing an excel file that has known errors like #N/A and #REF. Power query will error out until the excel is fixed. Well, I don't want to fix the errors in excel. I just want power query to ignore them. It won't. Even when I click remove errors. I tried try if xxx then xxx otherwise yyy or try error otherwise, but it still comes out as an error. Is there a fix?

Do you need to do anything with the formula's from Excel? If not, could you possibly make all data copypaste as values before Power query? — JvdV

Olly Olly · Accepted Answer · 2018-06-14T13:20:13

Replace all errors with null:

= Table.ReplaceErrorValues(#"Previous Step", List.Transform(Table.ColumnNames(#"Previous Step"), each {_, null}))

How to catch an error in power query when the issue is with excel

1 Answers