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.
From my experience that error can be caused by something as simple as one column name being different to all the other tables you are querying that are the same structure.
The error messages like this in the query editor are quite poor I know.
To me it look as though maybe the 4th query (table) maybe has something slightly different. And then when the code attempts to move down to the next transformation there is some name that it can’t find.
I truly feel like this error will stem from a column name being slightly different.
On the code row where this is erroring out, how many column names are checked there. Can you go through these one by one and check they are referencing exactly the name when the query is in the previous state one before?
Thanks for your help… and you were right. I ran Query 2 in Excel (despite it apparently loading in PowerBI) and it had the error. Turns out one of our project managers submitted and old forecast template and hence the structure of the file was different.
Given you asked me to check files one by one, is it a safe assumption there is no automated way to check which file causes this error in the event of it occurring again?
I was only able to speed up the process by loading a portion of the files at a time until I hit an error…
The PowerBI team’s ability to release new features and uplift the application every month is quite amazing. I’ll sit tight for the enhancements as this is definitely a draw back of using the application.
I have been enjoying your training videos so far, they are certainly generating a lot of ideas of how I can maximise the use of the system.
Thanks and I hope your Sunday weather is as nice in NZ as Melbourne today.
Hi -Hope it’s OK to reply to an old question. I am having the same Error message. I’ve simplified my query to three small tables divided from the same table to assure the headers are exact. When I try to “Get Data From Folder” get an Expression Error: The Key didn’t match any rows in the table.
Key=
Item=Table1
Kind=Table
Table=Table
In the formula bar is the message: = #“Changed Type”{9}[Source.Name]
I understand that PowerQuery is looking for something that is not there, but what is not there?
The three simple files I am trying to combine are living in a folder on OneDrive. They are attached to this message. Any insight you can provide would be helpful.
