Is there a way to ignore data format errors and proceed with a query?

[brendo]

My raw data comes from a dataset that I cannot modify. When I try to load it into power query and use it in a query merge, I get a data.format error (for a certain column) that certain values cannot be parsed into dates. The thing is, I set the column type to "text" so I would assume there wouldn't be values that need to be parsed as dates... but I'm still getting that error. Is there a way to tell power query to ignore data.format errors? It would be a major pain to locate the values that are causing the issue because I have so many date and non-date values in that particular column.

[v-stephen-msft · Accepted solution]

Hi @brendo ,

You can try “Replace Error” feature.

Replace null with errors.

The column is still in date type, and the error is replaced with null.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Syk]

Are you merging into another table that has the column type set as date? Also, when you load data it likes to add a changed type step that tries to detect your data types. Experiment with removing this step as it may be trying to convert it to a date.