Forum Discussion
Helper IDataflow: Traceback Invalid Cell Value Errors to Source
I was able to resolve this issue by inserting a temporary step after the folder of excel workbooks is loaded into the query, which filtered the workbooks down by name into batches (beginning with). Using this I was able to test batches of about 5-10 at a time, until I narrowed it down to the two files which were throwing the "#REF!" errors. I opened them up, cleared up the formulas generating the errors, and now the dataflow is working again.
Super UserHi PowerBI_Chaos ,
#REF! and #N/A are Excel errors, not Power Query errors, so you're correct to want to trace this back to the Excel source.
To get this identified quickly you need to first: ignore generic ChatGPT answers, second; do it in Excel (not in the Dataflow). Dataflows are slow to load/filter and the rows aren't guaranteed to be in the same order as your source anyway.
In your Excel file you can either:
-1- Hit F5 (Go To), click on 'Special', select 'Formulas' radio button, and check the 'Errors' box.
-2- Hit Ctrl+F, type #REF! or #N/A in the Find field and either 'Find All' or 'Find Next'.
Pete
Helper IHi there,
Thank you for the reply. I had tried to adjust my post last night, but it appears to have not went through. While I was able to resolve one of the two, using the exact method mentioned... The problem is that the second power query pulls data from a bunch of excel files in a folder. Is there any way in Power Query to tell which of the files the errors are coming from? Otherwise I would need to open each and every one of them up, and there are like 50 or more.
I took this over from my predecessor, and am trying to piece it back together. Pulling data in this manner is not my preferred method.