Power Query error 'The key didn't match any rows in the table.' when combining

Occasional Contributor

Hello everyone,
I'm trying to merge multiple excel files into one spreadsheet using Power Query, but I receive the following error when doing so:

An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=Client Lists
Kind=Sheet
Table=



Now a bit of information on my data sheets. The workbooks I'm trying to combine are tabled and also contain 2 tabs each. I'm trying to specifically combine 1 of the tabs from each sheet. In the Power Query navigator, it shows the options of 'Table' and the named tab I want to focus on.

I tried to separate the 1 specific tab into one book and ensured all of my books have the exact same headers. When I try to combine I still receive the same error.

Do I have to un-table my sheets? Or do I have to create a new book for the specific sheet I'm looking to combine? Please help!
14 Replies

@Ozz_Kozz 

I guess you use From Folder connector. For it both files shall have the same structure. If you select 'Client Lists' sheet for sample (first) file, sheet with the same name shall be in another file. If you combine based on structured tables, they shall have the same name in all files.

@Ozz_Kozz 

 

The column data types on both table must be the same.

What kind of join did you select in your merge query?

 

cheers

@Sergei Baklan,

So which is it that's creating the error message since you already re-created the error

@Yea_So 

That's in my very first answer above

@Sergei Baklan
Yes and I my response was to supplement your response and make sure that the data type details and my join type question is not overlooked by the user which is not mentioned in your response.

"I guess you use From Folder connector. For it both files shall have the same structure. If you select 'Client Lists' sheet for sample (first) file, sheet with the same name shall be in another file. If you combine based on structured tables, they shall have the same name in all files."
I had a similar problem and for me the solution was simple. I just made the names of the worksheets in each workbook identical. I had 10 excel files, eg. USA.xlsx, China.xlsx, UK.xlsx, etc, each with one worksheet. I simply renamed the worksheet in each file as "SomeName" and that solved my problem.

@PGonzaga You are a genius. Simple and fast solution. Thanks you mate.

@Ozz_Kozz I built couple of PQs, one for Master and other for comparison. Now when I merged queries for vlookup, I'm getting the same error and that's what I expected as there were no matching records.  I want to go to next step and work on those records, but PQ doesn't allow me to Close & Load due to this. Does anyone overcome this or what's the work around?

@Ozz_Kozz 

 

I found this video on Ewetoob, on how to fix this. Very easy.

https://www.youtube.com/watch?v=JZSdAvGAq_E

Basically, the worksheets within each book have different names, PQ is looking for the same name. You can have it look by an index number instead, probably 0 if there's only one sheet in each workbook.

 

Open the editor. Select "Transform Sample File" in the Queries Pane (left side).

Select "Navigation" in the Applied Steps pane (right side).

Change the formula to 

= Source{0}[Data]

That should fix it!

The ability to use Get Data from Folder in Power BI is a very useful option. However, if your sheet names are different in the list of Excel files, then you ...

@PGonzaga  

  

The simplest solution to a common problem experienced by most people the first time they try to use Power Query to merge Excel documents.  

   

Just make the name of the worksheet the same in each file containing data.  That is all.  

  

How many pages full of incomprehensible and complicated answers that were impossible to implement did I have to get through just to find this simple answer? Lots.  

I've been struggling for three hours trying to combine 56 Excel spreadsheets with over 26,000 records and this is the first answer that fixed the problem first-try. Thank you x1.000!