Forum Discussion
How to FIND the source of 'DataFormat.Error: Invalid cell value '#NAME?'.'
Hi Netrelemo
You can apply those steps :1. From the view tab check on the option "Column quality"
2. If you have more than 1000 rows, you can test all of them if you change the default option:
Just click on the bottom left inscription and choose "column profiling based on all data set"
3. On hovering the mouse cursor on the place of statistics of quality m select the option of keep errors:
In this way you will stay with your problematic rows and their details :
You can also refer to the Microsoft documentation about the issue :
https://learn.microsoft.com/en-us/power-query/dealing-with-errorsfrom the part :
Handling errors at the cell level
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks for that .... functionality I did not know of.
Column profiling on entire dataset - yep
Column quality- there are no errors anywhere. Some Empty areas, sure, but nothing as an Error.
But there are errors ... it's just that Power Query cannot pick them up.
Now if I look at the data quality later in the list of APPLIED steps, then I run into this.
After looking over the info from this link here, I found a good work-around.
From Power Query Editor, select all columns at the top of your data and use Replace Errors option and just type XXXXXXXXXXX (something obvious that stands out when scrolling down the data). This will make it much easier to locate where the error is located. From there, just go to that source file (or files) and correct the error.