How to FIND the source of 'DataFormat.Error: Invalid cell value '#NAME?'.'

[Netrelemo]

I have a couple of hundred workbooks I am interrogating with a PowerQuery. There s a problem in the source data, because one of the sheets n one of the workbooks has a '#NAME?' problem in a cell. 

I want to find it so I can go fix it. 

I suspect a numeric column is the culprit

• Change it to datatype Text and try filter. Doesn't work - same error. 
• Change it to whole number and try replace the error. Doesn't work - same error. 
• Change it to datatype Text and look for "#NAME?"  Doesn't work - same error. 
• Refresh the query and try quickly click on the "2 errors" in the sidebar window - the error message pop blocks me from getting there. 

My table is a couple of thousand line items long.... and the previw is only 30 lines at a time. 

Suggestions? 

[Ritaf1983 · Accepted solution]

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-errors

from 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

[Netrelemo]

Well, I've managed to solve my problem from a different angle. 

Use Python. 

A short script of 20 odd lines pulls 45,000 rows of data in less than 1 minute, and no frustrating "I can't do this" from PowerQuery on an Excel source. 

[Netrelemo]

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. 

[FlyVapenPikk]

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.

[Ritaf1983 · Accepted solution]

@Netrelemo 
They appear on some steps, you just need to go over all the steps that are recorded and wait until the step updates itself (a few seconds).

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

[FlyVapenPikk]

I'm experiencing the same issue. While that column quality tip is useful... it doesn't locate the error. Halfway through my applied steps is where the error must be but it goes from showing the 3 % options at the top row (just like in the screenshot) to the next step (where error(s) must be) each of the cells that had the 3 % options for column quality now show "An unexpected error occurred". Also, no, it's not about waiting for anything to load to locate the error(s). Each of those cells spins for a bit before showing that unexpected error message in each of the cells. That is how it looks for every step following that first error step too.

[Netrelemo]

Well, maybe in your ideal world, but not here. 

a) Your document advice "Handling errors at the cell level" is talking about cells in the Power Query results .. .not cells in the source data. You're not understanding the problem. 

b) I can wait a full hour (the query takes 5 minutes to complete). All processe seem complete. Nothing  magically appears. The quality analysis says there are 0% errors. Zero. But the query will not load data because there is a CELL ERROR in the underlying Excel document.

Power Query seems to have some fundamental weakness here. 

[Ritaf1983 · Accepted solution]

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-errors

from 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