April 28, 2020
I have a model that is a combination of power queries to pull data from a number of different workbooks and some standard pivots in the model. When I refresh all I am getting the following error [DataFormat.Error] Invalid cell value '#REF!'. However I think the model still runs, but I am not sure. Is there anyway I can trace or find out what this error is?
I have gone into power editor query and there are no issues on any of the queries. I have searched for #REF in underlying and cant seem to find it.
Thanks for your assistance
October 5, 2010
Can you supply your file?
#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.