#VALUE! and #REF! errors

[Anonymous]

I am using Excel spreadsheet as a source of data and I am running into issues with the #VALUE! and #REF! values. This spreadsheet contains some rows with invalid value and reference from excel so when I try to clean these up in query editor by using Table.ReplaceValue(#"Promoted Headers","#VALUE!"," ",Replacer.ReplaceValue,{"NAME"}) and Table.ReplaceValue(#"Replaced Value","#REF!"," ",Replacer.ReplaceValue,{"TELEPHONE"}), I am still getting DataFormat.Error: Invalid cell value '#VALUE!' and DataFormat.Error: Invalid cell value '#REF!' query errors during refresh.

Any idea how to get rid of this error message since I already tried replacing these values but that doesnt seem to work.

[v-easonf-msft]

Hi, @Anonymous 

Glad to hear that you have worked it out.

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

I used Table.ReplaceErrorValues and that seems to do the job.