Data Format.Error Invalid Cell Value #REF

[smjzahid]

I have a dataflow, that is connected using Sharepoint.Folder connector which has 232 excel files connected to this, The steps in Power Query in this dataflow then combines all the files together.

I am geting below error and the dataflow refresh fails, How do I know which file has invalid cell value, 

How do I get to the root of this error and find out which file is the culprit, 

Doing this manually is not possible (going through 232 excel files), any idea or hint on how to resolve this please

Thansk in advance

[v-yalanwu-msft]

Hi, @smjzahid ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @smjzahid ;

#REF is generated in the Excel sheet, not in PQ or the data Model, so somewhere in a sheet that error is occurring.

Check the source of the query that is having the error and check that source (is it an Excel table?) for the error.

You may not be seeing the error in the PQ editor as it may not have loaded all rows from the source - you only get a preview in the editor.

Try this:

1. Duplicate your query

2. Delete all steps except the Source & Navigation steps

3. Close & Apply - this should result in a pop-up like this telling you there are errors in your query.

4. Click on the View errors link in the yellow-highlighted section.

5. This creates a new query in the PQ Editor that lists the errors in your query, including the row number where the error is

6. Check the rows for your error(s).

If that doesn't help you find the issue please supply your source data file.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gilberttanjh]

Hi,

Can you help to identify which are the source and navigation steps per the picture below? Thanks. 

[ftm]

Hi @gilberttanjh, The source is the step named as source and navigation would be the step named as navigation, but as I see you do not have any for this source. Did you solve your issue? I am now facing a similar one. Thanks