Error - The Key Didn't Match Any Rows in the Table


#1

Hi all,

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.

Cheers
KC


#2

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?


#3

Hi Sam,

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…

Thanks again, KC.


#4

Great, glad it’s fixed

Unfortunately no. The errors in the query editor are just not very good.

It sometimes just takes checking this things unfortunately.

I do know some good enhancements are on the way, so that is positive.


#5

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. :slight_smile:


#6

Great to hear. Beautiful day here today!