I currently have a model developed to pull data from 50+ spreadsheets sitting in a SharePoint folder. The model was working fine but after I extracted the PBIX file from Service, modified a measure and re-published, one of the queries broke. It will not refresh in Service or Desktop with the following error:
After researching in public forums, it seemed a possible issue was inconsistencies in the excel files. I believe I have eliminated this however as the error by performing the same query in Excel Get & Transform without receiving an error.
A few key points that may be important:
- As mentioned, the query (“Query 1”) performs a retrieve of a table from 50+ spreadsheets
- The query then appends in another query (“Query 2”) pulling a different table from the same spreadsheets
- The function mentioned in the above error “Transform File from Query 1 (4)”, is actually used in Query 2 (not Query 1) - however Query 2 appears to be loading without issue
- The query works fine until a particular navigation step to Pivot a column
I’ve tried quite a few troubleshooting steps such as clearing cache, closing and re-opening the file, removing and recreating the Pivot step and recreating the whole query. None have been successful.
Any suggestions for how I can troubleshoot this error would be very much appreciated.