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

[Ozz_Kozz]

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!

[#2]

@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.

[#3]

@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

[#4]

@Yea_So 

That's easy to reproduce

[#5]

@SergeiBaklan,

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

[#6]

@Yea_So 

That's in my very first answer above

[#7]

@SergeiBaklan
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."

[#8]

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.

[#9]

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

[#10]

@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?

[#11]

It worked for me, thanks!

[#12]

@edgardcalero , glad it helped

[#13]

@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!

[#14]

@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.  

[#15]

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!

[#16]

@El_Burro_Grande 

Thank you!!! I too was struggling for hours. 🙂

[#17]

@SergeiBaklan 

I have the same error, However Im simply trying to load and save the power query from a single excel file, having only one worksheet.  In fact it is from one of the Tutorials and sample files from Kevin Stratvert. All simple tutorial steps followed. single file, single works sheet "Order Data".  No merge step required. However same error. Is there a bug with using the Folder Source connection?  Thanks for any reply.

KR, alx

[#18]

@alxjcbs 

Do you use From Folder connector? Are there any other files in this folder? On which step do you have an error?

[#19]

This fixed the problem I was having! Thank you! 1 files out of 18 had a different name on the tab. I updated the name to match the other 17 and it worked!